Different Approaches to Correct SQL Server Parameter Sniffing

By:   |   Comments (19)   |   Related: > 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



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, November 15, 2019 - 5:42:38 AM - Aaron Murphy Back To Top (83097)

Am I missing something, or is the execution plan wrong for "Use Dummy Variables on SQL Server Stored Procedures"? It basically explains how using dummy variables will prevent the SP from using the cache and therefore a potentially suboptimal execution plan. However, it then shows a screenshot of the plans for both 'A' and 'B' using the execution plan that is optimal for 'A'. Surely the screenshot should show two different execution plans, with each being the optimal for the parameter?

Saturday, March 30, 2019 - 2:47:39 PM - Ray Herring Back To Top (79427)

Many times you either know in advance or can determine at run time which parameter values are skewed in your database.

For example, we know most customers have a few dozen or so orders but there are some with thousands of orders.  This meta-data can be materialized in a number of ways (e.g. a lookup table) and then different versions of the stored procedure can be called for the special cases.

In our situation, we know the "size" of the customer based on configuration data but even in a more dynmaic environment it is still possible to obtain this meta-data and avoid the whole problem without turning off parameter sniffing.

Also, see Kimberly Tripp's web site at SQL Skills.  She has a lot of great stuff on this topic.

Saturday, October 20, 2018 - 2:40:31 AM - Daniel Farina Back To Top (77993)

 

Hi Gary!

Thanks to you for reading and commenting! Please allow me to quote one paragraph you wrote:

"if the stored proc with one set of parameters runs very slow, will it be consistently slow, or it can be sometimes very fast, sometimes very slow, within one day." 

That is what happens with parameter sniffing! SQL Server builds an execution plan according to the parameters in your query and then SQL Server will keep using this plan until the plan get flushed from the plan cache.

Since you say that if you comment the update part and the query runs fine. You should also take a look at the destination table. I suggest you to take a look at some bulk insert tips. For instance it is always better to create a raw table, fill it with data and then create the indexes.

The SOS_SCHEDULER_YIELD wait type means that the scheduler yields its execution quantum because it is not ready to run. It could be because there are pending IO requests or any other thing.

Also, you must consider that if you run the update query and then run the same query commenting the update part, the SQL Server compiler takes it as two different queries, so you can't rely on this if you are suffering from parameter sniffing. 

Do you consider me worthy to deserve the author of the year award? If som you can vote for me in the next link https://www.mssqltips.com/mssqltips-author-of-year/ .

Thanks for reading and commenting!!!!

Friday, October 19, 2018 - 3:31:35 PM - Gary Tan Back To Top (77987)

Hi Daniel

 

Thanks for sharing, question about the parameter sniffing: 

  if the stored proc with one set of parameters runs very slow, will it be consistently slow, or it can be sometimes very fast, sometimes very slow, within one day. 

  

I got a weird performance issue, we have a stored procedure being called from SSIS, the query inserts data into temp tables and permanent tables. Sometimes the SSIS job completed very fast, but sometimes just hanged there. 

 

If I comment out just the data update part (we only have read-only permission in Production), the the query always completed very fast, even at the time the SSIS job hanged there. 

 

And most of time, from below I can see my query has a very high logic reads number, and the last_wait_type is SOS_SCHEDULER_YIELD

  FROM sys.dm_exec_requests r

  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t

  CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS p

 

Can you tell this is not the case of parameter sniffing any more, or it still might be? 

 

Thanks 

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

 Groso! Gracias por compartir!!

 

Friday, February 9, 2018 - 4:15:39 AM - Qusai Back To Top (75144)

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 (73420)

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 (73367)

 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 2, 2017 - 10:49:11 AM - Bojan Dolinar Back To Top (63777)

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 (63537)

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 (63463)

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 (52779)

 

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 (51618)

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 (51600)

 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 (43168)

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

Regards

Sree

 

 

Friday, March 4, 2016 - 5:09:47 PM - Barry Back To Top (40863)

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 (40286)

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 2, 2015 - 5:24:07 PM - Julie Back To Top (36137)

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 (32380)

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!














get free sql tips
agree to terms