Problem

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 although Indexes were created correctly, Statistics were up to date even you used Search Argument (SARG) in WHERE statement. One of things you should think about it that is PAREMETER SNIFFING issue.

 

Solution

 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.

 

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
CREATE NONCLUSTERED INDEX [id_ModifiedDate] ON [Sales].[SalesOrderDetail]
(
	[ModifiedDate] ASC
)
INCLUDE ( 	[CarrierTrackingNumber],
	[OrderQty],
	[UnitPrice],
	[UnitPriceDiscount],
	[LineTotal],
	[ProductID])

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;

ParameterSniffing1.png

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;

ParameterSniffing2.png

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.

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.

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;

 

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

 

Both ways, SQL Engine is forced to re-compile your stored procedure before executing, and the result of the Execution Plan is better.
ParameterSniffing3.png

 

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

 

If we excute it again, the Estimated Rows is 10,918 — not good, but better than the previous excution plan.
ParameterSniffing4.png

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

 

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

Use OPTION (OPTIMIZE)

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

 

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;

 

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s