Summary

This chapter covered plan caching and focused on what you need to know to efficiently reuse query plans. Query optimization is a relatively expensive operation, so if a query can be optimized once and the created plan can be reused many times, it can greatly improve the performance of your applications. We covered the batch compilation and recompilation process in detail and showed you how to identify problems with excessive compilations and recompilations.

Plan reuse is based on query parameterization, so this topic was also covered in detail. We looked at cases where SQL Server decides to automatically parameterize your queries, as well as cases when it has to be explicitly defined by either using the forced parameterization configuration option or objects such as stored procedures, user-defined scalar functions, and multistatement table-valued functions.

Although looking at the parameters of a query helps the query optimizer produce better execution plans, occasionally...