What is Parameter Sniffing ?

What is Parameter Sniffing?
 
First of all, don't be scared about parameter sniffing. It's an expected behavior.

When a stored procedure is compiled or recompiled, the parameter values passed for that invocation are "sniffed" and used for cardinality estimation.  The net effect is that the plan is optimized as if those specific parameter values were used as literals in the query.  Take the following stored procedure as an example:
 
create procedure dbo.SearchProducts  @Keyword varchar(100) as
select * from Products where Keyword like @Keyword

Assume the table is has approximately 100,000 rows, and has a single-column nonclustered index on the Keyword column.

Let's say you call this the first time and pass in a parameter @Keyword='XBOX%'.  Suppose the number of rows in the table with a keyword starting with XBOX is very small -- perhaps just a few dozen rows.  The optimizer might choose to use a query plan that uses the index on the keyword column to evaluate the LIKE, then a bookmark lookup to retrieve the other columns for the row. 

This index seek + bookmark lookup plan will be cached and reused for subsequent executions of the procedure. 

Performance Problems Caused by Parameter Sniffing
 
However, at some point in the future the server must compile/recompile a new plan for the stored procedure (the prior plan may have been aged out of cache or auto update statistics kicked in on the Products table, etc).  Unfortunately, the particular execution of the procedure that compiled the new plan had a @Keyword parameter of 'KINECT%'.  Suppose that the filter 'KINECT%' returns 10% of the rows in the table.  When compiling the procedure with this parameter, SQL might select a query plan that uses a full table scan.  That plan would be ideal for the parameter 'KINECT%', but would be a terrible plan for other, more selective, search criteria. 
 
Unfortunately, following the recompile, the table scan plan would also get cached and reused.  The performance of subsequent executions with more typical parameter values would suffer. 
 
Parameter sniffing allows SQL to compile a plan that is tailored to the type of parameter that is actually passed into the stored procedure.  Generally speaking, this feature allows more efficient stored procedure execution plans, but a key requirement for everything to work as expected is that the parameter values used for compilation be "typical".  Unfortunately, as illustrated in this hypothetical example,  a procedure or parameterized query may occasionally be executed with an atypical parameter (data skew is often at play in these cases).
 
Parameter sniffing performance problems can affect all sorts of queries, but queries that use LIKE (like the example described above) are especially prone to this class of problem.  Performance problems caused by parameter sniffing are generally considered to be By Design.  
 
 

Action plan:

Since SQL Server 2005 we have a new functionality that permits individual plans recompilation instead of compiling entire execution plan for single store procedure. You could find below some workaround when you are affecting by a performance issue.

       
1. Using dummy variables that are not directly displayed on parameters also ensure execution plan stability without need to add recompile hint, example below:

create procedure dbo.SearchProducts
    @Keyword varchar(100)
As
Declare @Keyworddummy as varchar(100)
Set @Keyworddummy = @Keyword
select * from Products where Keyword like @Keyworddummy

2. To prevent this and other similar situations, you can use the following query option:

OPTIMIZE FOR
RECOMPILE

3. Disable auto-update statistics during the batch

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |