Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

Different Approaches to Correct SQL Server Parameter Sniffing


By:   |   Read Comments (15)   |   Related Tips: More > Query Optimization

Problem

You have a SQL Server stored procedure that has been performing fairly well, but suddenly its execution times skyrocket. After looking at the cached execution plan you see that the query is using all indexes as should be. Whatís going on? Keep reading and you will find an answer.

Solution

How many times when a process takes longer than expected you have listened to the same complaint: It was working yesterday? This is a tough complaint to face, especially when you need to explain to someone with little knowledge about how a RDBMS works. Of course there are a lot of things to consider when answering this questioning and one that you should not overlook is Parameter Sniffing.

What is Parameter Sniffing in SQL Server?

Every batch you execute, either ad-hoc or stored procedure, generates a query plan that is kept in the plan cache for future usage. SQL Server attempts to create the best query plan to retrieve the data, but what may seem obvious is not always the case with the plan cache.

The way SQL Server choses the best plan is by cost estimation. For example, if I ask you which is best, an index seek followed by a key lookup or a table scan you may answer the first, but it depends on the number of lookups. In other words it depends on the amount of data being retrieved. So the best query plan takes into consideration the cardinality estimation based on input parameters and with the help of statistics.

When the optimizer creates an execution plan it sniffs the parameter values. This is not an issue; in fact it is needed to build the best plan. The problem arises when a query uses a previously generated plan optimized for a different data distribution.

In most cases the database workload is homogeneous so parameter sniffing wonít be a problem; but on a small number of cases this becomes a problem and the outcome can be dramatic.

SQL Server Parameter Sniffing In Action

Now I will demonstrate a parameter sniffing issue with an example.

1. CREATE DATABASE script.

USE [master]
GO

CREATE DATABASE [TestDB]
CONTAINMENT = NONE
ON PRIMARY 
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\TestDB.mdf' , 
SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'TestDB_log', FILENAME = N'E:\MSSQL\TestDB.ldf' , 
SIZE = 5MB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO

ALTER DATABASE [TestDB] SET RECOVERY SIMPLE

2. Create two simple tables.

USE TestDB
GO

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
  DROP TABLE dbo.Customers
GO

CREATE TABLE Customers
  (
   CustomerID INT NOT NULL IDENTITY(1,1) ,
   CustomerName VARCHAR(50) NOT NULL ,
   CustomerAddress VARCHAR(50) NOT NULL ,
   [State] CHAR(2) NOT NULL ,
   CustomerCategoryID CHAR(1) NOT NULL ,
   LastBuyDate DATETIME ,
   PRIMARY KEY CLUSTERED ( CustomerID )
  )

IF OBJECT_ID('dbo.CustomerCategory', 'U') IS NOT NULL
  DROP TABLE dbo.CustomerCategory
GO

CREATE TABLE CustomerCategory
  (
   CustomerCategoryID CHAR(1) NOT NULL ,
   CategoryDescription VARCHAR(50) NOT NULL ,
   PRIMARY KEY CLUSTERED ( CustomerCategoryID )
  )

CREATE INDEX IX_Customers_CustomerCategoryID 
ON Customers(CustomerCategoryID)

3. The idea with the sample data is to create an odd distribution.

USE TestDB
GO

INSERT INTO [dbo].[Customers] ( 
                [CustomerName],
                [CustomerAddress],
                [State],
                [CustomerCategoryID],
                [LastBuyDate])
SELECT 
    'Desiree Lambert',
    '271 Fabien Parkway',
    'NY',
    'B',
    '2013-01-13 21:44:21'


INSERT INTO [dbo].[Customers] ( 
                [CustomerName],
                [CustomerAddress],
                [State],
                [CustomerCategoryID],
                [LastBuyDate])
SELECT  
    'Pablo Terry',
    '29 West Milton St.',
    'DE',
    'A',
    GETDATE()

go 15000

4. Execute the following query and take a look at the query plan.

USE TestDB
GO

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = 'A'

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = 'B'




Sample Query Execution

As you can see, the first query performs a clustered index seek on the CustomersCategory table and a clustered index scan on tghe Customers table whereas the second one uses the non-clustered index (IX_Customers_CustomerCategoryID). That is because the query optimizer is smart enough to anticipate our query results from the given parameters and scans the clustered index instead of performing an index seek followed by a key lookup to our non-clustered index, that would be more expensive due to the fact that our first query returns almost the entire table.

5. Now we create a stored procedure to execute our query.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing 
@CustomerCategoryID   CHAR(1)
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID

GO

6. Execute the stored procedure.

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

On the script we first execute the DBCC FREEPROCCACHE to clean the plan cache and then run the stored procedure. Look at the image below and you will see how the second call to the stored procedure uses the same plan without taking into consideration the parameters given.

