Parameter Sniffing

Abstract

In the previous chapter, I discussed how to get execution plans into cache and how to get them reused. It’s a laudable goal and one of the many ways to improve the overall performance of the system. One of the best mechanisms for ensuring plan reuse is to parameterize the query, through either stored procedures, prepared statements, or sp_executesql. All these mechanisms create a parameter that is used instead of a hard-coded value when creating the plan. These parameters can be sampled, or sniffed, by the optimizer to use the values contained within when creating the execution plan. When this works well, as it does most of the time, you benefit from more accurate plans. But when it goes wrong and becomes bad parameter sniffing, you can see serious performance issues.