Parameter Sniffing

What is “parameter sniffing”? When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as “parameter sniffing”.

This is good as long as the input parameters for the first invocation are typical for future invocations. But if that is not the case this will cause performance problems.

For example, a procedure is supposed to retrieve all rows for customer orders with non-clustered index on the customer column. If the first invocation returns a small set of orders it may be most efficient to use index seek. Further invocations may be for large set of orders, but the first cached plan with index seek will be used resulting in poor performance (instead of using a scan).

Here is one example stored procedure and different methods to handle parameter sniffing.

CREATE PROCEDURE GetCustomerOrders

 @customerid NCHAR(5)

AS

BEGIN

 

    SELECT orderid, customerid, orderdate, shippeddate

    FROM Orders

    WHERE customerid = @customerid;

 

END

Replace parameters with local variables

This solution is based on assigning the stored procedure parameters to local variables and then using the local variables in the query. This works because SQL Server is not sniffing local variables and using the local variables in place of parameters forces plan generated based on statistics (in effect this disables parameter sniffing).

CREATE PROCEDURE GetCustomerOrders

 @customerid NCHAR(5)

AS

BEGIN

 

    DECLARE @local_customerid NCHAR(5);

 

    SET @local_customerid = @customerid;

 

    SELECT orderid, customerid, orderdate, shippeddate

    FROM Orders

    WHERE customerid = @local_customerid;

 

END

Execute using WITH RECOMPILE

This solution forces recompile of the stored procedure on each run, that way forcing a fresh query plan for the current parameters. Note that this will recompile all statements inside the stored procedure.

EXEC GetCustomerOrders @customerid = N'CACYK' WITH RECOMPILE;

Query hint RECOMPILE

SQL Server 2005 offers the new query hint RECOMPILE which will force recompilation of the individual query. This method is better than the prior method because recompilation will affect only one statement and all other queries in the stored procedure will not be recompiled.

CREATE PROCEDURE GetCustomerOrders

 @customerid NCHAR(5)

AS

BEGIN

 

    SELECT orderid, customerid, orderdate, shippeddate

    FROM Orders

    WHERE customerid = @customerid

    OPTION (RECOMPILE);

 

END

Query hint OPTIMIZE FOR

Another new query hint in SQL Server 2005 is OPTIMIZE FOR. It allows specifying a constant that will be used to optimize the query plan instead of the variable. This could be useful if it is known that particular selective value is frequently used to invoke the stored procedure. However, any other parameter value will suffer the same performance problems.

CREATE PROCEDURE GetCustomerOrders

 @customerid NCHAR(5)

AS

BEGIN

 

    SELECT orderid, customerid, orderdate, shippeddate

    FROM Orders

    WHERE customerid = @customerid

    OPTION (OPTIMIZE FOR (@customerid = N'CACYK'));

 

END

Note: SQL Server 2008 adds a new option to specify “OPTION (OPTIMIZE FOR UNKNOWN)”. This specifies that the query optimizer will use statistical data instead of the initial value to determine the value for query optimization.

Plan Guides

Plan guides in SQL Server 2005 provide the opportunity to optimize a query without changing the actual code of the query. This is especially useful when dealing with third party vendor applications where access to code may not be available. A plan guide allows associating query hints with a query without changing the query.

EXEC sp_create_plan_guide

    @name = N'SolveParameterSniffing',

    @stmt = N'SELECT orderid, customerid, orderdate, shippeddate

               FROM Orders

               WHERE customerid = @customerid',

    @type = N'OBJECT',

    @module_or_batch = N'GetCustomerOrders',

    @params = NULL,

    @hints = N'OPTION (RECOMPILE)';

USE PLAN query hint
Another plan stability feature in SQL Server 2005 is the USE PLAN “xml_plan” query hint, which allows forcing the use of a specific plan every time the query is run.

Additional Resources:

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

12 replies
  1. Anonymous
    Anonymous says:

    Reading your article, I found out I had a 'parameter sniffing' problem.

    I tried local variables as you mentioned and it really speeds up my queries. Thanks a lot.

    Reply
  2. Ric
    Ric says:

    Very cool, had the same problem, switched to using 'local' variables and all is well again, still a bit confused about the whole situation, so will continue to read around what is happening!

    Thanks.

    Reply
  3. DOUG LUBEY
    DOUG LUBEY says:

    10,000% IMPROVEMENT

    THANKS FOR THE TIP; THE LOCAL VARIABLES DID NOT FIX MY PROBLEM.
    BUT SHED SOME LIGHT ON IT;

    ALSO TRY AND USE SPECIFIC FILTERS IN PLACE OF >=.

    EXAMPLE> MY LOGICAL READS WERE IN THE 100,000+ RANGE WHEN UTILIZING THE >= CLAUSE FOR ONE OF MY FILTERS.

    CORRECTION> I CHANGE
    [TDATE] >= GETDATE()

    TO BE:
    [TDATE] BETWEEN GEDATE()+ DATEADD(YEAR,40,GETDATE())

    THE IMPACT WAS REDUCED BY LOGICAL READS TO BE ABOUT 1000…

    CRAZY IMPROVEMENT…I TRIED SO MANY OTHER TECHNIQUES AND FINALLY DETERMINED THIS WAS SO SIMPLE BUT OVERLLOKED.

    THANKS,
    DOUG LUBEY OF LOUISIANA

    REFERENCE:
    total_logical_reads
    total_physcial_reads

    Reply
  4. Anonymous
    Anonymous says:

    Thanks a lot. This article is very helpful and saved the performance issues we had on live. The solution I implemented was "Replace parameters with local variables"

    Reply
  5. Anonymous
    Anonymous says:

    It really works! Thanks a lot!!!!
    My query with a lot of filters and grouping case takes just 7 seconds. But my stored procedure with the SAME query inside had run longer than 5 minutes! So, I've followed parameter sniffing advice and used local variables, and now my stored procedure runs in 7 seconds! Awesome! My sincere appreciation!!!

    Reply
  6. Sharath
    Sharath says:

    I am a bit confused from the sample you gave above (I am not a db person, and maybe this is a silly question 🙂 ):
    “Further invocations may be for large set of orders, but the first cached plan with index seek will be used resulting in poor performance (instead of using a scan).”

    Question: Is scan better in case of large result sets? I my understanding is that seek always overrules scan.

    Reply
    • Plamen Ratchev
      Plamen Ratchev says:

      Hi Sharath,

      It is not true that seek is always more efficient than scan. A seek operation may be very efficient for retrieving a small result set based on a non-clustered index. However, if you have parameters that query data on a large range of values where a big percentage of the values match the predicate, then a scan will be more efficient. Similar when the data table is very small, a scan can be more efficient than traversing the index using a seek.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *