Supplements and SQL Hints - Part 1 - TSQL Helpers

 

Let's face it, every now and then we need some help. A little boost to get over the finish line. Whether that be in the gym or in your daily life as a database administrator. Hence I am putting together a little mini series: Supplements and SQL Hints! Both are little helpers that can assist in crossing the finish line of a goal or simply improving overall performance. Honestly, when I first conceived of The Viking DBA Blog, this was I believe the second topic I jotted down to write about. Just never got around to it. But we're here now so let's dive in.

The series is going to span the month of September, each week addressing a new topic under this banner. Week 1 - Query Hints. Using boosters that affect the overall execution plan of the script.
Week 2 - Pre-workout supplements and energy boosters
Week 3 - Table Hints. These are elements you can apply at a single table level
Week 4 - Post workout supplements and muscle builders
Week 5 - Join Hints. You guessed, how to affect your join operators

In each of these subjects, I will put the disclaimer out there that it is by no means a comprehensive list. I'll hit the top 3 most common in each subject and discuss.

The first item to cover is what is a query hint? Within TSQL, you can add the OPTION ( some text ) to the bottom of your query in order to encourage the database engine to perform certain things. Most of the time, the engine will build a good plan but occasionally it'll need some nudging in one direction.

OPTION (Recompile)

Probably the most recognizable in the TSQL community, this hint forces SQL Server to compile or rebuild a new plan every time the query is executed. You most frequently see this in stored procedures but it is not exclusive to that.
Example: 

Why would you use this hint? The first time a query runs, the SQL engine will first check to see if it has a cached (stored) execution plan that matches the query to be executed. If it does, excellent. It chooses that plan and uses it. That sounds efficient right? In the case of my example above query, this particular one probably doesn't need a recompile. Even though a different value might be passed in the parameter each time, since it's only seeking one record and in this case it's the ID, it should be lightening fast and the cached execution plan is fine.
However, let's change that query up a little and try by name instead. For the purposes of this example, my Customers table is 121 distinct customer name records and 100,000 with BrianM. This is all very rudimentary, but hopefully you'll get the idea.

Even though I do have an index on the CustomerName field, because of the skewed data where every other record has just one value but 'BrianM' has 100,000, the engine is guessing based on the statistics it'll find more. When in reality it's found just one.

Now the system has a cached execution plan. It also stores the parameter we passed to it the first time. So cached with the plan is 'Bob Taylor'. That'll be important in a moment. 

So now with our cached plan, let's pass in the 'BrianM' value and see what happens.

Whoa! What happened to our estimates? The cached plan thought it was going to find 821 but got 100,000 instead! You might ask, what's the big deal? Incorrect estimates such as this will lead to memory grant issues, spills to TempDB and single threaded operations to name a few. The cached plan was granted X amount of memory, but when we passed the different parameter value, that memory may not be enough. SQL only allocates what it though it needed at the time of execution. Which would then lead to TempDb spills. Essentially keeping the data in TempDb until it's needed in the next step or the end. Also, in a previous blog post I address the Cost Threshold for Parallelism. If the first plan isn't very large, it won't hit that cost threshold and it'll only utilize one core. Start passing in larger ranges of parameter values and suddenly the query is gasping for more CPU.
OPTION Recompile addresses this problem. Adding that in, the engine will rebuild the execution plan every time it runs so as to maximize efficiency for the values coming. Think about something such as an Orders table. Pass in order date range of a single day, it returns results quickly. You want a whole month? Now you wait and wait and wait.... If the query or procedure had an OPTION recompile on there, it can build the plan according to the different values.

Parameter Sniffing. A problem as old as time in the TSQL space. OPTION Recompile is a good way to combat it. However, it is not always a silver bullet. Using this naturally will incur resource costs. So if you add it to a procedure and that gets called thousands of times in an hour or day, you might start to see an inverse reaction where the engine spends so much time rebuilding the plan it slows down operations too. Use this where it makes sense for you, try it and monitor performance.

OPTIMIZE FOR ( (@parameter=value))

This query hint will establish a cached execution plan with a parameter value of your choosing. If you know your data has a value that would be in the sweet spot, not a wide range but also not so narrow that it builds too small a plan, this might be a great alternative to OPTION Recompile. Let's go back to my Customers table example. To help with this, I'm adding 50,000 rows of 'VikingDBA') a perfect middle ground.

Now we can see the estimate as being 50,000 records. Because I told the execution to build as if the parameter value would be 'VikingDBA'.

Conversely, let's run it with my original 'BrianM' again.

Estimate is now low, but we granted more memory and when it comes to larger data sets, it might make it run parallel instead of single threaded. 

OPTION (Optimize For ( )) is another good tool to help fight parameter sniffing. Instead of building a new plan each time, you can go ahead and specify parameter values that allow for proper memory grants and index usage. Agin though, be careful and find the values that work for your data.

OPTION (MAXDOP x)

Oh my good friend MAXDOP (Maximum Degree of Parallelism). I do have a previous blog post about this one too so I won't delve deep here. This is what tells the engine how many processors it can use when processing a query. Refer to this post on the subject: Parallelism and the Gym

With this option, you override the configured value at the server level and for this specific query tell it how much processor power it can use. Got a query that might need a little extra horsepower? Drop this option in and temporarily use however many processors you feel comfortable with. This is not one I would personally tinker with too much. The server level settings are typically sufficient. However, in that rare case, just know you have it in the toolbelt.

As stated, there are many, many more Query Hints available to you. These are just some of the ones that I have used to get me over the hurdle of a troublesome query. I hope they helped. 

Got one that you just love?

Share it in the comments. Tell us about how you've used it to cross the finish line of a problem child query or process!

Tune in next week for the first round of Supplement Discussion!