Plan cache size and reserved memory

by GordonLiddy   Last Updated June 11, 2016 08:02 AM

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 24KB
    • sys.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)

Related Questions

Execution plan for COMPUTE BY

Updated July 09, 2015 17:02 PM