When SQL Server Query Hints Can Improve Performance

By: SolarWinds


SQL is a declarative language; it only defines what data to retrieve from the database. It doesn’t describe the manner in which the data should be fetched. That, as we know, is the job of the query optimizer, which analyzes a number of candidate execution plans for a given query, estimates the cost of each of these plans, and selects an efficient plan by choosing the cheapest of the choices considered.

But there may be cases when the execution plan selected is not performing as you have expected and, as part of your query troubleshooting process, you may try to find a better plan yourself. Before doing this, keep in mind that just because your query does not perform as you expected, this does not mean a better plan is always possible. Your plan may be an efficient one, but the query may be an expensive one to perform, or your system may be experiencing performance bottlenecks that are impacting the query execution.

However, although the query optimizer does an excellent job most of the time, it does occasionally fail to produce an efficient plan. That being said, even in cases when you’re not getting an efficient plan, you should still try to distinguish between the times when the problems arise because you’re not providing the query optimizer with all the information it needs to do a good job, and those when the problems are a result of a query optimizer limitation. This post is designed to help you to provide the query optimizer with the information it needs to produce an efficient execution plan, such as the right indexes and good quality statistics, and also how to troubleshoot the cases when you are not getting a good plan. The following describes what to do if you hit a query optimizer limitation.

Having said that, there might be cases when the query optimizer just gets it wrong, and because of that we may be forced to resort to hints. Hints are essentially optimizer directives that allow us to take explicit control over the execution plan for a given query, with the goal of improving its performance. In reaching for a hint, however, we are going against the declarative property of the SQL language and, instead, giving direct instructions to the query optimizer. Overriding the query optimizer is risky business; hints need to be used with caution, and only as a last resort when no other option is available to produce a viable plan.

With this warning in mind, we will review some of the hints SQL Server provides, should the need arise, as well as how and when they might be used. We’ll focus only on those hints I’ve most often seen provide positive performance benefits in certain circumstances.

When to Use Hints

Hints are a powerful means by which we can cause our decisions to overrule those of the query optimizer. However, you should only do so with extreme caution, because hints restrict the choices available to the query optimizer. They also make your code less flexible and will require additional maintenance. A hint should only be employed once you’re certain you have no alternative options. At a minimum, before you reach for a hint, you should explore these potential issues:

  • System problems. You need to make sure your performance problem is not linked to other system-related issues, such as blocking or bottlenecks in server resources such as I/O, memory, and CPU.
  • Cardinality estimation errors. The query optimizer often misses the correct plan because of cardinality estimation errors. Cardinality estimation errors can sometimes be fixed via solutions such as updating statistics, using a bigger sample for your statistics (or scanning the entire table), using computed columns, multi-column statistics, or filtered statistics, and so on. There might be cases where the cardinality estimation errors are caused by the use of features in which statistics are not supported at all, such as table variables and multi-statement, table-valued, user-defined functions. In these particular instances, you may consider using standard or temporary tables if you are not getting an efficient plan.
  • Additional troubleshooting. You may need to perform additional troubleshooting before considering the use of hints. One of the obvious choices for improving the performance of your queries is providing the query optimizer with the right indexes. You might also consider some other, less obvious troubleshooting procedures, such as breaking your query down into steps or smaller pieces and storing any intermediate results in temporary tables, as shown earlier in this chapter. You can use this method just as a troubleshooting procedure—for example, to find out which part of the original query is expensive so you can focus on it. Alternatively, you can keep it as the final version of your query if these changes alone give you better performance.

Query optimizers have improved radically after more than 30 years of research, but still face some technical challenges. The SQL Server query optimizer will give you an efficient execution plan for most of your queries, but will be increasingly challenged as the complexity of the query grows with more tables joined, plus the use of aggregations and other SQL features. If, after investigating the troubleshooting options and recommendations described here, you still find that the query optimizer is not finding a good execution plan for your query, you may need to consider using hints to direct the query optimizer toward what you feel is the optimal execution path.

Always remember that, by applying a hint, you effectively disable some of the available transformation rules to which the query optimizer usually has access and thus restrict the available search space. Only transformation rules that help to achieve the requested plan will be executed. For example, if you use hints to force a particular join order, the query optimizer will disable rules that reorder joins. Always try to use the least restrictive hint because this will retain as much flexibility as possible in your query and make maintenance somewhat easier. In addition, hints cannot be used to generate an invalid plan or a plan that the query optimizer normally would not consider during query optimization.

Furthermore, a hint that initially does a great job might actively hinder performance at a later point in time when some conditions change—for example, as a result of schema updates, service packs, new versions of SQL Server, or even enough data changes. The hints may prevent the query optimizer from modifying the execution plan accordingly, and thus result in degraded performance. It is your responsibility to monitor and maintain your hinted queries to make sure they continue to perform well after such system changes or to remove those hints if they are no longer needed.

Also, remember that if you decide to use a hint to change a single section or physical operator of a plan, then after you apply the hint, the query optimizer will perform a completely new optimization. The query optimizer will obey your hint during the optimization process, but it still has the flexibility to change everything else in the plan, so the end result of your tweaking may be unintended changes to other sections of the plan. Finally, note that the fact that your query is not performing as you hoped does not always mean that the query optimizer is not giving you a good enough execution plan. If the operation you are performing is simply expensive and resource intensive, then it’s possible that no amount of tuning or hinting will help you achieve the performance you’d like.

 NOTE

As of SQL Server 2005 SP1, Microsoft also published trace flag 2301, which you can use to enable advanced optimizations specific to decision support queries with large data sets. For more details and a list of the scenarios where this trace flag can help, seehttp://blogs.msdn.com/b/ianjo/archive/2006/04/24/582219.aspx.

Leave a Reply