SQL Server - Parameter Sniffing

Parameter Sniffing

SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed.  By “first time”, I really mean whenever SQL Server is forced to compile or recompile a stored procedures because it is not in the procedure cache.

Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.

Prior to showing you my stored procedure, let me first provide you the code I used to create and populate my table from which my stored procedure selected data:

SET NOCOUNT ON;

DROP TABLE BillingInfo;

CREATE TABLE BillingInfo(

ID INT IDENTITY,

BillingDate DATETIME,

BillingAmt MONEY,

BillingDesc varchar(500));

DECLARE @I INT;

DECLARE @BD INT;

SET @I = 0;

WHILE @I < 1000000

BEGIN

  SET @I = @I + 1;

  SET @BD=CAST(RAND()*10000 AS INT)%3650;

  INSERT BillingInfo (BillingDate, BillingAmt)

  VALUES (DATEADD(DD,@BD,

  CAST('1999/01/01' AS DATETIME)),

   RAND()*5000);

END

ALTER TABLE BillingInfo

  ADD  CONSTRAINT [PK_BillingInfo_ID]

  PRIMARY KEY CLUSTERED (ID);

CREATE NONCLUSTERED INDEX IX_BillingDate

ON dbo.BillingInfo(BillingDate);

To show you how parameter sniffing effects stored procedure executions I will be using this stored procedure: 

CREATE PROC [dbo].[DisplayBillingInfo]

   @BeginDate DATETIME,

   @EndDate DATETIME

 AS

 SELECT BillingDate, BillingAmt

 FROM BillingInfo

 WHERE BillingDate between @BeginDate AND @EndDate; 

I will execute this stored procedure twice.  Each test will call this stored procedure using a different set of parameter values.  

My first test will run the following statements:

SET STATISTICS IO ON;

DBCC FREEPROCCACHE;

EXEC dbo.DisplayBillingInfo

@BeginDate = '1999-01-01', 

@EndDate  = '1999-12-31'; 

EXEC dbo.DisplayBillingInfo

@BeginDate = '2005-01-01', 

@EndDate  = '2005-01-03';

I turned on statistics so I can show the I/O generated by each execution.  I also ran “DBCC FREEPROCCACHE;” statement so I could make sure my stored procedure was not already in the procedure cache.  This allowed the first EXEC statement to compile my stored procedure. 

The first set of statistics is associated with the first EXEC statement and the second set of statistics is for the second EXEC statements:

Table 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now let me run the second test.  Here is the code for the second test:

SET STATISTICS IO ON;

DBCC FREEPROCCACHE;

EXEC dbo.DisplayBillingInfo

  @BeginDate = '2005-01-01', 

  @EndDate  = '2005-01-03';

EXEC dbo.DisplayBillingInfo

  @BeginDate = '1999-01-01', 

  @EndDate  = '1999-12-31';

Here I have swapped the order of the two different EXEC statements.  This way the first EXEC statement now calls the stored procedure using the smaller date range as parameters.  This short date range will be the one that are sniffed in order to create the compiled execution plan.

Below are the statistics for the second test:

Table 'BillingInfo'. Scan count 1, logical reads 2965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'BillingInfo'. Scan count 1, logical reads 337040, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

How to Deal With Parameter Sniffing

Option 1: With Recompile

The problem with parameter sniffing is the fact that the first set of parameter values are used to determine the execution plan.  To overcome this problem, all you need to do is to recompile the stored procedure every time it is executed. 

This can be accomplished by using the “WITH RECOMPILE” options when you create a stored procedure, like so:

DROP PROC [dbo].[DisplayBillingInfo]

GO

CREATE PROC [dbo].[DisplayBillingInfo]

  @BeginDate DATETIME,

  @EndDate DATETIME

WITH RECOMPILE

AS

SELECT BillingDate, BillingAmt

FROM BillingInfo

WHERE BillingDate between @BeginDate AND @EndDate;

The drawback of this option is the store procedure is recompiled with every execution.  This means, you are incurring additional system resources to compile this procedure with each execution.

Option 2: Disabling Parameter Sniffing

Another method of resolving the parameter sniffing issue is to disable parameter sniffing altogether.  This is not done with a switch or database option, but can be done from within the script of  your stored procedure code.  Here is an example of how I created my stored procedure so parameter sniffing is disabled:

DROP PROC [dbo].[DisplayBillingInfo]

GO

CREATE PROC [dbo].[DisplayBillingInfo]

   @BeginDate DATETIME,

   @EndDate DATETIME

 AS

DECLARE @StartDate DATETIME;

DECLARE @StopDate DATETIME;

SET @StartDate = @BeginDate;

SET @StopDate = @EndDate;

SELECT BillingDate, BillingAmt

FROM BillingInfo

WHERE BillingDate between @StartDate AND @StopDate; 

To disable parameter sniffing, all I did was to change the way the parameter values were used within the stored procedure.  By creating two different local variables (@StartDate and @EndDate) inside my procedure, setting those variables to the passed parameters, and then using the local variables in the BETWEEN condition, I was able to disable parameter sniffing.  Parameter sniffing is disabled because the optimizer is not able to identify the parameters’ values in the actual SELECT statement.  Because SQL Server cannot tell what parameter values where used to call the stored procedure, the optimizer creates a generic plan based on the statistics. 

When I execute my stored procedure using the code above, using either a narrow range of dates or a years’ worth of dates, the compiled execution plan always does an “index scan” operation.  I can tell parameter sniff is turned off because I know that the short range of dates would normally have created an index seek operation.

Option 3: Creating Multiple Stored Procedures

This option uses multiple stored procedures where each stored procedure can be optimize for a specific type of parameters values. 

In my example, I have two different sets of parameters.  One set of parameters has a short range of dates, where the dates are between 2005-01-01 and 2005-01-03.  Whereas the second set of dates have a large range between 1999-01-01 and 1999-12-31.

CREATE PROC [dbo].[DisplayBillingInfoNarrow]

   @BeginDate DATETIME,

   @EndDate DATETIME

 AS

SELECT BillingDate, BillingAmt

  FROM BillingInfo

  WHERE BillingDate between @BeginDate AND @EndDate; 

GO

CREATE PROC [dbo].[DisplayBillingInfoWide]

  @BeginDate DATETIME,

  @EndDate DATETIME

AS

SELECT BillingDate, BillingAmt

  FROM BillingInfo

  WHERE BillingDate between @BeginDate AND @EndDate; 

GO 

DROP PROCEDURE [dbo].[DisplayBillingInfo];

GO 

CREATE PROC [dbo].[DisplayBillingInfo]

  @BeginDate DATETIME,

  @EndDate DATETIME

AS

IF DATEDIFF(DD,@BeginDate, @EndDate) < 4

  EXECUTE DisplayBillingInfoNarrow @BeginDate, @EndDate

ELSE

  EXECUTE DisplayBillingInfoWide @BeginDate, @EndDate

GO

The first procedure I created  was DisplayBillingInfoNarrow and the second  was DisplayBillingInfoWide. They are identical, except for the name.  The third stored procedure DisplayBillingInfo has the same name as my original stored procedure, but logic in the stored procedure is different.  The code now determines the number of days between the @BeginDate and @EndDate and if it is less than four it called the narrow stored procedure DisplayBillingInfoNarrow, otherwise it calls the wide stored procedure DisplayBillingInfoWide.