Parameter Sniffing

What is it? First we will go through the scripts below;

Create the following index;

USE [AdventureWorks2008R2]

GO

/****** Object:  Index [NNI_SalesOrderDetail_ModifiedDate]    Script Date: 03/06/2012 15:09:41 ******/

CREATE NONCLUSTERED INDEX [NNI_SalesOrderDetail_ModifiedDate] ON [Sales].[SalesOrderDetail]

(

     [ModifiedDate] ASC

)

Then create the below stored procedure. It is a very simple SP which has single SELECT statement for Sales.SalesOrderDetail table. There are two parameters for two date values.
USE AdventureWorks2008R2

GO

CREATE PROC dbo.Test_ParameterValues

(

      @From datetime

     ,@To   datetime

)

AS

BEGIN

     SELECT   SalesOrderDetailID

              ,CarrierTrackingNumber

              ,OrderQty,ProductID

              ,UnitPrice

              ,UnitPriceDiscount

              ,LineTotal

     FROM

          Sales.SalesOrderDetail

     WHERE

          ModifiedDate>=@From AND ModifiedDate<=@To

    

END  

Enabled the execution plan in SSMS and execute the stored procedure as mentioned below;

EXEC dbo.Test_ParameterValues '01/01/2008','06/06/2008'

As per the execution plan, it is a clustered index scan.

Then execute the SP again with different values. This time for a small date range.

EXEC dbo.Test_ParameterValues '01/02/2008','01/04/2008'

Still the plan is same as the previous time.
Let's clear the cache.

DECLARE @DBID int

SELECT @DBID=DB_ID('AdventureWorks2008R2')

DBCC FLUSHPROCINDB(@DBID)

This clears the cache only for AdventureWorks2008R2. (Never run this in a production system without knowing the repercussions)
Execute the SP now for the small date range first. That is;

EXEC dbo.Test_ParameterValues '01/02/2008','01/04/2008'

This time execution plan has changed. Now it is Index Seek and Key Lookup. Keep this in mind and execute the SP again for large date range.

EXEC dbo.Test_ParameterValues '01/01/2008','06/06/2008'

Same plan again.

Did you notice this phenomena before? This is known as "Parameter Sniffing". It means when SQL Server creates an execution plan it uses the parameter values supplied at very first time. Then use the same plan till it gets aged out.

Is parameter sniffing a problem? MIGHT BE. See the SP I created here. The preffered behavior is to work 90% of the time for small date range. Very first if it executed with small date range, the plan it creates would be the optimized one. However if it executes very first time with a large date range (not the normal behavior) then every time it uses the same plan for small date range as well, which is not the optimal plan.

Cheers for reading this blog post.