SQLServer Parameter sniffing

We had an issue in two of our stored procedures recently with query execution plan caching. After digging deep in the code base we found what was causing the problem.

One of the great features in SQL Server is its optimizing query processor. 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 "test" for a parameter named @param, then use @param in the WHERE clause of a query within the procedure, the optimizer is smart enough to use "test" 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.
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 are three simple tricks that you can use to solve this problem:

- the use of "decoy variables”. This means that we filter the query on a local variable value; witch replaces the parameter that we actually pass into the procedure.

CREATE PROCEDURE usp_avoidSniff1(@param int)
AS

BEGIN
Decalre @lclVarParam int

SET @lclVarParam = @param

SELECT x, y
FROM MyTable
WHERE z = @lclVarParam
END

GO

- Filter the query on a dummy parameter, i.e. @template_param, that we never actually pass into the procedure. We always pass in the filter value we want to use via @param and let @template_param retain its default. The optimizer notices that we’re filtering the query against the sales table using @template_param and sniffs its value (which will always be the default value at compilation time) when creating the query plan for it.

CREATE PROCEDURE usp_avoidSniff2(@param int, @param_def int= ‘My optim val’)
AS

BEGIN

SET @param_def = @param


SELECT x, y
FROM MyTable
WHERE z = @param_def
END

GO

- Use the OPTIMIZE FOR statement (SQLServer 2005). Adding this to a query makes sure the plan is optimized for the value you specify. And when all else fails you can even force a particular query plan with the new USE PLAN statement

CREATE PROCEDURE usp_avoidSniff1(@param int)
AS

BEGIN

SELECT x, y
FROM MyTable
WHERE z = @param OPTION(OPTIMIZE FOR (@IDate = Null))

END

GO