Transcript
Welcome to Query Tuning with Hints and Optimizer Hotfixes. I’m Kendra Little from sqlworkbooks.com.
This course is about how you can use hints and settings to bully your query execution plans in SQL Server. Now, bullying people is absolutely a bad thing and is something you shouldn’t do. Bullying your execution plans can sometimes be a dangerous thing, but it’s also something that can be incredibly valuable if you’re in a tricky situation.
Imagine that you’re responsible for the performance of a critical SQL Server and it started getting a bad execution plan
This execution plan is for a query that can sometimes run across multiple sessions at the same time, and what’s happening is, when it gets this bad plan, it gets a really big memory allocation. So big, that if this happens across multiple sessions, it can impact all sorts of other queries and slow down your whole SQL Server.
You usually can’t just re-write a bunch of code on the fly immediately, but what if you could find that there’s a hint that will stabilize the performance of this query and prevent it from getting that bad plan?
Using that hint could buy you time, get production working well, and let you re-write that T-SQL, have time to properly review it, and take your time getting a better, longer term fix into place.
This can sometimes be a really useful approach, but we’ve got to know: what are the benefits and the risks of using query and table hints in SQL Server? What do recompile hints do? And what are the trade-offs for different ways we can use them?
If we need to bump an execution plan out of cache, what are the options for doing it and how can we be really targeted if we need to be?
What are query optimizer hotfixes, and how can we turn these on in SQL Server?
We’ve got lots of different options
You’ll also get sample interview questions and answers about using hints and optimizer hotfixes in SQL Server.
Throughout the course we will be using a demo database that has data that originated from the United States government at data.gov. This data has been imported into a SQL Server 2016 database. You can get developer edition of 2016 for free. A few of the settings we’ll be talking about today, and T-SQL commands, work in SQL Server 2016 and higher only, but most of what we’ll be talking about works in earlier versions of SQL Server as well.
Let’s get going and look at hints in SQL Server.