Database Reference
In-Depth Information
Table 15-1.
sys.dm_exec_cached_plans
Column Name
Description
refcounts
This represents the number of other objects in the cache referencing this plan.
usecounts
This is the number of times this object has been used since it was added to the cache.
size_in_bytes
This is the size of the plan stored in the cache.
cacheobjtype
This specifies what type of plan this is; there are several, but of particular interest
are these:
Compiled plan: A completed execution plan
Compiled plan stub: A marker used for ad hoc queries (you can find more details in the
“Ad Hoc Workload” section of this chapter)
Parse tree: A plan stored for accessing a view
Objtype
This is the type of object that generated the plan. Again, there are several, but these are
of particular interest:
Proc
Prepared
Ad hoc
View
Parent_plan_handle
This is the identifier for this plan in memory; it is used to retrieve query text and
execution plans.
Using the DMV
sys.dm_exec_cached_plans
all by itself gets you only a small part of the information. DMOs are best
used in combination with other DMOs and other system views. For example, using the dynamic management function
sys.dm_exec_query_plan(plan_handle)
in combination with
sys.dm_exec_cached_plans
will also bring back the XML
execution plan itself so that you can display it and work with it. If you then bring in
sys.dm_exec_sql_text(plan_handle)
,
you can also retrieve the original query text. This may not seem useful while you're running known queries for the
examples here, but when you go to your production system and begin to pull in execution plans from the cache, it might
be handy to have the original query. To get aggregate performance metrics about the cached plan, you can use
sys.
dm_exec_query_stats
to return that data. Among other pieces of data, the query hash and query plan hash are stored in
this DMF. Finally, to see execution plans for queries that are currently executing, you can use
sys.dm_exec_requests
.
In the following sections, I'll explore how the plan cache works with actual queries of these DMOs.
Execution Plan Reuse
When a query is submitted, SQL Server checks the procedure cache for a matching execution plan. If one is not found,
then SQL Server performs the query compilation and optimization to generate a new execution plan. However, if
the plan exists in the procedure cache, it is reused with the private execution context. This saves the CPU cycles that
otherwise would have been spent on the plan generation.
Queries are submitted to SQL Server with filter criteria to limit the size of the result set. The same queries are
often resubmitted with different values for the filter criteria. For example, consider the following query:
SELECT soh.SalesOrderNumber,
soh.OrderDate,
sod.OrderQty,
sod.LineTotal
