Click here to monitor SSC
  • Av rating:
  • Total votes: 133
  • Total comments: 8
Greg Larsen

Parameter Sniffing

20 September 2010

If a SQL query has parameters, SQL Server creates an execution plan tailored to them to improve performance, via a process called 'parameter sniffing'. This plan is stored and reused since it is usually the best execution plan. Just occasionally, it isn't, and you can then hit performance problems, as Greg Larsen explains.

SQL Server tries to optimize the execution of your stored procedures by creating compiled execution plans.  An execution plan for a stored procedure is created the first time a stored procedure is executed.  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.  The process of looking at parameter values when compiling a stored procedure is commonly called “parameter sniffing”.  Parameter sniffing can lead to inefficient execution plans sometimes; especially when a stored procedure is called with parameter values that have different cardinality.   In 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.

What is Parameter Sniffing?

I am sure that the word “sniffing” provides everyone with a slightly different mental image of what it means; some good and some bad.  Parameter sniffing is the process whereby  SQL 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.  By “first time”, I really mean whenever SQL Server is forced  to compile or recompile  a 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.   

Not all execution plans are created equal.  Execution plans are optimized based what they need to do.  The SQL Server engine looks at a query and determines the optimal strategy for execution.  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.  This is a simplified explanation of how an execution plan is created.  The important point for us is that those parameters passed are used to determine how SQL Server will process the query.   An 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. 

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.   

An Example That Uses Excessive I/O Due to Parameter Sniffing

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

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:

SET NOCOUNT ON;

DROP TABLE BillingInfo;

CREATE TABLE BillingInfo(

ID INT IDENTITY,

BillingDate DATETIME,

BillingAmt MONEY,

BillingDesc varchar(500));

 

DECLARE @I INT;

DECLARE @BD INT;

SET @I = 0;

WHILE @I < 1000000

BEGIN

  SET @I = @I + 1;

  SET @BD=CAST(RAND()*10000 AS INT)%3650;

  INSERT BillingInfo (BillingDate, BillingAmt)

  VALUES (DATEADD(DD,@BD,

    CAST('1999/01/01' AS DATETIME)),

    RAND()*5000);

END

 

ALTER TABLE BillingInfo

  ADD  CONSTRAINT [PK_BillingInfo_ID]

  PRIMARY KEY CLUSTERED (ID);

 

CREATE NONCLUSTERED INDEX IX_BillingDate

  ON dbo.BillingInfo(BillingDate);

In the above code, you can see that I created a table named “BillingInfo”.  I then populated that table with a million different records randomly setting the BillingDate’s and BillingAmt’s columns .  My table has a primary clustered index key on the ID column, and a non-clustered index on BillingDate.  There is about 10 years worth of data in this table, starting with a BillingDate of “1999/01/01”.

To show you how parameter sniffing effects stored procedure executions I will be using this stored procedure: 

CREATE PROC [dbo].[DisplayBillingInfo]

  @BeginDate DATETIME,

  @EndDate DATETIME

AS

SELECT BillingDate, BillingAmt

  FROM BillingInfo

  WHERE BillingDate between @BeginDate AND @EndDate; 

I will execute this stored procedure twice.  Each test will call this stored procedure using a different set of parameter values.   

My first test will run the following statements:

SET STATISTICS IO ON;

DBCC FREEPROCCACHE;

EXEC dbo.DisplayBillingInfo

  @BeginDate = '1999-01-01', 

  @EndDate  = '1999-12-31'; 

 

EXEC dbo.DisplayBillingInfo

  @BeginDate = '2005-01-01', 

  @EndDate  = '2005-01-03';

I turned on statistics so I can show the I/O generated by each execution.  I also ran “DBCC FREEPROCCACHE;” statement so I could make sure my stored procedure was not already in the procedure cache.  This allowed the first EXEC statement to compile my stored procedure. 

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.  Whereas the second call, is only returning three days worth of data.  As already state the compiled execution plan will be based on the first EXEC statement above.  When I run these statements here is the execution plan used for both of these stored procedure executions:

As you can see a “Clustered Index Scan” operation was performed.  Below is the I/O information that was displayed when I ran the script above.  The first set of statistics is associated with the first EXEC statement and the second set of statistics is for the second EXEC statements:

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.

 

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.

 

Now let me run the second test.  Here is the code for the second test:

SET STATISTICS IO ON;

DBCC FREEPROCCACHE;

EXEC dbo.DisplayBillingInfo

  @BeginDate = '2005-01-01', 

  @EndDate  = '2005-01-03';

 

EXEC dbo.DisplayBillingInfo

  @BeginDate = '1999-01-01', 

  @EndDate  = '1999-12-31'; 

Here I have swapped the order of the two different EXEC statements.  This way the first EXEC statement now calls the stored procedure using the smaller date range as parameters.  This short date range will be the one that are sniffed in order to create the compiled execution plan.  When I run this test, here is the execution plan that each stored procedure execution will use:

