SQL Server Optimizer Hints

In most cases, the SQL Server Query Optimizer will correctly evaluate a query and run it as optimally as possible. But on occasion the Query Optimizer will fail, producing a less than optimal execution plan, and query performance will suffer because of it. When you identify such a query, you can override the Query Optimizer using what is called an optimizer hint. Optimizer hints can be divided into five different categories:

• Table Hints: Used to force index selection.
• Join Hints: Used to specify the type of JOIN strategy used.
• Query Hints: Hints used to affect GROUP BY and UNION functionality.
• Lock Hints : Used to help avoid undesirable locking.
• View Hints: Used to specify indexes in indexed views.
• Other Hints: Misc. hints.

In essence, what a hint does is override what the Query Optimizer recommends. If the Query Optimizer is wrong, then using a hint is very beneficial. But if the hint you specify is wrong, or not ideal, it will continue to be wrong until you change it, hurting performance until it is corrected.

Generally speaking, hints should be avoided. This is because the Query Optimizer is much smarter than we are (most of the time), and the execution plans it proposes are generally the best. So that means we had better be sure that we know, beyond a shadow of any doubt, that the Query Optimizer is wrong and that we know better, before we use a hint. Testing is the best way to find this out.

If you think that a hint might be necessary to optimize your query, be sure you first do all of the following first, because one of the following could be the actual problem:

• Update the statistics on the relevant tables.
• If the problem query is inside a stored procedure, recompile it, and then rerun it again to see if it work better now or not.
• Review the search arguments to see if they are sargable, and if not, try to rewrite them so that they are sargable.
• Review the current indexes, and make changes if necessary.

If you have done all of the above, and the query is not running as you expect, then you may want to consider using an appropriate optimizer hint.

Another problem with using hints is that a hint might be justified in a certain set of circumstances, but if those circumstances change, then the hint may no longer be appropriate. So if you decide to use a hint, you need to establish some process that reminds you to go back and recheck the hint's performance on a periodic basis. As data changes, or as code changes (through new releases or service packs), the hints you have provided may no longer be appropriate. If not, the hints you have used may end up hurting performance instead of helping it. [6.5, 7.0, 2000, 2005] Updated 5-1-2006

*****

Hints sometimes hurt performance more than they help. Let's say, for example, that you have inherited a database that uses a lot of hints, and you want to know if the current hints are helping or are hurting performance. One way would be to test each query that has a hint in Query Analyzer, checking the performance of the query with and without the hint. If you have just a few hints, this would not be a problem. But what if there are many hints used, even hundreds of them, what is the best way to check them all?

Depending on your circumstances, there is quick and dirty way to turn off hints for a single user connection, or for an entire SQL Server. This is done without changing any code. Here's how:

DBCC TRACEON(8602)

This above command is used to turn off all index hints for the current connection. So after running this command, any Transact-SQL code run will ignore any index hints.

DBCC TRACEON(8755)

This above command is used to turn off all locking hints for the current connection. So after running this command, any Transact-SQL code run will ignore any locking hints.

DBCC TRACEON(8722)

This above command is used to turn off all other hints (other than index and locking) for the current connection. So after running this command, any Transact-SQL code run will ignore "other" hints.

These hints can be combined, for example:

DBCC TRACEON(8602, 8755, 8722)

This command turn off all hints types for the current connection.

Now, let's say that we want to turn off one or more hints for the entire server, not just the current connection.

DBCC TRACEON(8602, 8755, 8722, -1)

By adding "-1" to any of the above examples, hints are turned off for the entire server.

Once you are done, you will need to turn these commands off. If you have turned a hint on for the current connection, canceling that connection turns off the command. But if you have turned hints off for an entire server, you will need to run this command:

DBCC TRACEOFF(8602, 8755, 8722, -1)

Use the above command, or any of its variations, to turn off hint blocking for an entire server.

So how can we use these commands in the real world? One option would be to create a session in Query Analyzer, run the appropriate code, then turning all hints off, then run the code again, watching the behavior and comparing execution plans. This is still time consuming, but you don't have to change the code for this testing.

Another option is perform a Profiler Trace of a typical workload, sorting queries by their duration. Then turn off hints as described above for an entire server, then perform another Profiler Trace of a typical workload, also sorting queries by duration. This way, you can compare the durations of the various queries to see if there are any significant differences. This is still a lot of work, but still easier than testing each query, one at a time. [7.0, 2000, 2005] Updated 8-21-2005