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_os_memory_objects, looking at the plan in question, says that values for
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:
I know they're different DMVs with different functions, but they are related. The compiled (cached) plans in
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.
size_in_bytes is the cached plan size, why does SQL Server say another value in actual execution plan?
New query, new numbers:
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)