Database Reference
In-Depth Information
Chapter 26
Plan Caching
Query Optimization
is resource-intensive process that could introduce significant CPU load on busy servers.
SQL Server tries to minimize such load by caching plans in a special part of process memory called
plan cache
.
This chapter talks about
plan caching
in detail, and it consists of two parts: the first part provides a high-level
overview of plan caching, and it discusses several issues associated with it. The second part dives deeper into plan
cache internals, and it discusses the various data management views that you can use for plan cache monitoring.
Plan Caching Overview
SQL Server prevents unnecessary recompilations of queries by caching plans in a special area of the memory called
plan cache
. In addition to prepared parameterized queries and ad-hoc queries and batches, it caches plans of the
various objects, such as stored procedures, triggers, user-defined functions, and a few others.
SQL Server does not cache actual
execution plans,
but rather it caches a set of other plan-related entities, mainly
compiled plans
. Every time when a query needs to be executed, SQL Server generates an actual execution plan from
the compiled plan, which is an inexpensive operation as compared to compiled plan creation. Execution plans are
run-time structures, and they are unique for each query execution; that is, if multiple sessions need to execute the
same compiled plan, multiple execution plans would be generated at one per session.
■
SQL Server documentation and other resources often ignore the difference between compiled and execution
plans. They often refer to plan cache as the memory area that caches execution plans. This is completely normal, and you
should not be confused by this description.
Note
A compiled plan is generated for the entire batch, and it includes plans for individual statements from the batch.
In this chapter, I typically reference query- or statement-level plans; however, plans for multi-statement batches
behave in the same way.
In addition to compiled plans, SQL Server caches other structures, such as
compiled plan stubs
,
shell queries
, and
a couple others. We will talk about all of them in detail later in the chapter.
The number of cached plans does not directly affect the performance of SQL Server. However, plan cache uses
memory and, therefore, it can reduce the size of the buffer pool, which, in turn, can increase the number of physical
reads and decrease system performance.
SQL Server uses different algorithms to determine which plans should be removed from the cache in case of
memory pressure. For ad-hoc queries, this selection is based strictly on how often a plan is reused. For other types of
plans, the cost of plan generation is also factored into the decision.
