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?

  • 2
    This has nothing to do with the problem, but of all types, why money? – Aaron Bertrand Mar 30 '15 at 23:39
  • To be honest, purely because I'd copied and pasted that code from somewhere online when looking at example cached plans queries and didn't bother changing the datatype used there – Paul McLoughlin Mar 31 '15 at 0:59
  • 1
    Is this a NUMA machine? How many NUMA nodes, and how many cores per node? – Aaron Bertrand Mar 31 '15 at 1:08
  • @PaulMcLoughlin : Aaron asked about NUMA machine do you have it ? This behavior might come into picture because of NUMA. Can you please confirm – Shanky Mar 31 '15 at 4:23
  • 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 – Paul McLoughlin Mar 31 '15 at 15:23
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

| improve this answer | |
  • 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 '15 at 14:42
  • Wanting to bump this one as I am experiencing something similar: clerks is showing >5GB while cached plans is 100MB - the OP's discrepancy is even larger - DenisT: I think you've got why they wouldn't line up exactly, but it doesn't explain the huge, order-of-magnitude scale discrepancies we are seeing... – Mike DeFehr Sep 11 '15 at 13:31
  • 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. – Paul McLoughlin Sep 15 '15 at 23:24
  • @Paul it's like Windows! Can't solve a problem? Reboot, it will solve itself! – Aaron Bertrand Sep 15 '15 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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