Controlling Parameter Sniffing in SQL Server

Introduction

Whenever a Stored Procedure or parameterized query is executed, the query optimizer tries to reuse the execution plan. If an execution plan already exists for a Stored Procedure or parameterized query in the cache then the query optimizer will blindly reuse it. If an execution plan is not present in the cache then the query optimizer creates a well-optimized execution plan based on input parameter values. The query optimizer will use the same execution plan for the next call of the Stored Procedure. This is known as parameter sniffing. Parameter sniffing is enabled by default in SQL Server. The cached execution plan may not be well-optimized for the new parameter, hence the second call of the query may be very slow.
 
The advantage of this behavior of the optimizer, is that the optimizer needs not re-create the execution plan to execute the same query multiple times. The optimizer will go and search the execution plan that is compiled earlier from the cache. This looks beneficial when good statistcs are maintained and typical parameters are used.
 
Parameter Sniffing Workaround

Disabling the parameter sniffing is helpful when there are several parameters in a Stored Procedure and there is also no favorite combination of parameters. If the value of parameters is not known at the time of compilation then there is nothing to be sniffed.

Using following, we may avoid the issue with parameter sniffing.
 
In this article I am using the same example from my previous article:

Parameter Sniffing in SQL Server
 
Use dummy variables that are not directly used as parameters

Use of a dummy variable that is not used as a parameter also ensures the stability of the execution plan.

CREATE PROCEDURE TestStoreProcedureNew

(

 @CompanyId INT

)

AS

BEGIN

 DECLARE @DummyCompanyId INT

 SET @DummyCompanyId = @CompanyId

 

 SELECT * FROM EmployeeMaster em

 INNER JOIN EmployeeDetails ed ON em.DetailId = ed.EmployeeDetailId

 WHERE em.CompanyId = @DummyCompanyId 

END
 
Execution Plan

Execute Stored Procedure using "WITH RECOMPILE" option

This solution enforces recompilation of the Stored Procedure on each run, so every time the optimizer creates a new execution plan for the current parameters.

EXEC TestStoreProcedureNew 1002 WITH RECOMPILE

--OR

 

CREATE PROCEDURE TestStoreProcedureWithRecompile

(

 @CompanyId INT

)

WITH RECOMPILE

AS

BEGIN

 SELECT * FROM EmployeeMaster em

 INNER JOIN EmployeeDetails ed ON em.DetailId = ed.EmployeeDetailId

 WHERE em.CompanyId = @CompanyId

END
 
Query hint RECOMPILE

SQL Server offers the new query hint RECOMPILE that forces query recompilation. This is a better way in which only a specific query is recompiled but in the above method entire the Stored Procedure is recompiled on every call. OPTIMIZE FOR in a query hint allows us to specify a constant that is used by the optimizer while optimizing the query.

CREATE PROCEDURE TestStoreProcedureQueryHint  

( 

 @CompanyId INT 

)

AS 

BEGIN

 

 SELECT * FROM EmployeeMaster em 

 INNER JOIN EmployeeDetails ed ON em.DetailId = ed.EmployeeDetailId 

 WHERE em.CompanyId = @CompanyId 

 OPTION (OPTIMIZE FOR UNKNOWN)

END
 
Disable auto-update statistics during the batch

Statistics are database objects that contains the information about the value distribution in one or more columns of a table and views. The optimizer uses this statistical data to estimate the number of rows in the query result and based on the number of rows the optimizer will select the execution plan. In the case when the statistics are very old or invalid or out of date then the optimizer chooses the wrong execution plan, hence query performance is degraded.
 
Using the system Stored Procedure "sp_updatestats" we can update the statics of the database. Frequently, update statistics are a bad option for dealing with parameter sniffing.
 
To disable the "Auto Update Statistics", go to the database property and select the "Option" tab and set the "Auto Update Statistics" property to false.

Database Property Auto Update Statistics

Alternatively we can also use the following script to disable the Auto Update Statistics.

ALTER DATABASE DatabaseName SET AUTO_UPDATE_STATISTICS OFF

Use Decision Tree algorithm in Stored Procedure

A decision tree Stored Procedure decides which Stored Procedure is called and the decision making is based on the parameter value.
 the
In the preceding example, the Stored Procedure "TestStoreProcedureDecisionTree" determines if the parameter value is 1001 then it calls a "TestStoreProcedureCompany1", if the parameter value 1002 is found then it calls a "TestStoreProcedureCompany2" Stored Procedure. Here both sub Stored Procedures have the same procedure body, in other words both are identical.
 

CREATE PROCEDURE TestStoreProcedureDecisionTree

( 

 @CompanyId INT 

)

AS 

BEGIN

     IF(@CompanyId = 1001)

     BEGIN

        EXEC TestStoreProcedureCompany1 @CompanyId

     END

     ELSE IF(@CompanyId = 1002)

     BEGIN

        EXEC TestStoreProcedureCompany2 @CompanyId

     END
END

GO

CREATE PROCEDURE TestStoreProcedureCompany1

( 

 @CompanyId INT 

)

AS 

BEGIN

 

 SELECT * FROM EmployeeMaster em 

 INNER JOIN EmployeeDetails ed ON em.DetailId = ed.EmployeeDetailId 

 WHERE em.CompanyId = @CompanyId 

 

END

GO

CREATE PROCEDURE TestStoreProcedureCompany2

( 

 @CompanyId INT 

)

AS 

BEGIN

 

 SELECT * FROM EmployeeMaster em 

 INNER JOIN EmployeeDetails ed ON em.DetailId = ed.EmployeeDetailId 

 WHERE em.CompanyId = @CompanyId   

END
 
Execution plan

Execution Plan Detail

This solution allows us to reuse the execution plans and this is better than other options or solutions such as recompilation of a query or Stored Procedure in terms of performance.
 
Summary

In short, parameter sniffing is one plan for all parameter combinations.

Common work around of parameter sniffing:

  • Use Optimize for unknown or optimized for @Variable (Query hint)
  • Use Plan guide
  • Recompile procedure option
  • Avoid use of a parameter in a where clause directly instead of using a dummy variable
  • Use the decision tree algorithm within a Stored Procedure for a different value of the parameter

Up Next
    Ebook Download
    View all
    Learn
    View all