When running a query including the Actual Execution Plan, the root operator (SELECT
) tells me that the Cached Plan Size is 32KB.
A query that joins sys.dm_exec_cached_plans
and sys.dm_os_memory_objects
, looking at the plan in question, says that values for pages_in_bytes
and max_pages_in_bytes
are 32768 (32KB), which matches the cached plan size.
What I don't understand is what the value in sys.dm_exec_cached_plans.size_in_bytes
, which is 49152 (48KB) stands for. I've read BOL on all these columns, and especially size_in_bytes
which says:
"Number of bytes consumed by the cache object."
I can't get that last bit of the puzzle in place, to understand what it really means.
I know that all operators (not talking about the additional memory grant used for sorts and hashes) requires some amount of fixed memory, to store state, make calculations etc., which is stored with the optimized plan in the cache, but where?
So, my questions are:
- What does
size_in_bytes
really mean - Why is it a higher value than "Cached plan size"?
- Where is the fixed amount of memory for all operators/iterators reserved, is it with the "Cached plan size" (32Kb in my example), or anywhere else?
I know they're different DMVs with different functions, but they are related. The compiled (cached) plans in sys.dm_exec_cached_plans
joins sys.dm_os_memory_objects
on memory_object_address
column. The reason I post the questions here, is that I'm asking for help on this, understanding how to interpret the DMVs and their columns.
If size_in_bytes
is the cached plan size, why does SQL Server say another value in actual execution plan?
New query, new numbers:
- Actual plan
- Cached Plan Size 16KB
- CompileMemory 96KB
- DMVs:
sys.dm_exec_cached_plans.size_in_bytes
24KBsys.dm_os_memory_objects.pages_in_bytes, .max_pages_in_bytes
16KB.
Also, note that this query doesn't require any additional memory grants for sorts and hash operations.
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
Best Answer
The reason that the
size_in_bytes
field of thesys.dm_exec_cached_plans
DMV, at least in terms of "Compiled Plans", is larger than theCachedPlanSize
attribute of theQueryPlan
node in the XML plan is because a Compiled Plan is not the same thing as a Query Plan. A Compiled Plan is comprised of multiple Memory Objects, the combined size of which equates to thesize_in_bytes
field. So, the description of "Number of bytes consumed by the cache object" that you found in the documentation is accurate; it's just that it is easy to misinterpret what is meant by "cache object" given the name of the DMV and that the term "plan" has multiple meanings.A Compiled Plan is a container that holds various pieces of information related to the query batch (i.e. not just a single statement), one (or more) of those pieces being the query plan(s). Compiled Plans have a top-level Memory Object of MEMOBJ_COMPILE_ADHOC which is the row in
sys.dm_os_memory_objects
that is linked via thememory_object_address
field in both DMVs. This Memory Object contains the symbol table, parameter collection, links to related objects, accessor cache, TDS metadata cache, and possibly some other items. Compiled Plans are shared amongst Sessions/Users that are executing the same batch with the same Session settings. However, some related objects are not shared between Sessions/Users.Compiled Plans also have one or more dependent objects that can be found by passing the
plan_handle
(insys.dm_exec_cached_plans
) into thesys.dm_exec_cached_plan_dependent_objects
DMF. There are two types of dependent objects: Executable Plan (Memory Object = MEMOBJ_EXECUTE) and Cursor (Memory Object = MEMOBJ_CURSOREXEC). There will be 0 or more Cursor objects, one per each cursor. There will also be one or more Executable Plan objects, one per each User executing that same batch, hence Executable Plans are not shared between Users. Executable Plans contain run-time parameter and local variable info, run-time state such as the currently executing statement, object ids for objects created at run-time (I assume this refers to Table Variables, Temporary Tables, Temporary Stored Procedures, etc), and possibly other items.Each statement within a multi-statement batch is contained within a Compiled Statement (Memory Object = MEMOBJ_STATEMENT). The size of each Compiled Statement (i.e.
pages_in_bytes
) divided by 1024 should match theCachedPlanSize="xx"
values of the<QueryPlan>
nodes in the XML plan. Compiled Statements will often have one (possibly more?) associated runtime Query Plans (Memory Object = MEMOBJ_XSTMT). Finally, for each runtime Query Plan that is a query, there should be an associated Query Execution Context (Memory Object = MEMOBJ_QUERYEXECCNTXTFORSE).With respect to Compiled Statements, single-statement batches do not have separate Compiled Statement (i.e. MEMOBJ_STATEMENT) or separate runtime Query Plan (i.e. MEMOBJ_XSTMT) objects. The value for each of those objects will be stored in the main Compiled Plan object (i.e. MEMOBJ_COMPILE_ADHOC), and in that case, the
pages_in_bytes
value for that main object divided by 1024 should match theCachedPlanSize
size in the<QueryPlan>
node of the XML plan. Those values will not equate, however, in multi-statement batches.The
size_in_bytes
value can be derived by summing the entries in thesys.dm_os_memory_objects
DMV (the items noted above in bold), all related bydm_os_memory_objects.page_allocator_address
for that Compiled Plan. The trick to getting the correct value is to first get thememory_object_address
fromsys.dm_exec_cached_plans
for a particular Compiled Plan, then use that to get the corresponding MEMOBJ_COMPILE_ADHOC row fromsys.dm_os_memory_objects
based on itsmemory_object_address
field. Then, grab thepage_allocator_address
value fromsys.dm_os_memory_objects
for that row, and use it to grab all rows fromsys.dm_os_memory_objects
that have the samepage_allocator_address
value. (Please note that this technique does not work for the other Cached Object types: Parse Tree, Extended Proc, CLR Compiled Proc, and CLR Compiled Func.)Using the
memory_object_address
value obtained fromsys.dm_exec_cached_plans
, you can see all of the components of the Compiled Plan via the following query:The query below lists all of the Compiled Plans in
sys.dm_exec_cached_plans
along with the Query Plan and statements for each batch. The query directly above is incorporated into the query below via XML as theMemoryObjects
field:Please note that:
TotalPlanBytes
field is just a re-statement of thesys.dm_exec_cached_plans.size_in_bytes
field,AllocatedBytes
field is the SUM of the related memory objects that typically matchesTotalPlanBytes
(i.e.size_in_bytes
)AllocatedBytes
field will occasionally be greater thanTotalPlanBytes
(i.e.size_in_bytes
) due to the memory consumption increasing during execution. This seems to happen mostly due to recompilation (which should be evident with theusecounts
field showing1
)BaseSingleStatementPlanKB
field should match theCachedPlanSize
attribute of theQueryPlan
node in the XML, but only when using a single query batch.MEMOBJ_STATEMENT
insys.dm_os_memory_objects
, one for each query. Thepages_in_bytes
field for these rows should match the individual<QueryPlan>
nodes of the XML plan.Resources: