Parameter Sniffing to performance issues

Have you eve been in the situation where your stored procedure (with parameters) ran very slowly in some cases? Then you observed the execution plan and saw that the SQL engine generated the incorrect execution plan when you passed different values of your stored procedure's paramaters.

For example, you wrote a stored procedure P1 to retrieve a list of orders by a date range. The first time you queried for the list of orders of January 2014, so you passed the value of the dates from 20140101 to 20140131, and the result is responded very quickly with 1.000 rows in one second. After that, you need to query the list of orders of the first quarter 2014, and the result very slowly returns 10.000 rows in one minute. What's happenning? Why does SQL engine respond slowly although your index was created correctly (SQL Server using a SEEK index)?

Well, maybe you are facing PARAMETER SNIFFING problem. Today, I'm going to talk about this problem and how to overcome it.

1- What is   PARAMETER SNIFFING?
Whenever your stored procedure is executed the first time, SQL Engine compiles it by using the called parameters that are passed the first time to your store procedure is executed. By "first time",  I mean whenever SQL Server is forced to compile or re-compile for some reason, such as the execution plan is has been aged out of cache, Auto Update Statistics kicked in on your tables... and after that, every time your store procedure is executed again, SQL Engine retrieves the execution plan from the plan cache and uses it. That is the problem when your store procedure is executed the first time; the exection plan is accepted for a set of the parameters, and it is run effectively. However, if you use a more common set of the parameters, it will be very bad.

2- Example of PARAMETER SNIFFING
In this section, I will demonstrate how PARAMETER SNIFFING impact to performance of your stored procedure.  Create a stored procedure to get a list of detail orders by ModifiedDate. Next to it, create a non-clustered index on ModifiedData column for demonstration purposes:

USE AdventureWorks2012
                      Go
                      CREATE PROCEDURE usp_SearchOrderDetailByDate
                      (
                      @FromDate date,
                      @ToDate date
                      )
                      AS
                      BEGIN
                      SET NOCOUNT ON;
                      
                      	SELECT p.Name,sod.OrderQty,sod.LineTotal,sod.CarrierTrackingNumber,sod.UnitPrice,sod.UnitPriceDiscount
                      	FROM Sales.SalesOrderDetail sod
                      	  INNER JOIN Production.Product p ON p.ProductID = sod.ProductID
                          WHERE sod.ModifiedDate >= @FromDate AND sod.ModifiedDate <=@ToDate
                      
                      SET NOCOUNT OFF;
                      END
                      

Select all Open in new window

CREATE NONCLUSTERED INDEX [id_ModifiedDate] ON [Sales].[SalesOrderDetail]
                      (
                      	[ModifiedDate] ASC
                      )
                      INCLUDE ( 	[CarrierTrackingNumber],
                      	[OrderQty],
                      	[UnitPrice],
                      	[UnitPriceDiscount],
                      	[LineTotal],
                      	[ProductID])
                      

Select all Open in new window

For the first execution, we need query DetailOrder records from 2005-07-01 to 2005-07-31 and we observe the execution plan:

DBCC FREEPROCCACHE
                      DBCC DROPCLEANBUFFERS
                      
                      SET STATISTICS PROFILE ON;
                      EXEC usp_SearchOrderDetailByDate '2005-07-01','2005-07-31';
                      SET STATISTICS PROFILE OFF;
                      

Select all Open in new window

From the SEEK index on the non-clustered index, the result is 498 rows, and the Execution Plan is appropriate. Now, we change range date from 2005-07-01 to 2007-12-31.

SET STATISTICS PROFILE ON;
                      EXEC usp_SearchOrderDetailByDate '2005-07-01','2007-12-31'
                      SET STATISTICS PROFILE OFF;
                      

Select all Open in new window

Still a SEEK index on non-clustered index, but what's happened with Estimated Rows? It is still 493 while Actual Rows is 75741. Obviously, SQL Engine has retrieved the prior execution plan for excuting. This can cause performance issues if we have huge volumes of data because SQL Engine has determined a incorrect execution plan. 

3- How to overcome PARAMETER SNIFFING?
Because of PARAMETER SNIFFING, sometimes SQL Engine will be determine a incorrect execution plan. So there are some solutions to overcome it in case it's causing the performance issue. 

3.1 Use WITH RECOMPILE
You can add WITH RECOMPILE while executing stored procedure. With this option, SQL Engine is forced to re-comple your stored procedure whenerver is is executed

SET STATISTICS PROFILE ON;
                      EXEC usp_SearchOrderDetailByDate '2005-07-01','2007-12-31' WITH RECOMPILE
                      SET STATISTICS PROFILE OFF;
                      

Select all Open in new window

Alter your store procedured to add the option WITH RECOMPILE

