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:

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

First invocation with Street:

First invocation with ZIP:

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:

b) Wrapping parameters in local variables:

c) Using Trace Flag 4136:

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:

Recompiling execution plan:

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:

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

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