Ok

I've verified that the performance counter includes memory for plans allocated through both MPA and SPA by using the below

SELECT [type]

, SUM(single_pages_kb)/1024 AS [SPA Mem, Kb]

, SUM(multi_pages_kb)/1024 AS [MPA Mem, Kb]

, SUM(single_pages_kb)/1024+ SUM(multi_pages_kb)/1024 Total

FROM sys.dm_os_memory_clerks

where type in ('CACHESTORE_SQLCP','CACHESTORE_OBJCP','CACHESTORE_PHDR')

GROUP BY [type] with rollup

ORDER BY SUM(single_pages_kb)

select object_name,counter_name,cntr_value*8/1024 MBs from sys.dm_os_performance_counters

where object_name like '%Plan Cache%' and counter_name like '%Cache Pages%' and instance_name like '%_Total%'

That's all good and well.

However, I am assuming that the DMV sys.dm_exec_cached_plans reports on all plans, irrespective of whether they were allocated using SPA or MPA

The problem is, why do the below queries return 2 different results?

SELECT

sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

FROM sys.dm_exec_cached_plans

SELECT

SUM(single_pages_kb)/1024+ SUM(multi_pages_kb)/1024 [Total MBs]

FROM sys.dm_os_memory_clerks

where type in ('CACHESTORE_SQLCP','CACHESTORE_OBJCP','CACHESTORE_PHDR')

Any help with this guys?

Thanks