{"id":993,"date":"2010-09-20T00:00:00","date_gmt":"2010-09-20T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/parameter-sniffing\/"},"modified":"2026-04-17T14:07:09","modified_gmt":"2026-04-17T14:07:09","slug":"parameter-sniffing","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/parameter-sniffing\/","title":{"rendered":"SQL Server Parameter Sniffing: What It Is and How to Fix It"},"content":{"rendered":"\n
\n

Parameter sniffing is the process by which SQL Server creates an execution plan for a stored procedure using the parameter values passed on the first execution, then caches and reuses that plan for all subsequent calls. This is an intentional optimisation – but it backfires when the initial parameters represent an atypical case. If the first execution uses a parameter value that selects very few rows, SQL Server may cache an Index Seek plan that performs poorly when later executions use a value that selects many rows. The result: a stored procedure that runs fast sometimes and inexplicably slowly at other times, depending on what query happened to run first after the plan was last compiled.<\/strong><\/p>\n

SQL Server tries to optimize the execution of your stored procedures by creating compiled execution plans.\u00a0 An execution plan for a stored procedure is created the first time a stored procedure is executed.\u00a0 When the SQL Server database engine compiles a stored procedure it looks at the parameter values being passed and creates an execution plan based on these parameters.\u00a0 The process of looking at parameter values when compiling a stored procedure is commonly called “parameter sniffing”.\u00a0 Parameter sniffing can lead to inefficient execution plans sometimes; especially when a stored procedure is called with parameter values that have different cardinality.\u00a0 \u00a0In this article, I will explain what parameter sniffing is, and then explore different ways of dealing with the performance problems that are occasionally caused to parameter sniffing.<\/p>\n

What is Parameter Sniffing?<\/b><\/h1>\n

I am sure that the word “sniffing” provides everyone with a slightly different mental image of what it means; some good and some bad.\u00a0 Parameter sniffing is the process whereby \u00a0SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed.\u00a0 By “first time”, I really mean whenever SQL Server is forced \u00a0to compile or recompile \u00a0a stored procedures because it is not in the procedure cache. Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan.\u00a0 \u00a0<\/p>\n

Not all execution plans are created equal.\u00a0 Execution plans are optimized based what they need to do.\u00a0 The SQL Server engine looks at a query and determines the optimal strategy for execution.\u00a0 It looks at what the query is doing, uses the parameter values to look at the statistics, does some calculations and eventually decides on what steps are required to resolve the query.\u00a0 This is a simplified explanation of how an execution plan is created.\u00a0 The important point for us is that those parameters passed are used to determine how SQL Server will process the query.\u00a0 \u00a0An optimal execution plan for one set of parameters might be an index scan operation, whereas another set of parameters might be better resolved using an index seek operation.\u00a0<\/p>\n

To further, understand parameter sniffing, let me show you an example that demonstrates how parameter-sniffing causes a stored procedure to use more resources based on the parameters passed.\u00a0 \u00a0<\/p>\n

An Example That Uses Excessive I\/O Due to Parameter Sniffing<\/b><\/h1>\n

To demonstrate parameter sniffing and the performance differences when different parameters are used, let me show you an example. My example will use two different sets of parameters to call my stored procedure.\u00a0 I will show you how the second execution call will consume more I\/O, then the originally passed parameters. The difference will depend on the set of parameters used to compile the stored procedure<\/p>\n

You may also be interested in:<\/strong> How SQL Server execution plans affect FULL OUTER JOIN performance<\/a><\/p>\n

Prior to showing you my stored procedure, let me first provide you the code I used to create and populate my table from which my stored procedure selected data:<\/p>\n

SET NOCOUNT ON;\nDROP TABLE BillingInfo;\nCREATE TABLE BillingInfo(\nID INT IDENTITY,\nBillingDate DATETIME,\nBillingAmt MONEY,\nBillingDesc varchar(500));\n\u00a0\nDECLARE @I INT;\nDECLARE @BD INT;\nSET @I = 0;\nWHILE @I < 1000000 \nBEGIN\n\u00a0 SET @I = @I + 1;\n\u00a0 SET @BD=CAST(RAND()*10000 AS INT)%3650;\n\u00a0 INSERT BillingInfo (BillingDate, BillingAmt) \n\u00a0 VALUES (DATEADD(DD,@BD,\n\u00a0 \u00a0 CAST('1999\/01\/01' AS DATETIME)),\n\u00a0 \u00a0 RAND()*5000);\nEND \n\u00a0\nALTER TABLE BillingInfo \n\u00a0 ADD\u00a0 CONSTRAINT [PK_BillingInfo_ID] \n\u00a0 PRIMARY KEY CLUSTERED (ID);\n\u00a0\nCREATE NONCLUSTERED INDEX IX_BillingDate\n\u00a0 ON dbo.BillingInfo(BillingDate);\n<\/pre>\n
In the above code, you can see that I created a table named “BillingInfo<\/strong>“.\u00a0 I then populated that table with a million different records randomly setting the BillingDate<\/strong>‘s and BillingAmt<\/strong>‘s columns .\u00a0 My table has a primary clustered index key on the ID column, and a non-clustered index on BillingDate<\/strong>.\u00a0 There is about 10 years worth of data in this table, starting with a BillingDate<\/strong> of “1999\/01\/01”.<\/p>\n
To show you how parameter sniffing effects stored procedure executions I will be using this stored procedure:\u00a0<\/p>\n
CREATE PROC [dbo].[DisplayBillingInfo]\n\u00a0 @BeginDate DATETIME,\n\u00a0 @EndDate DATETIME\nAS\nSELECT BillingDate, BillingAmt\n\u00a0 FROM BillingInfo\n\u00a0 WHERE BillingDate between @BeginDate AND @EndDate;\u00a0 \n<\/pre>\n
I will execute this stored procedure twice.\u00a0 Each test will call this stored procedure using a different set of parameter values.\u00a0 \u00a0<\/p>\n
My first test will run the following statements:<\/p>\n
SET STATISTICS IO ON;\nDBCC FREEPROCCACHE;\nEXEC dbo.DisplayBillingInfo \n\u00a0 @BeginDate = '1999-01-01',\u00a0 \n\u00a0 @EndDate\u00a0 = '1999-12-31';\u00a0 \n\u00a0 \nEXEC dbo.DisplayBillingInfo \n\u00a0 @BeginDate = '2005-01-01',\u00a0 \n\u00a0 @EndDate\u00a0 = '2005-01-03';\n<\/pre>\n
I turned on statistics so I can show the I\/O generated by each execution.\u00a0 I also ran “DBCC FREEPROCCACHE;” statement so I could make sure my stored procedure was not already in the procedure cache.\u00a0 This allowed the first EXEC statement to compile my stored procedure.\u00a0<\/p>\n
By looking at my two different stored procedure calls you can see the first execution has a begin date and an end date that represents a years’ worth of billing information.\u00a0 Whereas the second call, is only returning three days worth of data.\u00a0 As already state the compiled execution plan will be based on the first EXEC statement above.\u00a0 When I run these statements here is the execution plan used for both of these stored procedure executions:<\/p>\n
\"1142-imgA.jpg\"<\/figure>\n
\u00a0<\/p>\n
As you can see a “Clustered Index Scan” operation was performed.\u00a0 Below is the I\/O information that was displayed when I ran the script above.\u00a0 The first set of statistics is associated with the first EXEC statement and the second set of statistics is for the second EXEC statements:<\/p>\n
Table 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\nTable 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\n<\/pre>\n
Now let me run the second test.\u00a0 Here is the code for the second test:<\/p>\n
SET STATISTICS IO ON;\nDBCC FREEPROCCACHE;\nEXEC dbo.DisplayBillingInfo \n\u00a0 @BeginDate = '2005-01-01',\u00a0 \n\u00a0 @EndDate\u00a0 = '2005-01-03';\n\u00a0 \nEXEC dbo.DisplayBillingInfo \n\u00a0 @BeginDate = '1999-01-01',\u00a0 \n\u00a0 @EndDate\u00a0 = '1999-12-31';\u00a0 \n<\/pre>\n
Here I have swapped the order of the two different EXEC statements.\u00a0 This way the first EXEC statement now calls the stored procedure using the smaller date range as parameters.\u00a0 This short date range will be the one that are sniffed in order to create the compiled execution plan.\u00a0 When I run this test, here is the execution plan that each stored procedure execution will use:<\/p>\n
\"1142-imgE.jpg\"<\/figure>\n
\u00a0<\/p>\n
Here you can see that this time an Index Seek operation was used to resolve the query.\u00a0 Below are the statistics for the second test:<\/p>\n
Table 'BillingInfo'. Scan count 1, logical reads 2965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n\u00a0\nTable 'BillingInfo'. Scan count 1, logical reads 337040, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\n<\/pre>\n
Note that this time, using an Index Seek operation, my first query performed less logical reads to resolve the stored procedure execution using the small date range, then an index scan operation took in the first test.\u00a0 However, for the one year date range the Index Seek operation was much more I\/O intensive compared to the first test, and performed almost 100 times more I\/O to resolve the query.\u00a0<\/p>\n
Using two different tests created different compiled execution plan depending on the parameters passed when the stored procedure was compiled.\u00a0 \u00a0By looking at these two different tests, you should have a better understanding of parameter sniffing and the effects it can have on your store procedure performance.\u00a0 Let me next explore some options you can take to resolve the issue caused by parameter sniffing.\u00a0<\/p>\n
How to Deal With Parameter Sniffing<\/b><\/h1>\n
There are a number of ways to deal with the parameter sniffing issue.\u00a0 Keep in mind all options may not be acceptable in every situation. Let me walk you through each option.<\/p>\n
You may also be interested in:<\/strong> Why DISTINCT in JOIN-heavy queries creates sort operations that amplify plain instability<\/a><\/p>\n
Option 1: With Recompile<\/i><\/h3>\n
\n
The problem with parameter sniffing is the fact that the first set of parameter values are used to determine the execution plan.\u00a0 To overcome this problem, all you need to do is to recompile the stored procedure every time it is executed.\u00a0 This can be accomplished by using the “WITH RECOMPILE” options when you create a stored procedure, like so:<\/p>\n
DROP PROC [dbo].[DisplayBillingInfo]\nGO\nCREATE PROC [dbo].[DisplayBillingInfo]\n\u00a0 @BeginDate DATETIME,\n\u00a0 @EndDate DATETIME\nWITH RECOMPILE\nAS \nSELECT BillingDate, BillingAmt\n\u00a0 FROM BillingInfo\n\u00a0 WHERE BillingDate between @BeginDate AND @EndDate;\u00a0 \n<\/pre>\n
Once you have dropped and re-created the DisplayBillingInfo<\/b> stored procedure, then you can test out what happens with the recompile option.\u00a0 This can be done by running the following code, while including the actual execution plan information:\u00a0<\/p>\n
DBCC FREEPROCCACHE;\nEXEC dbo.DisplayBillingInfo \n\u00a0 @BeginDate = '2005-01-01',\u00a0 \n\u00a0 @EndDate\u00a0 = '2005-01-03';\n\u00a0 \nEXEC dbo.DisplayBillingInfo \n\u00a0 @BeginDate = '1999-01-01',\u00a0 \n\u00a0 @EndDate\u00a0 = '1999-12-31';\n\u00a0 \n<\/pre>\n
When you run this code, you will find that the first execution performs an index seek operation and the second execution performs an index scan operation.\u00a0<\/p>\n
The drawback of this option is the store procedure is recompiled with every execution.\u00a0 This means, you are incurring additional system resources to compile this procedure with each execution. Depending on the performance gains you get with different sets of parameter values, will determine if there is value in using this option to overcome the parameter sniff issue.\u00a0<\/p>\n<\/div>\n
Option 2: Disabling Parameter Sniffing<\/i><\/h3>\n
\n
Another method of resolving the parameter sniffing issue is to disable parameter sniffing altogether.\u00a0 This is not done with a switch or database option, but can be done from within the script of \u00a0your stored procedure code.\u00a0 Here is an example of how I created my stored procedure so parameter sniffing is disabled:<\/p>\n
DROP PROC [dbo].[DisplayBillingInfo]\nGO\nCREATE PROC [dbo].[DisplayBillingInfo]\n\u00a0 @BeginDate DATETIME,\n\u00a0 @EndDate DATETIME\nWITH RECOMPILE\nAS \nDECLARE @StartDate DATETIME;\nDECLARE @StopDate DATETIME;\nSET @StartDate = @BeginDate;\nSET @StopDate = @EndDate;\nSELECT BillingDate, BillingAmt\n\u00a0 FROM BillingInfo\n\u00a0 WHERE BillingDate between @StartDate AND @StopDate;\u00a0 \n<\/pre>\n
To disable parameter sniffing, all I did was to change the way the parameter values were used within the stored procedure.\u00a0 By creating two different local variables (@StartDate<\/strong> and @EndDate<\/strong>) inside my procedure, setting those variables to the passed parameters, and then using the local variables in the BETWEEN<\/strong> condition, I was able to disable parameter sniffing.\u00a0 Parameter sniffing is disabled because the optimizer is not able to identify the parameters’ values in the actual SELECT<\/strong> statement.\u00a0 Because SQL Server cannot tell what parameter values where used to call the stored procedure, the optimizer creates a generic plan based on the statistics.\u00a0<\/p>\n
When I execute my stored procedure using the code above, using either a narrow range of dates or a years’ worth of dates, the compiled execution plan always does an “index scan” operation.\u00a0 I can tell parameter sniff is turned off because I know that the short range of dates would normally have created an index seek operation.\u00a0 <\/i><\/p>\n<\/div>\n
Option 3: Creating Multiple Stored Procedures<\/i><\/h3>\n
\n
As I said before, there is no ideal solution to resolving the problems with parameter sniffing.\u00a0 This option uses multiple stored procedures where each stored procedure can be optimize for a specific type of parameters values.\u00a0<\/p>\n
In my example, I have two different sets of parameters.\u00a0 One set of parameters has a short range of dates, where the dates are between 2005-01-01 and 2005-01-03.\u00a0 Whereas the second set of dates have a large range between 1999-01-01 and 1999-12-31.\u00a0 We know that the optimal plan for the first range of dates was to perform an ‘index seek’ operation, and the second range is more optimal if a index scan operation is performed.\u00a0 In order to optimize the performance for these two different date ranges we will need to create two different stored procedures; one for each.\u00a0 We’ll then follow by having a third stored procedure that determine which stored procedure to call, basing the decision \u00a0on the number of days between the @BeginDate<\/strong> and @EndDate<\/strong> parameter values.\u00a0 Here are my three stored procedures:<\/p>\n
CREATE PROC [dbo].[DisplayBillingInfoNarrow]\n\u00a0 @BeginDate DATETIME,\n\u00a0 @EndDate DATETIME\nAS \nSELECT BillingDate, BillingAmt\n\u00a0 FROM BillingInfo\n\u00a0 WHERE BillingDate between @BeginDate AND @EndDate;\u00a0 \nGO\nCREATE PROC [dbo].[DisplayBillingInfoWide]\n\u00a0 @BeginDate DATETIME,\n\u00a0 @EndDate DATETIME\nAS \nSELECT BillingDate, BillingAmt\n\u00a0 FROM BillingInfo\n\u00a0 WHERE BillingDate between @BeginDate AND @EndDate;\u00a0 \nGO\u00a0 \nDROP PROCEDURE [dbo].[DisplayBillingInfo];\nGO\u00a0 \nCREATE PROC [dbo].[DisplayBillingInfo]\n\u00a0 @BeginDate DATETIME,\n\u00a0 @EndDate DATETIME\nAS \nIF DATEDIFF(DD,@BeginDate, @EndDate) < 4\n\u00a0 EXECUTE DisplayBillingInfoNarrow @BeginDate, @EndDate\nELSE\n\u00a0 EXECUTE DisplayBillingInfoWide @BeginDate, @EndDate\nGO\n\u00a0\n<\/pre>\n
The first procedure I created \u00a0was DisplayBillingInfoNarrow<\/b> and the second \u00a0was DisplayBillingInfoWide<\/b>. They are identical, except for the name.\u00a0 The third stored procedure DisplayBillingInfo<\/b> has the same name as my original stored procedure, but logic in the stored procedure is different.\u00a0 The code now determines the number of days between the @BeginDate and @EndDate and if it is less than four it called the narrow stored procedure DisplayBillingInfoNarrow<\/b>, otherwise it calls the wide stored procedure DisplayBillingInfoWide<\/b>.\u00a0<\/p>\n
To test out which index operation is performed now, I can run my normal test code:<\/p>\n
DBCC FREEPROCCACHE;\nEXEC dbo.DisplayBillingInfo \n\u00a0 @BeginDate = '2005-01-01',\u00a0 \n\u00a0 @EndDate\u00a0 = '2005-01-03';\n\u00a0 \nEXEC dbo.DisplayBillingInfo \n\u00a0 @BeginDate = '1999-01-01',\u00a0 \n\u00a0 @EndDate\u00a0 = '1999-12-31';\n\u00a0\n<\/pre>\n
When I run this test and display the actual execution plan information I can see that the first EXEC statement does an index seek operation, and the second call does an index scan operation. Additionally, if I try different date ranges, with a date range of less than 4 days an index seek operation will be always be performed and any range that is greater than or equal to 4 days will perform an Index Scan operation.\u00a0<\/p>\n
Using multiple stored procedures allowed me to point the execution call to the appropriate stored procedure that was compiled the first time with either a narrow range, or a wide range.\u00a0 Doing this allowed me to ensure that each time my stored procedure was run, regardless of the parameter values, it would call the stored procedure with the efficient plan for that execution call.\u00a0 Of course using this method does require you to maintain multiple stored procedures, which does cause additional overhead.\u00a0<\/p>\n<\/div>\n
Overcoming Issues Related to Parameter Sniffing<\/b><\/h1>\n
Cached plans that are created based on one set of parameters may not always run optimally for a different set of parameters, due to parameter sniffing.\u00a0 You should now have a better understanding of how parameter sniffing might make your application use more resources depending on what parameters where use when a stored procedure was executed the first time.\u00a0 If you should find a stored procedure that is performing poorly due to parameter sniffing, now you have options for how you might re-write your code to overcome the issues related to parameter sniffing.\u00a0<\/p>\n
You may also be interested in:<\/strong> How to automate extended events data collection to diagnose plan cache issues<\/a><\/p>\n<\/div>\n\n\n\n
\n    
FAQs: Parameter Sniffing in SQL Server<\/h2>\n\n                        
1. What causes parameter sniffing problems in SQL Server?<\/h3>\n            
\n                
Parameter sniffing becomes a problem when the execution plan compiled for the first set of parameter values is suboptimal for a different, later set. It most commonly occurs with stored procedures that have skewed data distributions – where some parameter values select a handful of rows (favouring Index Seek) while others select large portions of the table (favouring Index Scan or Clustered Index Scan). The sniffed plan serves one case well and the other poorly.<\/p>\n            <\/div>\n                    
2. How do I tell if parameter sniffing is causing slow queries in SQL Server?<\/h3>\n            
\n                
The diagnostic signature is a stored procedure that runs fast when called with certain parameters and slow with others, while the underlying data and indexes have not changed. To confirm, query sys.dm_exec_cached_plans and sys.dm_exec_query_stats to inspect the cached plan for the procedure. Compare the actual execution plan generated under different parameter values – if they differ significantly in operator choice (Seek vs Scan), sniffing is the likely cause.<\/p>\n            <\/div>\n                    
3. What is the best fix for SQL Server parameter sniffing?<\/h3>\n            
\n                
There is no universal best fix – it depends on your scenario. WITH RECOMPILE on the stored procedure or query forces a fresh plan every execution, eliminating reuse entirely (best when executions are infrequent and optimal plans vary widely). OPTIMIZE FOR UNKNOWN uses average statistics rather than actual parameter values (best for general-purpose procedures). Local variable assignment inside the procedure obscures the parameter values from the plan compiler (a pragmatic workaround). Multiple specialised procedures are the cleanest architectural solution for procedures where a small number of discrete parameter ranges need fundamentally different plans.<\/p>\n            <\/div>\n                    
4. Does OPTIMIZE FOR UNKNOWN always solve parameter sniffing?<\/h3>\n            
\n                
No. OPTIMIZE FOR UNKNOWN tells SQL Server to compile a plan using statistical averages rather than actual parameter values. This produces a plan that is reliably mediocre for all inputs – neither the best plan for low-selectivity parameters nor the best for high-selectivity ones. It is appropriate when the mediocre plan is acceptable across the full input range and you want to avoid plan instability. It is not appropriate when one end of the parameter range requires a fundamentally different query strategy.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
SQL Server parameter sniffing occurs when stored procedures cache execution plans optimised for the first set of parameter values used, then reuse that plan for all subsequent calls – even when different values would benefit from a different plan. Learn to detect it and three ways to fix it.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4151,4252],"coauthors":[11330],"class_list":["post-993","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/993","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=993"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/993\/revisions"}],"predecessor-version":[{"id":110017,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/993\/revisions\/110017"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=993"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=993"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}