Beware: New Query Hints Added to SQL Server 2005

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.