Lesson #2: Why Parameter Sniffing Degrades Performance?

Lesson 3 of 6 • 0 upvotes • 15:00mins

Sourav Mukherjee

When the query optimizer sniffed the parameters and determined the superlative approach for processing the stored procedure, it thrives to cache the execution plan in the procedure cache. All subsequent executions of the SP re-use the execution plan from the procedure cache regardless if different parameters are passed. The potential problem with this approach is that the parameters were used when the plan was cached may not be required to produce an optimal plan for all execution of the SP, especially those that have a significantly different set of records returned depending on the parameters passed. For instance, if you passed parameters that required many records to be read, the plan might decide a table or index scan would be the most efficient method to process the sp. Then if the same SP was called with a different set of parameters that would only return a specific record, it would use the cached execution plan and perform an table or index scan operation to resolve it’s query, even if an index seek operation would be more efficient in returning the results for the second execution of the sp. When you try to create any stored procedure with parameters, the SQL server sniffs the parameter range, type and generates the cost-effective execution plan accordingly and this can be the difference in performance sometimes.