Here you can see that this time an Index Seek operation was used to resolve the query.  Below are the statistics for the second test:

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.

 

Table '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.

 

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

Using two different tests created different compiled execution plan depending on the parameters passed when the stored procedure was compiled.   By 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.  Let me next explore some options you can take to resolve the issue caused by parameter sniffing. 

How to Deal With Parameter Sniffing

There are a number of ways to deal with the parameter sniffing issue.  Keep in mind all options may not be acceptable in every situation.   Let me walk you through each option.

Option 1: With Recompile

The problem with parameter sniffing is the fact that the first set of parameter values are used to determine the execution plan.  To overcome this problem, all you need to do is to recompile the stored procedure every time it is executed.  This can be accomplished by using the “WITH RECOMPILE” options when you create a stored procedure, like so:

DROP PROC [dbo].[DisplayBillingInfo]

GO

CREATE PROC [dbo].[DisplayBillingInfo]

  @BeginDate DATETIME,

  @EndDate DATETIME

WITH RECOMPILE

AS

SELECT BillingDate, BillingAmt

  FROM BillingInfo

  WHERE BillingDate between @BeginDate AND @EndDate; 

Once you have dropped and re-created the DisplayBillingInfo stored procedure, then you can test out what happens with the recompile option.  This can be done by running the following code, while including the actual execution plan information: 

DBCC FREEPROCCACHE;

EXEC dbo.DisplayBillingInfo

  @BeginDate = '2005-01-01', 

  @EndDate  = '2005-01-03';

 

EXEC dbo.DisplayBillingInfo

  @BeginDate = '1999-01-01', 

  @EndDate  = '1999-12-31';

 

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. 

The drawback of this option is the store procedure is recompiled with every execution.  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. 

Option 2: Disabling Parameter Sniffing

Another method of resolving the parameter sniffing issue is to disable parameter sniffing altogether.  This is not done with a switch or database option, but can be done from within the script of  your stored procedure code.  Here is an example of how I created my stored procedure so parameter sniffing is disabled:

DROP PROC [dbo].[DisplayBillingInfo]

GO

CREATE PROC [dbo].[DisplayBillingInfo]

  @BeginDate DATETIME,

  @EndDate DATETIME

WITH RECOMPILE

AS

DECLARE @StartDate DATETIME;

DECLARE @StopDate DATETIME;

SET @StartDate = @BeginDate;

SET @StopDate = @EndDate;

SELECT BillingDate, BillingAmt

  FROM BillingInfo

  WHERE BillingDate between @StartDate AND @StopDate; 

To disable parameter sniffing, all I did was to change the way the parameter values were used within the stored procedure.  By creating two different local variables (@StartDate and @EndDate) inside my procedure, setting those variables to the passed parameters, and then using the local variables in the BETWEEN condition, I was able to disable parameter sniffing.  Parameter sniffing is disabled because the optimizer is not able to identify the parameters’ values in the actual SELECT statement.  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. 

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

Option 3: Creating Multiple Stored Procedures

As I said before, there is no ideal solution to resolving the problems with parameter sniffing.  This option uses multiple stored procedures where each stored procedure can be optimize for a specific type of parameters values. 

In my example, I have two different sets of parameters.  One set of parameters has a short range of dates, where the dates are between 2005-01-01 and 2005-01-03.  Whereas the second set of dates have a large range between 1999-01-01 and 1999-12-31.  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.  In order to optimize the performance for these two different date ranges we will need to create two different stored procedures; one for each.  We’ll then follow by having a third stored procedure that determine which stored procedure to call, basing the decision  on the number of days between the @BeginDate and @EndDate parameter values.  Here are my three stored procedures:

CREATE PROC [dbo].[DisplayBillingInfoNarrow]

  @BeginDate DATETIME,

  @EndDate DATETIME

AS

SELECT BillingDate, BillingAmt

  FROM BillingInfo

  WHERE BillingDate between @BeginDate AND @EndDate; 

GO

CREATE PROC [dbo].[DisplayBillingInfoWide]

  @BeginDate DATETIME,

  @EndDate DATETIME

AS

SELECT BillingDate, BillingAmt

  FROM BillingInfo

  WHERE BillingDate between @BeginDate AND @EndDate; 

GO 

DROP PROCEDURE [dbo].[DisplayBillingInfo];

GO 

CREATE PROC [dbo].[DisplayBillingInfo]

  @BeginDate DATETIME,

  @EndDate DATETIME

AS

IF DATEDIFF(DD,@BeginDate, @EndDate) < 4

  EXECUTE DisplayBillingInfoNarrow @BeginDate, @EndDate

ELSE

  EXECUTE DisplayBillingInfoWide @BeginDate, @EndDate

GO

 

The first procedure I created  was DisplayBillingInfoNarrow and the second  was DisplayBillingInfoWide. They are identical, except for the name.  The third stored procedure DisplayBillingInfo has the same name as my original stored procedure, but logic in the stored procedure is different.  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, otherwise it calls the wide stored procedure DisplayBillingInfoWide. 