First Execution of Stored Procedure

After this we perform the same steps, but with the parameters in reverse order and you will see the same behavior, but a different query plan.

Second Execution of Stored Procedure

Workarounds for SQL Server Parameter Sniffing

Now being exposed to the problem here are a few methods to address it:

  • Create SQL Server Stored Procedures using the WITH RECOMPILE Option
  • Use the SQL Server Hint OPTION (RECOMPILE)
  • Use the SQL Server Hint OPTION (OPTIMIZE FOR)
  • Use Dummy Variables on SQL Server Stored Procedures
  • Disable SQL Server Parameter Sniffing at the Instance Level
  • Disable Parameter Sniffing for a Specific SQL Server Query

Create SQL Server Stored Procedures using the WITH RECOMPILE Option

If the problem is that the optimizer uses a plan compiled with parameters that are no longer suitable then a recompilation will create a new plan with the new parameters right? This is the simplest solution, but not one of the best. If the problem is a single query inside the stored procedure code then performing a recompilation of the entire procedure is not the best approach. We should correct the problematic query.

Furthermore, recompilation will increase CPU load and in heavy concurrent systems it could be as problematic as the issue we are trying to solve.

As an example, below is the sample code to create our previous stored procedure with the recompile option.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Recompile 
@CustomerCategoryID   CHAR(1)
WITH RECOMPILE
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID

GO

Use the SQL Server Hint OPTION (RECOMPILE)

As I said in the previous paragraph, recompiling the whole stored procedure is not the best choice.  We can take advantage of the hint RECOMPILE to recompile the awkward query alone.  Look at the sample code below.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Query_Hint_Option_Recompile 
@CustomerCategoryID   CHAR(1)
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(RECOMPILE)

GO

Now execute the following code and look at the query plan.

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID 
GO



Stored Procedure Execution with OPTION(RECOMPILE) hint

As you can see on previous image, we have the right plan according to our parameters on both queries.

Use the SQL Server Hint OPTION (OPTIMIZE FOR)

This hint will allow us to set a parameter value to use as a reference for optimization; you can read about this hint from a tip by Greg Robidoux Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint.  Maybe in our scenario we canít use this hint because we donít know for sure which parameter values the procedure will use to execute. But if you are using SQL Server 2008 or above then OPTIMIZE FOR UNKNOWN will bring some light. I must warn you that it wonít produce the best plan, speaking in layman terms, the resulting plan will be something in between. So to use this hint you must consider how often the stored procedure is being executed with a wrong plan and how much it impacts your environment having a long running query.

Here is the sample code to use this hint.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Query_Hint_Optimize_Unknown 
@CustomerCategoryID   CHAR(1)
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(OPTIMIZE FOR UNKNOWN )

GO

The next script is to execute our new Stored Procedure.

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID 
GO

And here is the screen capture of its execution plan.

Stored Procedure Execution with OPTION(OPTIMIZE FOR UNKNOWN ) hint

Use Dummy Variables on SQL Server Stored Procedures

This is an old method used on SQL Server versions previous to 2005. It consists of assigning the input parameter to a local variable and uses this variable instead of the parameter.

Look at the sample code below.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Dummy_Var 
@CustomerCategoryID   CHAR(1)
AS
DECLARE @Dummy    CHAR(1)

SELECT @Dummy = @CustomerCategoryID

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @Dummy

GO

To execute this Stored Procedure you can use this code.

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Dummy_Var @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Dummy_Var @CustomerCategoryID 
GO

Look at the resulting plan.

Stored Procedure Execution Using Dummy Variables

Disable SQL Server Parameter Sniffing at the Instance Level

Maybe this is the worst choice you can make. As I said before, parameter sniffing is not a bad thing per se and is very useful in most cases to get the best plan. But if you want, starting the instance with trace flag 4136 set will disable parameter sniffing. You can read more about this and the patch level required to use this flag on this KB article: SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 introduce trace flag 4136 that can be used to disable the "parameter sniffing" process.

Before using this method take a look at the next one, itís far less drastic.

Disable Parameter Sniffing for a Specific SQL Server Query

This may be unknown to you, but a query can use a trace flag as a hint to change the behavior of the query optimizer. The way to do this is by adding the QUERYTRACEON hint to the OPTION clause.

Here is the sample stored procedure followed by its execution.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Query_Hint_QUERYTRACEON 
@CustomerCategoryID   CHAR(1)
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(QUERYTRACEON 4136)

GO

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID 
GO

This is a screen capture of the execution plan.

Stored Procedure Execution Using Dummy Variables

Next Steps



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 23, 2018 - 9:19:12 AM - Andres Back To Top

 Groso! Gracias por compartir!!

 


Friday, February 09, 2018 - 4:15:39 AM - Qusai Back To Top

