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?