7

We've got a strange discrepancy on one of our SQL Server 2014 instances between the amount of memory being used for cached plans as per sys.dm_exec_cached_plans and the amount of memory used as per sys.dm_os_memory_clerks looking at the CACHESTORE_SQLCP type (which I understand is for adhoc query cached plans).

If we query the cached plans as below:

select cp.cacheobjtype, cp.objtype, 
  sum(cast(cp.size_in_bytes as money))/1024/1024 as sizeMB
from sys.dm_exec_cached_plans as cp
group by cp.cacheobjtype, cp.objtype;

then we appear to have about 90 MB being used in total for cached plans, with only 2MB being used for Adhoc plans. There are only 300 plans in the cache too.

However, if we look at the dm_os_memory_clerks view as below:

select mc.type, mc.pages_kb/1024 as pagesMB
from sys.dm_os_memory_clerks as mc
where mc.type = 'CACHESTORE_SQLCP'

then it is reporting that roughly 12 GB is being used. Our instance has approx. 300 GB of RAM in it.

We'd like to understand the discrepancy, and ideally take some steps to ensure that the plan cache is used effectively (i.e. has more than 300 plans in it to improve the cache hit ratio which is currently very poor). Being able to account for this space would be the first step.

Any thoughts on what the discrepancy could be and why this space is not being used for caching plans?

1
  • As far as I can tell we have 2 NUMA nodes, both with online_scheduler_count of 16, with no virtual address space reserved for the second node. This comes from the following query: select n.node_id, n.node_state_desc, n.online_scheduler_count, n.processor_group, n.memory_node_id, CAST((m.virtual_address_space_reserved_kb / 1024.0 / 1024) AS INT) as reservedMemoryGB FROM sys.dm_os_nodes n INNER JOIN sys.dm_os_memory_nodes m ON n.memory_node_id = m.memory_node_id WHERE n.node_state_desc NOT LIKE '%DAC%' ORDER BY n.node_id Mar 31, 2015 at 15:23

1 Answer 1

1

FWIW - and I'm only posting this as a temporary answer because there's no way I could make this a digestible comment - I ran these queries and saw some discrepancies as well:

select cp.cacheobjtype, cp.objtype, 
  sum(cast(cp.size_in_bytes as DECIMAL(19,4)))/1024/1024 as sizeMB
from sys.dm_exec_cached_plans as cp
group by cp.cacheobjtype, cp.objtype
ORDER BY sizeMB DESC;

select mc.type, mc.pages_kb/1024 as pagesMB
from sys.dm_os_memory_clerks as mc
where mc.type LIKE N'CACHESTORE[_]%'
ORDER BY pagesMB DESC;

Most notably, the CACHESTORE_SQLCP total in the second query is less than the four Compiled Plan results in the first query, and there was substantial memory clerk usage under other cachestores as well. This is a lowly VM that doesn't have 300 GB of RAM but still demonstrates that these numbers do not always correlate and add up nicely.

enter image description here

2
  • 1
    Aaron, could it be Compiled Plan vs. Execution Plans? "Executable plans, or execution contexts, are considered to be dependent on compiled plans and do not show up in the sys.dm_exec_cached_plans view. Executable plans are runtime objects created when a compiled plan is executed. Just as for compiled plans, executable plans can be object plans stored in the object store, or SQL plans, stored in the SQL store. Each executable plan exists in the same cache store as the compiled plan to which it is dependent." Plan Cache Internals
    – DenisT
    Mar 31, 2015 at 14:42
  • We never got to the bottom of why we were seeing this discrepancy, but after a BCP test (where we restarted the server) the discrepancy went away - and the other symptoms that we were experiencing like poor cache hit ration went away too. Sep 15, 2015 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.