Summary
For all of the caching mechanisms, reusing a cached plan avoids recompilation and optimization. This saves compilation time, but it means that the same plan is used regardless of the particular parameter values passed in. If the optimal plan for a given parameter value is not the same as the cached plan, the optimal execution time will not be achieved. For this reason, SQL Server is very conservative about autoparameterization. When an application uses sp_executesql, prepare and execute, or stored procedures, the application developer is responsible for determining what should be parameterized. You should parameterize only constants whose range of values does not drastically affect the optimization choices.
In this chapter, we looked at the caching and reuse of plans generated by the optimizer. SQL Server can cache and reuse plans not only from stored procedures, but also from adhoc and autoparameterized queries. Because generating query plans can be expensive, it helps to understand how and why query plans are reused and when they must be regenerated. Understanding how caching and reusing plans work will help you determine when using the cached plan can be a good thing, and when you might need to make sure SQL Server comes up with a new plan to give your queries and applications the best performance.
© Microsoft. All Rights Reserved.