To test out which index operation is performed now, I can run my normal test code:

DBCC FREEPROCCACHE;

EXEC dbo.DisplayBillingInfo

  @BeginDate = '2005-01-01', 

  @EndDate  = '2005-01-03';

 

EXEC dbo.DisplayBillingInfo

  @BeginDate = '1999-01-01', 

  @EndDate  = '1999-12-31';

 

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. 

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.  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.  Of course using this method does require you to maintain multiple stored procedures, which does cause additional overhead. 

Overcoming Issues Related to Parameter Sniffing

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

Greg Larsen

Author profile:

Greg started working in the computer industry in 1982. In 1985, he got his first DBA job, and since then he has held five different DBA jobs and managed a number of different database management systems. Currently works as a DBA for Department of Health in Washington State managing SQL Server databases, and also does part-time consulting. He has published numerous articles in SQL Server Magazine, and many online web sites dedicated to SQL Server. He also is a SQL Server MVP and holds a number of Microsoft Certification. Greg can be reached at gregalarsen@msn.com.

Search for other articles by Greg Larsen

Rate this article:   Avg rating: from a total of 133 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Option 4: Enable trace flag 4136
Posted by: wilfred van dijk (view profile)
Posted on: Monday, September 20, 2010 at 8:47 AM
Message: Hi,

Starting with MSSQL 2005 SP3 CU9 (2008 also), you have the possibility to disable parameter sniffing by setting a traceflag. See http://support.microsoft.com/kb/980653

Subject: You missed the new query hint - Optimise For
Posted by: Stephen Morris (not signed in)
Posted on: Thursday, September 23, 2010 at 1:20 AM
Message: You missed the new query hint - Optimise For
from BoL

<snip>

OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.

@variable_name
Is the name of a local variable used in a query, to which a value may be assigned for use with the OPTIMIZE FOR query hint.

UNKNOWN
Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.

literal_constant
Is a literal constant value to be assigned @variable_name for use with the OPTIMIZE FOR query hint. literal_constant is used only during query optimization, and not as the value of @variable_name during query execution. literal_constant can be of any SQL Server system data type that can be expressed as a literal constant. The data type of literal_constant must be implicitly convertible to the data type that @variable_name references in the query.

OPTIMIZE FOR can counteract the default parameter detection behavior of the optimizer or can be used when you create plan guides. For more information, see Recompiling Stored Procedures and Optimizing Queries in Deployed Applications by Using Plan Guides.

OPTIMIZE FOR UNKNOWN
Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization. For more information about forced parameterization, see Forced Parameterization.

If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.

Subject: A Mixed Bag
Posted by: Rowland (view profile)
Posted on: Wednesday, March 14, 2012 at 8:07 AM
Message: Nice article Greg! No one has addressed this age-old problem as well as you did.

Some of the other comments gave me pause:
1) Using the magic of trace flags to solve problems in a wholesale way has never been my cup of tea. I'd rather deal with problems one by one than create whole batches of them with that approach

2) Not really a fan of these either. Hints like OPTIMIZE FOR have their place until the forced optimization no longer fits the bigger picture.

3) WITH RECOMPILE guarantees less than stellar performance for similar reasons as #2. In some ways I think it's worse.

4) I've always liked the parameter bait-and-switch approach for stubborn procedures.

5) I really liked the top-level-procedure-that-calls-optimized-procedures approach. This is the best overall solution in my opinion.

Thanks again Greg!

Subject: Great
Posted by: sqlpilot (view profile)
Posted on: Thursday, July 26, 2012 at 2:28 PM
Message: Hey Greg. Great article and reference for me to send people as I talk about sniffing too. Just yesterday I gave the 60 second description of parm. sniffing to a co-worker. I fired off this link to him for a more depth understanding.

And regarding OPTIMIZE for hints, trace flags, etc... as David DeWitt has said: these reflect the inadequacies or failure of SQL's optimizer and are band-aids at best. While they may work sometimes, I avoid them at all possible, especially a trace flag.

Subject: oh, another
Posted by: sqlpilot (view profile)
Posted on: Thursday, July 26, 2012 at 2:30 PM
Message: I forgot to mention the other approach I sometimes use - generate dynamic SQL with the composed parameter values for sp_executesql

Subject: oh, another
Posted by: sqlpilot (view profile)
Posted on: Thursday, July 26, 2012 at 2:41 PM
Message: I forgot to mention the other approach I sometimes use - generate dynamic SQL with the composed parameter values for sp_executesql

Subject: You Recompiled both queries
Posted by: MyNameIsChris (view profile)
Posted on: Wednesday, May 22, 2013 at 8:26 AM
Message: You used WITH RECOMPILE in option 2 as well. So I don't see a difference

Subject: Good
Posted by: anilvanjre (view profile)
Posted on: Thursday, June 27, 2013 at 4:47 PM
Message: Good Article

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.