Parameter sniffing

In this section, we will cover the cases in which reusing a plan can create performance problems. As you saw in Chapter 6, Understanding Statistics, SQL Server can use the histogram of statistics objects to estimate the cardinality of a query and then use this information to try and produce an optimal execution plan. The query optimizer accomplishes this by inspecting the values of the query parameters. This behavior is called parameter sniffing, and it is a very good thing: getting an execution plan tailored to the current parameters of a query improves the performance of your applications. This chapter has explained that the plan cache can store these execution plans so that they can be reused the next time the same query needs to be executed. This saves optimization time and CPU resources because the query does not need to be optimized again.

However, although the query optimizer and the plan cache work well together most of the time, some performance problems...