Chapter 5: Plan Caching and Recompilation
—Kalen Delaney
In this chapter: The Plan CacheCaching MechanismsPlan Cache InternalsObjects in Plan Cache: The Big PictureMultiple Plans in CacheWhen to Use Stored Procedures and Other Caching MechanismsTroubleshooting Plan Cache IssuesSummary
We’ve now looked at SQL Server’s query optimization process and the details of query execution. Because query optimization can be a complex and time-consuming process, SQL Server frequently and beneficially reuses plans that have already been generated and saved in the plan cache, rather than producing a completely new plan. However, in some cases a previously created plan may not be ideal for the current query execution, and we might achieve better performance by creating a new plan.
In this chapter, we’ll look at the SQL Server 2005 plan cache and how it is organized. We’ll tell you about what kinds of plans are saved, and under what conditions SQL Server might decide to reuse them. We’ll look at what might cause an existing plan to be re-created. We’ll also look at the metadata that describes the contents of plan cache. Finally, we’ll describe the ways that you can encourage SQL Server to use an existing plan when it might otherwise create a new one, and how you can force SQL Server to create a new plan when you need to know that the most up-to-date plan is available.
© Microsoft. All Rights Reserved.