SQL Server TSQL – The TSQL Classes Stored Procedures: Parameter Sniffing Resolution

This blog is part of the series The TSQL Classes: Stored Procedure

The link to previous post are given below.

https://www.sqlservergeeks.com/sql-server-tsql-the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-caching/

https://www.sqlservergeeks.com/sql-server-stored-procedures-the-tsql-classes-stored-procedures-a-primer/

https://www.sqlservergeeks.com/sql-server-tsql-the-tsql-classes-stored-procedures-parameter-sniffing-resolution/g

In last class, I talked about parameter sniffing; a plan cache side effect. In this class I’ll talk about ways to resolve parameter sniffing.

The methods to resolve parameter sniffing issues are listed below.

  1. Using local variables with in procedures.
  2. Using with recompile option when creating a procedure.
  3. Using optimize for query hint.

Let’s take them one by one.

1. Using local variables with in procedures.

DROP TABLE fruit
GO
CREATE TABLE fruit (Sno int identity,frtname varchar(20),frttype int)
GO
-- Insert dummy data
INSERT INTO fruit Values('Apple',rand()*1000)
GO 10000
INSERT INTO fruit Values('Mango',rand()*1000)
GO 10
CREATE unique clustered index ix_sno on fruit(sno)
GO
CREATE INDEX ix_frtname on fruit(frtname) include(frttype)
GO

The above script creates and fills a table fruit with values.

CREATE Proc usp_fetchfruits
@frtname varchar(20)
AS
BEGIN
    SELECT * FROM fruit WHERE frtname=@frtname
END
GO
-- procedure with local variables
CREATE Proc usp_fetchfruits_localvar
@frtname varchar(20)
AS
BEGIN
    DECLARE @fruitname varchar(20)
    SET @fruitname=@frtname
    SELECT * FROM fruit WHERE frtname=@fruitname
END
GO

The above query creates two procedures usp_fetchfruits and usp_fetchfruits_localvar. The procedure usp_fetchfruits_localvar uses local variable in where clause. Let’s now compare the execution plan of the above procedures.

1_The_TSQL_Classes_Stored_Procedures_Parameter_Sniffing_Resolution

The above snapshot compares the execution plan of the two procedures. The difference in optimizers estimated and actual number of rows in the 2nd plan is because the optimizer doesn’t knows the actual parameter values passed to the where clause, thus the optimizer creates a generic plan based on available statistics. The table fruit has a total of 10010 rows and the optimizers estimated number of rows is 5005 which is exactly the half of the total count.

This method does resolves performance issues but it might not be that effective if actual number of rows are way greater than the average estimated rows.

2. Using with recompile option when creating a procedure.

The parameter sniffing problem occurs because the cached plan from the first execution is used in future procedure executions. If the procedure is being compiled and plan is prepared again for each and every execution than parameter sniffing will be resolved. This is what With Recompile does when used in procedure definition. It recompiles the procedure to create a new plan every time procedure is executed.

CREATE Proc usp_fetchfruits_withrecompile
@frtname varchar(20)
WITH RECOMPILE
AS
BEGIN
    SELECT * FROM fruit WHERE frtname=@frtname
END

2_The_TSQL_Classes_Stored_Procedures_Parameter_Sniffing_Resolution

   

In above snapshot, in both the execution plans the optimizer’s estimated and actual numbers of records are equal because the plan has been recompiled for each of the parameter values instead of cached plan reuse for the second execution.

The problem with this method is that resources are being used to recompile procedure every time. The plan is even recompiled for parameters values which might have benefited from the cached plan.

3. OPTIMIZE FOR query hint.

Using OPTIMIZE FOR query hint we can have SQL Server optimize a query based on a specific parameter value.

CREATE Proc usp_fetchfruits_optimizefor
@frtname varchar(20)
AS
BEGIN
    SELECT * FROM fruit WHERE frtname=@frtname
    OPTION (OPTIMIZE FOR (@frtname = 'Mango'))
END

In the above procedure, no matter what parameter value is supplied the select query will be optimized for the value “Mango”.

3_The_TSQL_Classes_Stored_Procedures_Parameter_Sniffing_Resolution

The above snapshot compares the execution plan for parameter values “Apple” and “Mango”. In both the plans the estimated values remain 10000 which is the total count for parameter “Apple”.

The SQL Server 2008 extends the OPTIMIZE FOR query hint so as to optimize a query for an unknown value.  This is somewhat similar to the method using local variable.

CREATE Proc usp_fetchfruits_optimizeforunknown
@frtname varchar(20)
AS
BEGIN
    SELECT * FROM fruit WHERE frtname=@frtname
    OPTION (OPTIMIZE FOR UNKNOWN)
END

The above procedure uses OPTIMIZE FOR UNKNOWN query hint  to get rid of parameter sniffing issue.

4_The_TSQL_Classes_Stored_Procedures_Parameter_Sniffing_Resolution

The above snapshot compares the execution for parameter values Apple and Mango. The optimizer estimates an average number of rows returned as it did when using local variables.

Another way is to create multiple stored procedures and call them up based on the parameter values. For example, In this case we can create 2 different procedures one for parameter value Mango and one for parameter value Apple and call them up based on the parameter value supplied.

Summary

In this class, I talked about ways to improve performance caused because for parameter sniffing issue.

 
Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

   

2 Comments on “SQL Server TSQL – The TSQL Classes Stored Procedures: Parameter Sniffing Resolution”

  1. Hi Ahmad,

    I enjoyed reading and running these examples. However you could have gone one step further and turned off the execution plan and turned statistics io on and examined the actual execution times to further verify what the execution plan is showing and to see if the parameter sniffing was actually causing a problem. In your example I found that the actual execution times were not widely varying between the two contrary to the percentage differences shown in the plan.

    When tuning queries I tend to run both statements in sequence and note the execution times. Often the second query appears to be faster than the first giving the impression that the second is actually faster. If you swap the statements around you find that the second query (that was previously slower than the second) now executes faster than the first. So my point is that it’s useful to go one step further before coming to conclusions. I’ve seen many situations where the execution plans display query execution percentages that do not in reality translate to reductions in execution times.

    Thanks for taking the time to write about this topic.

    Tony S.

  2. Thanks Tony for posting your observation. I will make neccessary changes in the blog as per your suggestions.

Leave a Reply

Your email address will not be published.