Get a recap of the most common hints

What are the most commonly used hints, and which are the most risky?

Links discussed in the video

Information on the undocumented workaround using QUERYTRACEON in a procedure is here. You’ll also see a demo of this in this course in the section on Query Optimizer Hotfixes.

Here’s the official list of trace flags supported with QUERYTRACEON.

Transcript

Let’s recap some of the most frequently used hints in SQL Server

Hints forcing physical joins are often used and these are risky

When you use these hints you need to make sure you ask yourself, when data changes in this table, is the join that I’m forcing going to be the right choice or if this query is parametrized, is this the right joint choice for the query to use when run with other parameters than the one I’m testing.

If I do hint something like a hash join, does it raise the memory allocation for the query, and is that going to be problem if this could run across many sessions at the same time. Here’s an example of a query hint, a query plan, where I’ve used a merge join table hint. Notice the sort operator to the right of the merge join.

Merge requires that the input be sorted in the same way as one another, so SQL Server, in order to do the hint that I’m forcing, had to put a big whole sort operator in there which is going to do things like up the memory needed, cause I got to have a place to do that sort, and potentially slow down my execution plan. It did do what I asked but is it a good thing, right?

Similarly, forcing indexes with table hints are pretty common…

Also there’s a common problem that if someone drops the index or even just renames the index, the query is going to fail with a message. It is a very clear message that says, hey the index doesn’t exist, so I couldn’t do what you asked me to do.

Optimize For query hints are also fairly common…

What this is is a query hint at the end of the query where you say option, optimize this query for these specific values. No matter what values were passed in for the parameters that I list here, use the values that I’m telling you to optimize the query, ignore whatever it’s being run for.

In this case, my query has a parameter named state code and I’m saying optimize for the state code of Washington. And then for the parameter value of gender, pretend that you don’t know what value you’re optimizing that for. Anonymize it, give me a plan for some sort of pretty good value for the query. Look at the density vector for the statistics and kind of give me an average good plan when it comes to gender.

What we’re doing when we use optimize for hints is saying, don’t sniff the values that come in for this for these parameters, for these parameters I’m going to tell you exactly what to optimize for.

And as long as what I’m dictating, it should optimize for, as long as that gives a good plan for any value that it runs for, then that’s great.

If data changes and the plan that I get for this turns out to start to be slow, then I’m telling it to sniff specific values, or in the case of gender, use the density vector, since I’m saying unknown, but I’m gambling if I use this that the values I’m telling it to optimize for will be good in the future.

Those querytraceon trace hints…

The ability to use querytraceon was added in SQL Server 2005 SP2. This does require the high sysadmin permission to use it, and if you want to know what trace flags work with querytraceon, check out KB 2801413. It lists what are valid trace flags that you can’t hint for the query with querytraceon.

For controlling cardinality estimation, before SQL Server 2016 service pack 1, we had to use querytraceon for this, and you could use trace flag 9481 to say use the legacy cardinality estimator. If you’re using the legacy cardinality estimator for your database, you can use querytraceon 2313 to say, no for this query, use the new cardinality estimator.

We have the USE hints after 2016 SP1 that I think make this much easier to understand

The new estimator’s called default, and the old estimator’s called legacy. What is your default is based on different things in different versions of SQL Server.

In SQL Server 2016, we have database level scope that we can use to change cardinality estimation. In SQL Server 2014, it’s based on database compatibility level.

We can use that, ignore nonclustered columnstore index hint to control whether or not we want to use it, specifically we use the hint to say don’t use it. If we don’t use that hint then we allow SQL Server the choice to choose it.

Of course, we can hint the columnstore index by name in order to force it, but even though columnstore is awesome, sometimes it’s actually not the fastest choice, so be careful if you’re forcing columnstore.

My very favorite thing about hints is that they help us learn

When I want to know why is the optimizer not doing x, or why is the optimizer doing y, using a hint to change the behavior and comparing the execution plans is a great way to get insight into why SQL Server’s making the choices it’s making and what happens if it makes a different choice. But there is a dark side to using hints, and this also goes for query re-writes as well.

When we limit flexibility for the optimizer, even if it speeds up the query now, what we’re doing as improvements come into the optimizer later on, it may not be the fastest choice, so when we have gone in and taken special steps to manipulate queries, these are queries that we should note down so that as we upgrade SQL Server, or even just raise our database compatibility level, if we do that separate from upgrades, we should test these queries to see, hey maybe the optimizer is actually just as good or even faster without our crazy hint or crazy re-write.

Even just as data changes, the optimizer may make different changes and our re-write or our hint may not be as good as the amount of data flowing through different parts of the plan changes as the data changes over time.

We have not discussed every hint in SQL Server

There are lots of hints and we get new hints all the time.

There are even weird things you can do in your TSQL that don’t really look like hints, like using parentheses to force logical join order. And I actually don’t recommend using that. I mention it only because I found it and with some weird execution plans and was like, what is going on here, so if you have existing code where you think this is happening, check out the article on sqlmag.com where Itzik Ben-Gan discusses using, gives them examples. I actually think it makes your queries really hard to interpret so I would prefer not to use those, but if you have them in place, it helps to do some research and kind of understand what you’re working with cause it is a weird little thing.

Whenever you’re using hints, to be successful, you need to comment your code thoroughly

Comment your code as to why that hint is there and what was going wrong. If there isn’t room and you don’t want to put full details in your comments, link to a ticket or someplace where there is detailed documentation on what you were fixing in your system. You also need to have process where you review the hinted code on a regular basis, and make sure that it’s still helping make your code fast, and that your hints haven’t become the piece that is slowing your code down.

You also need careful documentation to make sure that things like someone accidentally dropping or re-naming indexes that you’ve hinted happens, and causes queries to fail, so there’s lots of little things we have to think about to make sure that if we used these hints we can be successful.

But, if we are careful, and we have good code commenting, good processing, good documentation, there are times when these can help get us consistent, fast performance when we need it the most.