Sql-server – Help understanding discrepency between sys.dm_exec_cached_plans and dm_os_memory_clerks

dmvsql serversql server 2014

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?

Best Answer

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

Related Question