http://www.sqlshare.comFeed for All Videos1440http://www.sqlshare.com/ClearingtheProcedureCache_803.aspxhttp://www.sqlshare.com/ClearingtheProcedureCache_803.aspxYou'll probably need to do this less often than you think, but there are times when it's handy to start from scratch when it comes to plans. Most of us have heard of DBCC FREEPROCCACHE, but with SQL 2008 we have a couple more targeted options that you might find useful, all wrapped into one simple script you can download and experiment with.Thu, 29 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/ForcedParameterizationforQueriesinaDatabase_802.aspxhttp://www.sqlshare.com/ForcedParameterizationforQueriesinaDatabase_802.aspxIntroduced in SQL 2005, setting the parameterization mode to forced takes all plans that contain literals (firstname='andy') and changes it to a parameter (firstname = @firstname). The impact on performance can often be dramatic as the number of plans and compilations drops sharply due to the plan reuse that happens with parameterized plans. It's not always the right thing to do, but it's easy to use and requires no code changes.Wed, 28 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/IntroductiontoPlanGuides_801.aspxhttp://www.sqlshare.com/IntroductiontoPlanGuides_801.aspxEver needed to slightly change the behavior of a query in a third party app, or maybe to adjust the plan of a stored procedure without changing code? Plan guides let you set up situation where it if a query matches a template we can add or remove OPTION hints from the query, or even specify a different plan entirely. It's not perfect, but it's an interesting approach that can get you out a jam. It can also cause a headache if you are troubleshooting and don't realize a plan guide is altering the plan you're working on.Tue, 27 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/querying-full-text-indexes-using-the-formsof-method_553.aspxhttp://www.sqlshare.com/querying-full-text-indexes-using-the-formsof-method_553.aspxFull text search provides the ability to search documents and text within SQL Server. There are several ways to query full text indexes. This video shows you how to use the FormsOf method to query your full text indexes.Mon, 26 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/DeployingSSISPackagesusingFileSystemaswellasSQLServer_798.aspxhttp://www.sqlshare.com/DeployingSSISPackagesusingFileSystemaswellasSQLServer_798.aspxShould you - and how do you - deploy an SSIS package? They can go in the file system which is simple enough, but then you have to back them up separately. Or you can store them in MSDB which removes the backup issue, but makes it just a little more complicated to deploy a change to a package. You'll find reason to use both techniques!Fri, 23 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/creating-a-sql-server-data-source-and-dataset-in-ssrs_636.aspxhttp://www.sqlshare.com/creating-a-sql-server-data-source-and-dataset-in-ssrs_636.aspxSQL MVP Jessica Moss creates a connection and retrieves data from a SQL Server database to be used in a Reporting Services 2008 report.Thu, 22 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/PracticeYourRestoreSkills_800.aspxhttp://www.sqlshare.com/PracticeYourRestoreSkills_800.aspxIt's easy to get spoiled by doing restores from within Management Studio. What happens when a client running SQL 2005 sends you some *.bak files to restore? We'll show you how we did it, and then you can give it a try yourself since we're attaching all the bak files to this video!Wed, 21 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/HowToRestoreUsingaDifferentialBackupTSQL_799.aspxhttp://www.sqlshare.com/HowToRestoreUsingaDifferentialBackupTSQL_799.aspxIt's a good question - can you restore using a differential if you don't have log backups? The answer is yes! We'll show you how to do it using only TSQL. Tue, 20 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/SSISVariables_797.aspxhttp://www.sqlshare.com/SSISVariables_797.aspxUsing variables in SSIS packages is really the key to getting work done elegantly. In this example Marco shows how to control whether a package downloads a daily or weekly file. Almost anything can be set or changed using a variable, and in turn that can be set externally if you choose to allow it. One note - all variables have a 'scope', make sure you select the appropriate scope or you'll be scratching your head wondering why the value isnt' visible. Mon, 19 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/DifferentialBackupandRestoreofaSQLServerDatabaseFullRecovery_796.aspxhttp://www.sqlshare.com/DifferentialBackupandRestoreofaSQLServerDatabaseFullRecovery_796.aspxToday we're looking at how differential backups integrate into the backup cycle, providing us with a way to skip over log backups created between the time of our last full backup and the differential backup that we create. Just remember that differential backups include ALL the pages changed since the last full backup, so it can grow to be quite large if the time interval is long or you have have an environment that changes a large percentage of pages in the database.Fri, 16 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/DoingaDifferentialBackupofaSQLServerDatabaseSimpleRecovery_795.aspxhttp://www.sqlshare.com/DoingaDifferentialBackupofaSQLServerDatabaseSimpleRecovery_795.aspxDifferentials offer an interesting to backup changes, copying all of the pages that have been changed since the last full database backup. This doesn't give you point in time restore capability, but does capture all changes made up to the point of the differential backup. It's an interesting strategy, and one that is often overlooked.Thu, 15 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/HowtodoaCopyOnlyBackupofaSQLServerDatabase_794.aspxhttp://www.sqlshare.com/HowtodoaCopyOnlyBackupofaSQLServerDatabase_794.aspxWhat do you do when the QA team requests a new copy of a database? Hopefully you've got a fairly recently full backup that you can use, but sometimes they will want one that is up to date. To provide that you can just run a full backup which becomes part of the restore chain or you can do a "copy only" backup which can be used and deleted without affecting your ability to restore on the main server in any way.Wed, 14 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/RestoringaSQLServerDatabaseUsingTransactionLogBackups_793.aspxhttp://www.sqlshare.com/RestoringaSQLServerDatabaseUsingTransactionLogBackups_793.aspxIt may not be often, but it will happen - you'll need to do a point in time restore of a database. If you've done your preparations well, doing a full backup and log backups on a good schedule, the restore is the easy part. Restoring a database using log backups just builds on the skills you've already learned doing a restore from a full database backup.Tue, 13 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/HowtoBackuptheTransactionLogofaSQLServerDatabase_792.aspxhttp://www.sqlshare.com/HowtoBackuptheTransactionLogofaSQLServerDatabase_792.aspxBacking up the transaction log is an essential skill for a DBA, and it's the best way to minimize the amount of data that can be lost if a problem occurs on the server OR if someone does something bad - a delete without a where clause for example. Just remember to put the database into the full recovery model first, start the process by doing a full backup, and then you begin doing log backups.Mon, 12 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/HowtoRestoreaSQLServerDatabase_791.aspxhttp://www.sqlshare.com/HowtoRestoreaSQLServerDatabase_791.aspxHave a .BAK file you need to restore? Want to be prepared for the rainy day when something goes bad and you need to use one of those backups you've been so diligently creating? Restoring a database is a key task for anyone working with SQL Server, and it's easily done using SSMS or TSQL. Just remember to think first - do you want to overwrite the database from the backup, or create a copy from the backup? That decision is one you want to get right!Fri, 09 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/HowtoBackupaSQLServerDatabase_790.aspxhttp://www.sqlshare.com/HowtoBackupaSQLServerDatabase_790.aspxIt's one of the most basic tasks you'll do in SQL Server and incredibly important! We'll show you how to do it using Management Studio and TSQL, and along the way make sure you understand that you can do your backup while everyone continues to work.Thu, 08 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/ListingFilesinaBackupFileDevice_789.aspxhttp://www.sqlshare.com/ListingFilesinaBackupFileDevice_789.aspxSomeone sends you a backup file to restore, what do you do next? How do you know what database it contains and what logical files so you can map them to the correct drives for your server? We can view the needed answers in Management Studio, or we can use RESTORE FILELIST ONLY or RESTORE HEADERONLY to quickly view the contents of the backup. You'll use this more than once in your career!Wed, 07 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/UnderstandingRecoveryModels_788.aspxhttp://www.sqlshare.com/UnderstandingRecoveryModels_788.aspxSimple, full, or bulk-logged, what do they mean and which should you choose? The best practice answer is to go with FULL, but there are times when it makes sense to change from that. We'll review the basics of each model and show you how easy it is to change when needed.Tue, 06 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/PerformanceTuningQuiz3Part2_773.aspxhttp://www.sqlshare.com/PerformanceTuningQuiz3Part2_773.aspxWe're back with Part 2 of Quiz#3, showing you how to make SELECT COUNT(*) go faster against a simple table. As is often the case it turns out to be a case of needing a (better) index - did you figure it out? Did you get down to under 30 reads? Mon, 05 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/Exceptoperator_787.aspxhttp://www.sqlshare.com/Exceptoperator_787.aspxIn this video, Marco show how to use EXCEPT and ilustrates the difference between INTERSECT and EXCEPT. When you first look at these two operators you'll see that you could do what they do with joins, but for the sitations where they fit, they are a lot more elegant and more clearly express the intent. Worth using!Fri, 02 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/IntersectOperator_786.aspxhttp://www.sqlshare.com/IntersectOperator_786.aspxIn this video Marco introduces the INTERSECT and EXCEPT operators available in SQL Server. This video focuses on how to use INTERSECT and explains basic rules to have in mind when combining 2 resultsets. If you're used to using UNION you'll appreciate the elegance of these operators.Thu, 01 Jul 2010 00:00:00 ESThttp://www.sqlshare.com/IntersectOperator_786.aspxhttp://www.sqlshare.com/IntersectOperator_786.aspxIn this video Marco introduces the INTERSECT and EXCEPT operators available in SQL Server. This video focuses on how to use INTERSECT and explains basic rules to have in mind when combining 2 resultsets. If you're used to using UNION you'll appreciate the elegance of these operators.Wed, 30 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/SQLAzurePricingandBilling_785.aspxhttp://www.sqlshare.com/SQLAzurePricingandBilling_785.aspxSQL Azure isn't just a database in the cloud, it's database services billed as a utility, and that means you're going to get a bill each month. Andy does a great job of explaining the basics of pricing and also shows you how to monitor your usage on a day to day basis, good way to avoid end of month billing surprises!Mon, 28 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/configuring-network-settings-for-sql-server_564.aspxhttp://www.sqlshare.com/configuring-network-settings-for-sql-server_564.aspxIn this video Brian goes over the different options available for communicating with a SQL Server instance over a network. He then shows you how to enable and disable those options using the SQL Server Configuration Manager. Understanding these settings is important - they rarely change, but when they do, you'll want to know what it all means!Fri, 25 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/solve-parameter-sniffing-by-using-local-variables_531.aspxhttp://www.sqlshare.com/solve-parameter-sniffing-by-using-local-variables_531.aspxParameter sniffing is a process that occurs when executing a stored procedure for the first time that allows SQL Server to build an effective query plan. This video shows you how to limit the performance effects of parameter sniffing by utilizing local variables.Thu, 24 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/QueryDifferencesinSQLAzure_783.aspxhttp://www.sqlshare.com/QueryDifferencesinSQLAzure_783.aspxDid you know that you can use USE? Or that SELECT INTO doesn't work? There are some definite differences between what we can do in the 'standard' SQL Server and what we can do in Azure. Nothing that can keeps us from getting work done, but seeing them here will reduce the learning curve and frustration level when you launch your first Azure project!Wed, 23 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/RankingFunctionsNTILEPart4_784.aspxhttp://www.sqlshare.com/RankingFunctionsNTILEPart4_784.aspx4th and final part from the series "ranking functions". Marco shows how to use NTILE, which gives us the ability to easily bucket or partition a set of rows into groups. Imagine you want to break up your employees into teams of three, NTILE does that easily.Tue, 22 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/SQLAzureAddingandDroppingDatabases_781.aspxhttp://www.sqlshare.com/SQLAzureAddingandDroppingDatabases_781.aspxIts funny how even the smallest video can yield some unexpected learning. CREATE and DROP work pretty much as expected, but did you know that there is a db_manager role you can assign users to if you need to delegate those tasks? Or that if you create a database and drop it, you still get charged for one day?Mon, 21 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/using-client-statistics_410.aspxhttp://www.sqlshare.com/using-client-statistics_410.aspxThis video explains what client statistics are and how to use them when tuning queries.Fri, 18 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/ConnectingtoSQLAzureUsingVariousTools_782.aspxhttp://www.sqlshare.com/ConnectingtoSQLAzureUsingVariousTools_782.aspxSo far we've built a database on Azure and set the firewall rules, how you manage it remotely? Andy demonstrates connecting using SQL 2008 R2 Management Studio and SQLCmd, then changes to Visual Studio 2010 and shows how that works as well. Note the use of SQLCmd - little trick there in the user name, and while the password can be specified in the command line, Andy did not so that he could enter the password with you seeing it!Thu, 17 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/RankingFunctionsDENSERANKPart3_780.aspxhttp://www.sqlshare.com/RankingFunctionsDENSERANKPart3_780.aspxIn this 3rd video, Marco explains how to use DENSE_RANK and shows the differences between RANK and DENSE_RANK. It's probably not often that you'll use these, but knowing that they are there - and the differences - may well turn a complex problem into one that is simple to solve.Wed, 16 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/HandlingNULLValuesinSSISUsingTheConditionalSplitTransformation_779.aspxhttp://www.sqlshare.com/HandlingNULLValuesinSSISUsingTheConditionalSplitTransformation_779.aspxThe conditional split is an elegant transform - it lets you split data based on various expressions you write and send it to different destination (that may in turn 'fix' the data so it can be reintegrated back into the main stream. This video uses it to illustrate handling null/not null conditions AND reading/writing the output to Excel. For thos new to SSIS expressions, drag and drop is worth doing, the expressions are case sensitive!Tue, 15 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/FirewallConfigurationforSQLAzure_777.aspxhttp://www.sqlshare.com/FirewallConfigurationforSQLAzure_777.aspxOnce you've set up a SQL Azure server you're all ready to go, right? Not quite. You've got to decide what IP addresses can connect to it. Not hard, but maybe not expected either for those of us used to working within a corporate zone where such things are already handled. Luckily it's not hard to do as you'll see in this great demo!Mon, 14 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/RankingFunctionsRankPart2_776.aspxhttp://www.sqlshare.com/RankingFunctionsRankPart2_776.aspxIn this video Marco shows you how to use Rank function and explains the main difference between Rank and Row_number. Both are useful tools, just a matter of picking the right one for the job. Notice in the video the ones with a rank of 4 - all had the same value, so all get the same ranking.Thu, 10 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/introduction-to-deploying-ssis-packages_28.aspxhttp://www.sqlshare.com/introduction-to-deploying-ssis-packages_28.aspxIn this session, you'll learn the basics on how to deploy an SSIS package to the package store. You'll learn the benefits of whether to use the MSDB database or file system to store your packages and how to use configuration files to simplify migrations.Tue, 08 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/deployment-options-for-sql-compact-projects_487.aspxhttp://www.sqlshare.com/deployment-options-for-sql-compact-projects_487.aspxThis video takes a look at the different deployment options for a Visual Studio 2008 project using the SQL Compact edition databaseMon, 07 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/RankingFunctionsPart1_775.aspxhttp://www.sqlshare.com/RankingFunctionsPart1_775.aspxIn this video Marco introduces all 4 ranking functions available in SQL Server. This is the 1st part, wich he shows how to use ROW_NUMBER. It might look simple, but ROW_NUMBER was a significant addition to TSQL, enabling us to cleanly assign numbers to rows when needed.Thu, 03 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/SplittingDelimitedStrings_774.aspxhttp://www.sqlshare.com/SplittingDelimitedStrings_774.aspxIt's not something we'll do often, but it's a useful tool to have - especially if you want to let Reporting Services users select multiple values and pass them to a stored procedure. We'll look at two techniques, one that uses the TABLE variable, and one that works all the way back to SQL 2000 by building an XML string and using OpenXML.Wed, 02 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/PerformanceTuningQuiz3Part2_770.aspxhttp://www.sqlshare.com/PerformanceTuningQuiz3Part2_770.aspxWe're back with Part 2 of Quiz#3, showing you how to make SELECT COUNT(*) go faster against a simple table. As is often the case it turns out to be a case of needing a (better) index - did you figure it out? Did you get down to under 30 reads? Tue, 01 Jun 2010 00:00:00 ESThttp://www.sqlshare.com/sql-compact-result-set_484.aspxhttp://www.sqlshare.com/sql-compact-result-set_484.aspxThis video shows you how to query a SQL Compact edition database by using a result set in Visual Studio 2008Mon, 31 May 2010 00:00:00 ESThttp://www.sqlshare.com/PerformanceTuningQuiz3Part2_770.aspxhttp://www.sqlshare.com/PerformanceTuningQuiz3Part2_770.aspxWe're back with Part 2 of Quiz#3, showing you how to make SELECT COUNT(*) go faster against a simple table. As is often the case it turns out to be a case of needing a (better) index - did you figure it out? Did you get down to under 30 reads? Thu, 27 May 2010 00:00:00 ESThttp://www.sqlshare.com/ReportingServiceSubscriptionsTheDBA_768.aspxhttp://www.sqlshare.com/ReportingServiceSubscriptionsTheDBA_768.aspxSubscriptions are a great feature of Reporting Services, easy to build, easy to manage. Ever look at them from the DBA perspective? Lots of jobs with names like "2C16F370-785E-4460-9821-066EE952B372"! Beyond the hard to manage-ness of the names though the jobs do surprisingly little work. We'll explore that, and talk about what happens if you rename of these jobs to something more meaningful.Wed, 26 May 2010 00:00:00 ESThttp://www.sqlshare.com/PerformanceTuningQuiz3Part1_769.aspxhttp://www.sqlshare.com/PerformanceTuningQuiz3Part1_769.aspxAs usual in our quizzes Part 1 is devoted to setting up a simple test, you'll need some data from Adventureworks, and then we'll show you a very common scenario - a select COUNT(*) query. Can you make it run faster than 425 reads? How low can you go? If you're really anxious for the answer, we're loading Part 2 today as well, or you can wait until we release the answer on Thursday!Tue, 25 May 2010 00:00:00 ESThttp://www.sqlshare.com/using-tabledirect-with-sql-compact_485.aspxhttp://www.sqlshare.com/using-tabledirect-with-sql-compact_485.aspxThis video takes a look at using the SqlCommand object to query a SQL Compact edition database using the TableDirect option with Visual Studio 2008Mon, 24 May 2010 00:00:00 ESThttp://www.sqlshare.com/using-managed-procs-with-sql-compact_483.aspxhttp://www.sqlshare.com/using-managed-procs-with-sql-compact_483.aspxThis video shows you how to emulate stored procedures in SQL Compact by creating managed procs in a Visual Studio 2008 projectFri, 21 May 2010 00:00:00 ESThttp://www.sqlshare.com/PerformanceTuningQuiz2Part2_767.aspxhttp://www.sqlshare.com/PerformanceTuningQuiz2Part2_767.aspxIn Part 2 we see how adding a single compound index dramatically reduces the number of reads, eliminating a table scan in the process. We also modify the query to use NOT EXISTS, but find for the single example the savings isn't huge. Thu, 20 May 2010 00:00:00 ESThttp://www.sqlshare.com/IntroductiontoSQLAzure_764.aspxhttp://www.sqlshare.com/IntroductiontoSQLAzure_764.aspxThe first step in working with SQL Azure is to set up an account and provision a server. It only take a couple of minutes and when you're done you'll have a server name and a full fledged DNS path to it as well. We'll have more on this topic soon!Wed, 19 May 2010 00:00:00 ESThttp://www.sqlshare.com/PerformanceTuningQuiz2Part1_766.aspxhttp://www.sqlshare.com/PerformanceTuningQuiz2Part1_766.aspxYou've just been handled a routine problem - make a single stored procedure run faster and/or fewer reads using ONLY index changes, plus make recommendations on changes to the procedure that might further increase performance. Up for the challenge? Download the attached database and stored proc (requires SQL 2008 R2), or create your own in any version by making a copy of the person.contact table called Contacts. Part 2 will cover one solution later this week.Tue, 18 May 2010 00:00:00 ESThttp://www.sqlshare.com/creating-a-new-table-in-sql-compact_481.aspxhttp://www.sqlshare.com/creating-a-new-table-in-sql-compact_481.aspxThis video shows you how to create a new table in SQL Compact using Visual Studio 2008Mon, 17 May 2010 00:00:00 ESThttp://www.sqlshare.com/BuildingaReportViewerApplicationPart2_763.aspxhttp://www.sqlshare.com/BuildingaReportViewerApplicationPart2_763.aspxIn the conclusion of building our application we'll add the MS Report Viewer control and show you how to assign the report the user selected to the viewer and then how to make the viewer execute the report. Easy...almost too easy! At the end you'll have a simple but functional application and a lot better understanding of ways you can leverage the Reporting Services catalog and the viewer control.Fri, 14 May 2010 00:00:00 EST