| |
|
|
|
| | Using Index Hints in a Query | Index hints are a way to force SQL Server to use a particular index to process a query. It won't change the results you get, but it can dramatically change performance in a very few cases where the optimizer fails to find the right index. It can cause queries to fail if the index is renamed or deleted. It's a tool that you will rarely use, but when you need it, it's a very nice thing to know about. | Andy Warren |
| | Creating a Plan Guide for a Parameterized Statement | Plan guides are a great tool to know, providing a simple way to tweak the behavior of a query when you're not able to change the source statement directly. Today we'll show you how to create a plan guide for a parameterized statement executed via sp_executesql, and we'll throw in a demo of using OPTIMIZE FOR as a bonus! | Andy Warren |
| | Controlling Plan Guides | Plan guides enable you to add or remove OPTION hints from a query, giving you the ability to tweak the behavior of queries emitted from third party applications. In this lesson we're going to do a very quick demo of creating a plan guide, and then show you how you can control when they run by using sp_control_plan_guide to enable, disable, or drop them as needed. | Andy Warren |
| | Using WITH RECOMPILE With a Stored Procedure - Part 2 | In this lesson we should you a case where the plan created by SQL Server varies based on the first value passed in to the procedure, and as a result we end up with a plan that isn't ideal for all values. One of the ways we can handle scenarios like this is by using WITH RECOMPILE to force a new plan each time. Don't use this lightly | Andy Warren |
| | Using WITH RECOMPILE With a Stored Procedure - Part 1 | WITH RECOMPILE is a way to tell SQL to build a plan, execute it, and then throw it away. Why do that? Usually it's because the underlying data is distributed in a way that a one sized fits all plan just doesn't really fit all. WITH RECOMPILE applies to all the statements in the procedure, so if you only have one statement, it's not bad, but if you have multiple statements, check out OPTION RECOMPILE which does the same thing at the statement level. | Andy Warren |
| | Introduction to Plan Caching and SysCacheObjects | Plan caching is one of the things you don't think about when you're first learning SQL Server, it just works in the background and does good stuff. But eventually as you move deeper into trying to get the most performance from your server you need to understand when and how caching works at a high level. SysCacheObjects is our view into what is being cached, and from there we can look for opportunities to improve things. Learn the basics and then try it on your server at work. | Andy Warren |
| | SSIS Variables in Parent/Child Packages and Jobs | The real power of SSIS is variables and expressions. In an earlier video Marco introduced variables, in this lesson he talks about when you might want to override variables and how to do it - a great technique when you have parent/child packages, or you want to set the parameters to something you can control at the job level instead of inside the package. | Marco Francisco |
| | Understanding Facts and Dimensions in SQL Server | If you want to edge into data warehousing and Analysis Services you need to start with the language. Facts and dimensions are still data, but stored in a very specific way that is a bit different than how we approach most transactional processing databases. Once you get the differences though, it's smooth sailing. Join Kathi for a smooth five minute walk through of these key concepts. | Kathi Kellenberger |
| | Breakpoints in SSIS | Once you start using SSIS it's not long before your packages start to grow in size and complexity. That's not bad, but unless you learn good debugging skills, it can really be frustrating when something isn't working as expected. Breakpoints - the high tech equivalent of a STOP statement - are a basic tool that you can use for things like monitoring a loop that is processing a folder in the file system. Easy, simple, and powerful, Kathi shows you how to put them to work. | Kathi Kellenberger |
| | Divide and Conquer Transactional Replication using Tracer Tokens | One of the big questions you'll get asked when using replication is about the latency - how long does it take for a transaction to go from publisher to distributor to subscriber? Back in SQL 2000 you could hack it by inserting a test row and querying for it on the subscriber. Effective, but not very elegant. Tracer tokens were added in SQL 2005 to let you easily test and monitor latency. Easy to use, effective, definitely an important part of replication. | Chris Skorlinski |
| | Using OPTION RECOMPILE for Statement Level Recompilation | If you grew up with SQL Server you're probably familiar with WITH RECOMPILE, a flag we could add to a stored procedure that would force the entire procedure to recompile. It worked, but on a big stored procedure was expensive. OPTION RECOMPILE gives us the option to just fix the one statement that is causing the pain. It's not the only way to fix the problem, but its simple and reasonably elegant. | Andy Warren |
| | Clearing the Procedure Cache | You'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. | Andy Warren |
| | Forced Parameterization for Queries in a Database | Introduced 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. | Andy Warren |
| | Introduction to Plan Guides | Ever 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. | Andy Warren |
| | Practice Your Restore Skills | It'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! | Andy Warren |
| | How To Restore Using a Differential Backup & TSQL | It'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. | Andy Warren |
| | Techniques for Deploying SSIS Packages | Should 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! | Marco Francisco |
| | Using Variables in a SSIS Package | Using 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.
| Marco Francisco |
| | Differential Backup and Restore of a SQL Server Database (Full Recovery) | Today 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. | Andy Warren |
| | Doing a Differential Backup of a SQL Server Database (Simple Recovery) | Differentials 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. | Andy Warren |
| | How to do a Copy Only Backup of a SQL Server Database | What 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. | Andy Warren |
| | Restoring a SQL Server Database Using Transaction Log Backups | It 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. | Andy Warren |
| | How to Backup the Transaction Log of a SQL Server Database | Backing 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. | Andy Warren |
| | How to Restore a SQL Server Database | Have 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! | Andy Warren |
| | How to Backup a SQL Server Database | It'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. | Andy Warren |
| | Listing Files in a Backup File/Device | Someone 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! | Andy Warren |
| | Understanding Recovery Models | Simple, 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. | Andy Warren |
| | Except Operator | In 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! | Marco Francisco |
| | Intersect Operator | In 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. | Marco Francisco |
| | SQL Azure Pricing and Billing | SQL 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! | Andy Novick |
| | Ranking Functions - NTILE (Part 4) | 4th 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. | Marco Francisco |
| | Query Differences in SQL Azure | Did 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! | Andy Novick |
| | Connecting to SQL Azure Using Various Tools | So 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! | Andy Novick |
| | SQL Azure - Adding and Dropping Databases | Its 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? | Andy Novick |
| | Ranking Functions - DENSE_RANK (Part 3) | In 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. | Marco Francisco |
| | Handling NULL Values in SSIS Using The Conditional Split Transformation | The 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! | Rochak Tarika |
| | Firewall Configuration for SQL Azure | Once 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! | Andy Novick |
| | Ranking Functions - Rank (Part 2) | In 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. | Marco Francisco |
| | Ranking Functions - Part 1 | In 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. | Marco Francisco |
| | Splitting Delimited Strings | It'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. | Andy Warren |
| | Performance Tuning Quiz #3 - Part 2 | We'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? | Andy Warren |
| | Performance Tuning Quiz #3 - Part 2 | We'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? | Andy Warren |
| | Performance Tuning Quiz #3 - Part 1 | As 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! | Andy Warren |
| | Reporting Service Subscriptions & The DBA | Subscriptions 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. | Andy Warren |
| | Performance Tuning Quiz #2 - Part 2 | In 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. | Andy Warren |
| | Performance Tuning Quiz #2 - Part 1 | You'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. | Andy Warren |
| | Introduction to SQL Azure | The 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! | Andy Novick |
| | Building a Report Viewer Application - Part 2 | In 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. | Andy Warren |
| | Building a Report Viewer Application - Part 1 | It's incredibly simple to build a basic application that allows users to view reports on the desktop or the web using the MS Report Viewer control. In this video we'll create the project and load a list of reports from Reporting Services into a combo box - and it doesn't take long to do it! Create way for a DBA to get some experience with Visual Studio 2008 without writing much code. | Andy Warren |
| | Performance Tuning Quiz - Part 3 | In Part 2 we managed to get performance down to very acceptable levels, but is that as far as we can go? If you haven't already, go back and watch Parts 1 & 2, load the files, and give it a try, and then watch this one where we experiment to see if a JOIN hint will change the game enough to matter. | Andy Warren |