Parameter Sniffing Problem with Stored Procedures

AVS is looking for candidates
June 6, 2017
Sinergija Challange 2019
November 18, 2019

If you are writing a SQL queries or stored procedures, probably sometimes you noticed that stored procedure execution sometimes is quick and sometimes is slow. The difference in the execution period can also occur if you execute stored procedure from the SQL Server Management Studio and from your application. The reason for this is very often so called “parameter sniffing”.

 

What is Parameter sniffing?

SQL Server execution environment “sniffs” parameter values from the first invocation. Parameter values are sniffed for the following types of batches:

  • Stored procedure
  • Queries submitted via sp_executesql stored procedure

SQL server compiles the stored procedure using the parameter sniffing when the first time stored procedure is executed and put it in the plan cache. All subsequent executions reuse the execution plan from the procedure cache regardless if different parameters are passed.

 

Potential problem:

Optimal plan generated for the set of parameters on the first execution, does not have to be optimal for other sets of parameters.

Example:
When as SP is executed for the first time, parameters that are passed required a large number of records to be read and query optimizer decided a table or index scan is the most efficient method to obtain the records.
The same SP was called again with the different set of parameters that only returns a specified record. SQL server used the cached execution plan and performs a table or index scan, even if an index seek would be more efficient for this set of parameters.
It is not an issue, it is expected behavior.

 

Demo:

Let create following stored procedure:

Parametere sniffing1

All parameters are optional, at least one parameter must be provided.
First invocation with BP number. Second onvocation with Street and third with ZIP.

Parametere sniffing2

First invocation with Street:

Parametere sniffing3

First invocation with ZIP:

Parametere sniffing4

 

How to avoid side effect that is caused by parameter sniffing?:

There can be a few options/directions that we can go:

  1. Disable parameter sniffing
  2. Recompile execution plan every time when invoke stored procedure
  3. Decomposition of SQL statement or decision tree to follow specific combination of parameters
  4. Using dynamic SQL

 

We can disable parameter sniffing by:

a) Using OPTIMIZE FOR UNKNOWN query hint:

Parametere sniffing5

b) Wrapping parameters in local variables:

Parametere sniffing6

c) Using Trace Flag 4136:

Parametere sniffing7

 

When to use disabling SP?:

  1. When average plan would be acceptable
  2. To solve huge elapsed time for some combinations
  3. The price for improvement is paid all parameter combinations

 

In the situation when most important parameter is known, the options is that we can optimize for that specific parameter with OPTION OPTIMIZE FOR:

Parametere sniffing8

Recompiling execution plan:

Parametere sniffing9

Pros:

  • Execution plan for every parameter combination
  • Easy for maintenance

Cons:

  • Compiled by each execution and requires CPU resources for every execution
  • Execution plan is good, but not optimal

 

Decomposition or decision tree:

 

Parametere sniffing10

Pros:

  • Optimal execution plan for every parameter combination
  • Reuse the execution plan and doesn’t need additional CPU resources

Cons:

  • Maintenance problem – requires one sub stored procedure for every execution plan that we want to use
  • Maintenance problem – unmanageable for more the  four execution plans

 

Dynamic SQL

Parametere sniffing11

Pros:

  • Optimal execution plan for every parameter combination
  • Reuse the execution plan, no additional CPU resources
  • Can manage a lot of optional parameters

Cons:

  • Additional permissions required
  • Code maintenance problem – error friendly
  • Prone to SQL injection

 

Conclusion:

There is no ultimate solution. In most cases the solution is combination of applying two sometimes even more approaches. But there are some directions that should be followed when searching for optimal solution for concrete problem:

  • General recommendation is to always try to reuse the execution plan whenever is possible and to try to avoid disabling parameter sniffing.
  • Define goal of the optimization.
  • Check business logic requirements.
  • Check application design.
  • Use query hints if it is possible.
  • Use recompiling very carefully.
  • It is legitimate to combine approaches.

 

Dušan Stanojević

Senior Developer