20 September 2010

Parameter Sniffing

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:

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: 

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:

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:

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

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:


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:

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: 

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:

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:

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:

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. 

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Tags: , ,


  • Rate
    [Total: 217    Average: 4.4/5]

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.

View all articles by Greg Larsen

  • wilfred van dijk

    Option 4: Enable trace flag 4136

    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

  • Stephen Morris

    You missed the new query hint – Optimise For
    You missed the new query hint – Optimise For
    from BoL


    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.

    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.

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

    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.

    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.

  • Rowland

    A Mixed Bag
    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!

  • sqlpilot

    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.

  • sqlpilot

    oh, another
    I forgot to mention the other approach I sometimes use – generate dynamic SQL with the composed parameter values for sp_executesql

  • sqlpilot

    oh, another
    I forgot to mention the other approach I sometimes use – generate dynamic SQL with the composed parameter values for sp_executesql

  • MyNameIsChris

    You Recompiled both queries
    You used WITH RECOMPILE in option 2 as well. So I don’t see a difference

  • anilvanjre

    Good Article

  • sampath_asealu

    great article
    i like this article very much!!!

  • enjoyanu

    Nice article!
    I understand this and created basic theory post on SQL Server Parameter Sniffing.
    Please visit url of my blog:

  • RichardJM

    Struggled on Azure
    Thanks for the article, it pointed me in the right direction. For others this is what I tried.

    WITH RECOMPILE – no effect
    DECLARE @Param1 … set @Param1 = @Param – no effect
    OPTIMIZE FOR UNKNOWN – no effect
    Fully parameterised sp_executesql – no effect
    Embed specific parameter in string into the sp_executesql – finally effect

    I spotted this as the SELECT statement on it’s own was fine but as soon as it was in a stored proc it failed.