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”.
SQL Server execution environment “sniffs” parameter values from the first invocation. Parameter values are sniffed for the following types of batches:
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.
Optimal plan generated for the set of parameters on the first execution, does not have to be optimal for other sets of parameters.
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.
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:
There can be a few options/directions that we can go:
a) Using OPTIMIZE FOR UNKNOWN query hint:
b) Wrapping parameters in local variables:
c) Using Trace Flag 4136:
When to use disabling SP?:
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:
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: