One of the great features in SQL Server is its optimizing query processor. The Sybase version of SQL Server pioneered on-the-fly query optimization, and, even to this day, intrinsic query optimization is one of the most useful features of the product. By automatically optimizing end-user queries, SQL Server further separates the logical work requested from the physical means of doing it, providing greater plan reuse and flexibility for most users.

There are, however, times when some of the advanced features of the optimizer can cause problems. Sometimes it simply helps too much. One of those is when it automatically uses the parameter values passed into a stored procedure to tailor the execution plan it will construct for a query. This is called "parameter sniffing" and is normally a good thing. It allows the optimizer to take into account the actual values of stored procedure parameters as it builds plans to service queries within the procedure that make use of them. IOW, if you pass a value of "bar" for a parameter named @foo, then use @foo in the WHERE clause of a query within the procedure, the optimizer is smart enough to use "bar" when it estimates how many rows will match the WHERE clause as it compiles a plan for the query.

While parameter sniffing is certainly a powerful feature, it can cause problems when a procedure’s plan happens to have been kicked out of the procedure cache (or was never in it) just prior to the procedure being called with atypical parameter values. This can result in a plan that is skewed toward atypical use, one that is suboptimal (or even downright slow) when called with typical values. Since, once cached, a query plan can be reused for parameter values that vary widely, the ideal situation is to have a plan in the cache that covers the typical usage of the procedure as much as possible. If a plan makes it into the cache that is oriented toward atypical parameter values, it can have a devastating effect on performance.

An example would probably help here. Suppose we had a stored procedure that returns sales data by country. In our case, three-fourths of our sales occur in the U.S. The procedure takes a single parameter, @country, indicating the country for which to return sales info. It uses this parameter to filter a simple SELECT statement that returns the requested sales data.

Typically, the optimizer would choose to do a clustered index scan when servicing this query because (given that "USA" would normally be passed in for @country) so much of the table would be traversed anyway that scanning it would require less I/O and be faster than repeated nonclustered index lookups. However, what happens if the plan happens to have been kicked out of the cache (let’s say due to an auto-statistics update) just prior to a user calling it with, say, "BEL" (Belgium), where we have almost no sales? Assuming a suitable index exists, the optimizer may decide to use a nonclustered index seek in the new query plan. Subsequent executions of the procedure would reuse this plan, even if they passed in "USA" for @country. This could result in performance that is orders of magnitude slower than the scan-based plan.

Plans can be kicked out of the procedure cache for lots of reasons having nothing to do with the end user. This, coupled with the aforementioned downside of parameter sniffing, can lead to unpredictable performance from stored procedures that are called with widely varying values for their parameters.

SQL Server 2005 has some features to help combat this, but there are no built-in features for dealing with it in prior releases. There is, however, a simple trick that you can use to control what parameter values the optimizer uses when formulating a query plan. This trick works on every release of SQL Server since 7.0. It involves fooling the optimizer into using values you specify in the procedure itself to effectively disable parameter sniffing. Let’s look at some code:

CREATE PROC prSalesByCountry

@country char(3),

@template_country char(3)="USA"

AS

SET @template_country=@country

SELECT * FROM sales WHERE country=@template_country

GO

This technique is really very simple. We filter the query on a parameter, @template_country, that we never actually pass into the procedure. We always pass in the filter value we want to use via @country and let @template_country retain its default. The optimizer notices that we’re filtering the query against the sales table using @template_country and sniffs its value (which will always be "USA" at compilation time – again, because we never actually pass it, and "USA" is its default value) when creating the query plan for it.

Note the assignment of @country to @template_country. Because this statement doesn’t take effect until the plan is actually executed, the optimizer can’t take it into account during plan compilation. This is a key point to grasp. This technique works because the optimizer cannot factor the assignment of @country to @template_country into the compilation process. As far as the optimizer is concerned, @template_country will always have a value of "USA" when the SELECT statement is processed.

So, this effectively defeats parameter sniffing and allows you to ensure plan stability against parameter sniffing quirks regardless of what happens to a cached plan over time.

Of course, this is great for the typical case, but what if you genuinely wanted a different plan for atypical parameters? You have several options. If compilation isn’t too expensive, you could create or execute the procedure using the WITH RECOMPILE option. You could also split the typical and atypical cases into two different procedures so that you could ensure that they’d get their own plans. There are several options here. The technique above is really intended to address the not uncommon scenario where overzealous parameter sniffing and plan reuse combine to cause a procedure to perform unpredictably.

This approach was first documented in my last book, The Guru’s Guide to SQL Server Architecture and Internals. See the "Parameter Sniffing" section in the Query Processor chapter in that book for more info.