If you have spent much time reading the tips, FAQs, and articles I have written on this Web site, you have probably noticed that I am not in favor of using query hints in queries. For those of you who may be a little new to SQL Server, a query hint is a way to force the SQL Server query optimizer to optimize a specific query in a very specific way. For example, a query hint can be used to force the query optimizer to always use a specific index, to force a query to use a specific type of join, or to force the query optimizer not to use more than one CPU to execute a query. There are many different query hints available.
Most of the time, the query optimizer is smart enough to examine a query and produce an optimum query plan. An optimum query plan uses the least SQL Server resources possible to attain the desired results. But like about most everything, the query optimizer is not perfect and occasionally makes mistakes. When it makes a mistake, the query still returns the correct results; it just takes more SQL Server resources to execute than if it were using an optimum query plan. If your server is not very busy, or if users don’t mind waiting a little extra longer for a non-optimum query to execute, you as the DBA have no problems.
On the other hand, if your SQL Server instance is overloaded, or users complain a lot about poor performance, then you may have to get your hands dirty and see what’s going on. While there are many different reasons why a query may not run optimally, a non-optimized query plan is just one of them. So, if your research on what is causing poor performance on your SQL Server indicates a less than optimal query plan for a specific query, then you may be able to use a hint to “fix” the problem. For example, perhaps the query optimizer always uses a table scan to return results, but you know there is a useful index available. For whatever reason, the query optimizer doesn’t recognize this and produces a less than optimal query plan. If you, as the DBA, have enough experience to recognize this problem, then you can add a query hint to the miss-performing query, forcing it to use the available index, which then produces an optimal query plan, reducing the SQL Server resources used and speeding up the performance of the query. Now you are a hero.
While hints can sometimes make the DBA a hero, they can also get him or her into a lot of trouble. There are several problems with hints. First, you really need to be an experienced DBA to recognize the need for using a specific type of hint. If you don’t know what you are doing, don’t even think about using hints. Second, the reason the query optimizer may be producing a poor query plan can change. For example, when index or column statistics are updated, or the data distribution changes, or when new service packs are added to SQL Server, then what caused the query optimizer to originally produce a non-optimum query plan may no longer be true. And now, it is possible that the hint that was added is causing a new performance problem, not fixing an old one.
I don’t want to spend too much more time preaching about hints, as there is a lot of information on hints elsewhere on this Web site. The focus of this article is that SQL Server 2005 has added four new query hints. So let’s take a quick look at what they are and what problems they are supposed to correct. The four new hints are:
- RECOMPILE
- OPTIMIZE FOR
- USE PLAN
- PARAMETERIZATION
RECOMPILE
Some of you may be familiar with the WITH RECOMPILE stored procedure option. (This is not a query hint.) When this option is used with a stored procedure, it forces the entire stored procedure to be recompiled each time it is run, and a new query plan to be created each time. It is often used with stored procedures that have widely varying input parameters to ensure that the optimal query plan is generated for a particular set of input parameters used for a specific execution of a stored procedure. This prevents the stored procedure from reusing a pre-existing query plan that may or may not be optimal for the particular set of input parameters used for this particular execution of the stored procedure.
One disadvantage of using the WITH RECOMPILE option is that is forces the entire stored procedure to be recompiled each time. If the stored procedure is very long, with perhaps dozens of individual queries, the recompilation of the stored procedure can put an extra burden on SQL Server resources. In many cases, it is possible that only part of the stored procedure, not all of it, should be recompiled. If this is the case, it is less resource intensive to recompile a portion of a stored procedure instead of the entire stored procedure.
With the new SQL Server 2005 RECOMPILE hint, you can do just that. For example, instead of adding the WITH RECOMPILE option to the stored procedure, which forces all of the queries in the stored procedure to recompile, you can add the RECOMPILE hint to only the query (or queries) in the stored procedure that really need to be recompiled. In some cases, this can save a lot of recompilation time each time the stored produce executes, saving SQL Server resources and boosting performance.
OPTIMIZE FOR
If a Transact-SQL batch or stored procedure runs in SQL Server, and it uses a local variable, the query optimizer can’t always create an optimum query plan because it doesn’t know what the actual local variable will be when the code is actually executed. On the other hand, the query optimizer is rather smart, and can usually make a very good educated guess at what the optimum query plan should be, even without knowing the value of the local variable. But not always. Sometime the query optimizer makes a mistake and produces a less than optimum query plan.
If you have enough experience as a DBA to recognize such a problem, you might be able to use the OPTIMIZE FOR hint to “get around” this problem, thus getting the query optimizer to produce an optimum query plan. Here’s what this hint does. In the batch or stored procedure with the local variable, you can add the OPTIMIZE FOR hint, which in effect tells the query optimizer what the local variable most likely is, and to optimize the query plan based on the value you assign. The value you assign as a hint may or may not be the actual value used when the code is executed.
If everything works out as you expect, when the code runs, the query optimizer will use the value you gave in the hint and it will produce an optimum query plan. This of course assumes that you have used a good value as the hint, and the actual value that runs when the code is actually executed also takes full advantage of that same optimum query plan. It is possible that some of the time your suggested value will work great, and other times it might not.
If you decide to use this hint, essentially what you are saying is that you are smarter than the query optimizer and your guess is better than its guess of the unknown local variable. If you are confident in your abilities as a DBA, go for it.