USE AdventureWorks2012
                      Go
                      ALTER  PROCEDURE usp_SearchOrderDetailByDate
                      (
                      @FromDate date,
                      @ToDate date
                      )
                      WITH RECOMPILE
                      AS
                      BEGIN
                      SET NOCOUNT ON;
                      
                      	SELECT p.Name,sod.OrderQty,sod.LineTotal,sod.CarrierTrackingNumber,sod.UnitPrice,sod.UnitPriceDiscount
                      	FROM Sales.SalesOrderDetail sod
                      	  INNER JOIN Production.Product p ON p.ProductID = sod.ProductID
                          WHERE sod.ModifiedDate >= @FromDate AND sod.ModifiedDate <=@ToDate
                      
                      SET NOCOUNT OFF;
                      END
                      

Select all Open in new window

Both ways, SQL Engine is forced to re-compile your stored procedure before executing, and the result of the Execution Plan is better.
3.2 Use local variables
In content of your store procedure, declare local variables and assign value of parameters

USE AdventureWorks2012
                      Go
                      ALTER  PROCEDURE usp_SearchOrderDetailByDate
                      (
                      @FromDate date,
                      @ToDate date
                      )
                      AS
                      BEGIN
                      SET NOCOUNT ON;
                          DECLARE @StartDate date,@EndDate date
                      	SELECT @StartDate = @FromDate,@EndDate = @ToDate
                      
                      	SELECT p.Name,sod.OrderQty,sod.LineTotal,sod.CarrierTrackingNumber,sod.UnitPrice,sod.UnitPriceDiscount
                      	FROM Sales.SalesOrderDetail sod
                      	  INNER JOIN Production.Product p ON p.ProductID = sod.ProductID
                          WHERE sod.ModifiedDate >= @StartDate AND sod.ModifiedDate <=@EndDate
                      
                      SET NOCOUNT OFF;
                      END
                      

Select all Open in new window

If we excute it again, the Estimated Rows is 10,918 -- not good, but better than the previous excution plan.
3.3 Use OPTION (RECOMPILE) for your table

USE AdventureWorks2012
                      Go
                      ALTER  PROCEDURE usp_SearchOrderDetailByDate
                      (
                      @FromDate date,
                      @ToDate date
                      )
                      AS
                      BEGIN
                      SET NOCOUNT ON;
                          DECLARE @StartDate date,@EndDate date
                      	SELECT @StartDate = @FromDate,@EndDate = @ToDate
                      
                      	SELECT p.Name,sod.OrderQty,sod.LineTotal,sod.CarrierTrackingNumber,sod.UnitPrice,sod.UnitPriceDiscount
                      	FROM Sales.SalesOrderDetail  sod
                      	  INNER JOIN Production.Product p ON p.ProductID = sod.ProductID
                          WHERE sod.ModifiedDate >= @StartDate AND sod.ModifiedDate <=@EndDate
                      	OPTION(RECOMPILE)
                      
                      SET NOCOUNT OFF;
                      END
                      

Select all Open in new window

I perfer this way to 3.1 WITH RECOMPILE because it does not require re-compiling all objects in your stored procedure.

3.4 In case you query data within a specific common set of parameters frequently, you can use OPTION (OPTIMIZE (@Variable = value)) or OPTION (OPTIMIZE (@Variable UNKNOWN)) 
 

USE AdventureWorks2012
                      Go
                      ALTER  PROCEDURE usp_SearchOrderDetailByDate
                      (
                      @FromDate date,
                      @ToDate date
                      )
                      AS
                      BEGIN
                      SET NOCOUNT ON;
                          SELECT p.Name,sod.OrderQty,sod.LineTotal,sod.CarrierTrackingNumber,sod.UnitPrice,sod.UnitPriceDiscount
                      	FROM Sales.SalesOrderDetail  sod
                      	  INNER JOIN Production.Product p ON p.ProductID = sod.ProductID
                          WHERE sod.ModifiedDate >= @FromDate AND sod.ModifiedDate <=@ToDate
                      	OPTION(OPTIMIZE FOR (@FromDate = '2005-07-01',@ToDate='2007-12-31') )
                      
                      SET NOCOUNT OFF;
                      END
                      

Select all Open in new window

The Execution Plan will always be based on the set of paramters @FromDate = '2005-07-01' and @ToDate='2007-12-31' 

DBCC FREEPROCCACHE
                      DBCC DROPCLEANBUFFERS
                      
                      SET STATISTICS PROFILE ON;
                      EXEC usp_SearchOrderDetailByDate '2005-07-01','2005-07-31';
                      SET STATISTICS PROFILE OFF;
                      

Select all Open in new window

CONCLUSION

PARAMETER SNIFFING is not always a bad idea and it is not always the reason of performance issues. One of the most important things I want to remind you here if you query data and the data set varies every time (number of rows are returned very very different), you should think about PARAMETER SNIFFING. Otherwise, you should check other factors with a higher priority: indexes, update statistics and so on.