<

Parameter Sniffing to performance issues

Published on
5,096 Points
1,396 Views
2 Endorsements
Last Modified:
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

Open in new window


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

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;

Open in new window


ParameterSniffing1.pngFrom 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;

Open in new window

ParameterSniffing2.pngStill 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;

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

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.
ParameterSniffing3.png3.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

Open in new window

If we excute it again, the Estimated Rows is 10,918 -- not good, but better than the previous excution plan.
ParameterSniffing4.png3.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

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

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;

Open in new window

ParameterSniffing3.png
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.
2
Comment
Author:Dung Dinh
0 Comments

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month