Beware: New Query Hints Added to SQL Server 2005

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.

USE PLAN

If you are glutton for punishment, you might want to consider the SQL Server 2005 USE PLAN hint. I say "glutton for punishment" because (1) you really need to know what you are doing when you use this hint, and (2) you will spend a lot of your time getting it to work like you expect.

Here's what the USE PLAN hint does. It essentially allows you to create your own query plan for a query, so that every time that a particular query runs, it uses the query plan you created, not one created by the SQL Server query optimizer. I guess you could call this hint the "hint of all hints." Unlike most hints, which only affect a part of how an entire query plan is created by the query optimizer, you get to control everything.

I am sure there are some DBAs who just can't wait until they can create their own query plans for all their code. But in the real world this hint will not be used, except in those very rare cases when the query optimizer has a bug and the only way to get an optimum-performing query plan until the bug is fixed is to create your own.

In addition, what makes this hint a pain to implement is that you have to specify your homemade query plan in the form of an XML-formatted query plan. You can either write this yourself (if you do this, you must really be bored), or you can capture an appropriate query plan using Profiler (good luck finding it). As I said earlier, you have to be a glutton for punishment to even want to consider using this hint.

PARAMETERIZATION

By default, SQL Server 2005 does something called simple parameterization. Essentially, simple parameterization means that SQL Server can take a look at the static or dynamic Transact-SQL being sent to it from an application, and if it finds any values that it considers to be a parameter, it will parameterize the Transact-SQL, which allows the resulting query plan to be reused, much like how SQL Server can reuse the query plans of stored procedures. Let's take a look at an example of what I am talking about.

Let's say SQL Server receives the following SELECT query:

SELECT fname, lname, address, city, state, zip FROM mailinglist WHERE zip = '65742'

When SQL Server takes a look at this simple SELECT statement, it is smart enough to realize that "65742" is a parameter, and that it is very possible that another, very similar query will be run later, but with a different value for the parameter. Because of this, when SQL Server compiles the query, it will parameterize it and cache the query plan so that if it does see a similar query, it will be able to reuse the cached query plan to execute it instead of having to recompile the statement each time it is to be executed.

For example, if the following query is run after the one above, then SQL Server will be able to reuse the cached query plan, saving SQL Server resources and boosting overall performance.

SELECT fname, lname, address, city, state, zip FROM mailinglist WHERE zip = '99686'

While this is all good news, there is one small problem: by default, only relatively simple queries can be parameterized. If your application uses mostly simple queries, the default simple parameterization of SQL Server 2005 might be more than adequate to meet your performance expectations.

But what if your application uses mostly complex queries—queries that cannot be automatically parameterized by SQL Server using simple parameterization? This is where a new feature of SQL Server 2005 comes to the rescue. This new database option is called forced parameterization. When forced parameterization is turned on at the database level, it tells SQL Server to force the parameterization of virtually all SELECT, INSERT, UPDATE, and DELETE statements. There are a few types of queries that cannot be forced, but the exceptions are few.

With forced parameterization turned on, SQL Server will perform fewer compilations of statements because it now has the ability to reuse more query plans than before, helping to reduce resource usage and boosting performance.

So what does this all have to do with the PARAMETERIZATION hint? Essentially, it allows you to override the current PARAMETERIZATION setting for the database for a specific query.

For example, if SIMPLE PARAMETERIZATION (the default setting) is set for a database and you want to use FORCED PARAMETERIZATION for a specific query, you can add the PARAMETERIZATION FORCED hint to the query, and then the query optimizer will use FORCED PARAMETERIZATION for this query.

Or, if you have FORCED PARAMETERIZATION turned on for the database, but you want to use SIMPLE PARAMETERIZATION for a specific query, you can add the PARAMETERIZATION SIMPLE hint to the query, and then the query optimizer will use SIMPLE PARAMETERIZATION for this query.

As you can probably tell, the use of this hint requires a very experienced DBA, as there are many implications when using FORCED PARAMETERIZATION at the database and query level.

Conclusion

If you haven't already guessed what my conclusion is, then you must have gone to sleep while reading this article. While SQL Server 2005 has four new query hints that can solve very narrow problems, don't use them unless you know what you are doing and you have nothing better to do with your time. I know I do.