Thank you very much for this awesome article!

I learned something new today :)

 


Wednesday, November 29, 2017 - 10:45:16 AM - Zach Back To Top

Thanks for the info. One question is, for optimal performance, do you have a reccomendation on the best one of these methods?  Assuming I probably do not want to "Disable SQL Server Parameter Sniffing at the Instance Level"

Much thanks.


Tuesday, November 28, 2017 - 5:38:46 PM - Cedrick Back To Top

 This is an awesome article.  As a DBA, I come across these types of issues all of the time, but didn't have the right information to explain to the developer /BA exactly why their query wasn't performing as it normally would.  Now, not only do I have a good explanation, I also have additional options to offer them to get better performance consistency. 

Thanks Daniel!

Cedrick

 


Wednesday, August 02, 2017 - 10:49:11 AM - Bojan Dolinar Back To Top

I currently don't have access to that database, but version is 2012 and compatiblity level probably 100 (an assumption since this is the level of restored db).


Friday, July 28, 2017 - 11:45:39 PM - Daniel Farina Back To Top

Hi Bojan!

What version of SQL Server are you using and what compatibility level?

As far as I remember, you are describing an old issue that back in a time, usually people solved the way you have exposed.

Best Regards!!!

 


Friday, July 28, 2017 - 8:52:13 AM - Bojan Dolinar Back To Top

There is some other trick (from link below) we used and it works, but I don't understand why.

for example, this predicate

WHERE VS.StartDate >= @StartDate
  AND VS.StartDate < @EndDate

was rewritten into seemingly superfluous

WHERE VS.StartDate = VS.StarDate and VS.StartDate >= @StartDate AND
VS.StartDate < @EndDate


Any explanation on why this works? My guess (at least for our own case) would be that it makes it more likely to use index on StartDate, so it uses the right plan the first time around.


https://groups.google.com/forum/?hl=en#!msg/microsoft.public.sqlserver.tools/HZ7B02DGhdA/rO0k7510a1AJ


Monday, March 27, 2017 - 7:41:37 PM - Daniel Farina Back To Top

 

Hi Dave! 

The tip has been fixed. Thank you very much for your feedback!

 

Best Regards!


Friday, March 24, 2017 - 7:06:17 PM - Daniel Farina Back To Top

Hi Dave!

Thank you very much for the corrections! I will send to the editor a fixed version of the scripts!

 


Friday, March 24, 2017 - 11:25:47 AM - Dave Back To Top

 Hi,

 

Well explained article but your walkthrough fails on step 3 when inserting data into two tables:

Cannot insert the value NULL into column 'CustomerID', table 'TestDB.dbo.Customers'; column does not allow nulls. INSERT fails.

Additionally, the semi-colon causes issues in SQL 2014 and later:

A fatal scripting error occurred.Incorrect syntax was encountered while parsing GO.


Tuesday, August 23, 2016 - 5:47:39 AM - Sreeraj Sreevihar Back To Top

Thanks Daniel, Excellent article with clear explanation. Helped a lot.

Regards

Sree

 

 


Friday, March 04, 2016 - 5:09:47 PM - Barry Back To Top

Adding the line OPTION(QUERYTRACEON 4136) to the end of the SELECT statement being used in a stored procedure solved the performance problem.  The report now completes in seconds, not minutes.    

Thank you Daniel for taking the time to post this article!

 

 


Monday, December 21, 2015 - 11:43:56 AM - Channdeep Singh Back To Top

Respected sir- Awesome collections on solutions on this issue. One more possible solution in addition to the solutions mentioned above:

Source: http://thomaslarock.com/2013/02/something-smells-parameter-sniffing/

The typical band-aid applied is an update of the statistics on the underlying tables. Updating the statistics then forces all plans that would use those tables to be recompiled and as a result the very next plan that gets compiled and cached will “win” by being the one every other similar query will use. Depending upon your data distribution this may or may not be a good enough plan for all users and all queries. The likely result of this band-aid approach is to find yourself needing to update statistics again at some point in the future.

 


Monday, February 02, 2015 - 5:24:07 PM - Julie Back To Top

Daniel, you're AWESOME!! I've just spent 3 whole days trying to figure out why my SQL ran perfectly but as soon as I wrapped it in a Stored Procedure with a parameter, it ground to a halt. After numerous testing eventually I figured out it was the difference of using a local variable vs a parameter but I didn't know why to work around it. You have saved me!! I'm very grateful for your generosity in sharing your knowledge. Thank YOU!!


Tuesday, June 24, 2014 - 7:17:52 PM - Joshua Moore Back To Top

This was very helpful in understanding parameter sniffing. I had heard the term thrown around a lot, but never quite wrapped my head around it. Good article!


Learn more about SQL Server tools