Hi Guys
I've seen it posted that when using a Query like below to check Memory Allocations, if there is a high amount for CACHESTORE_SQLCP then that means that there are lots of Adhoc Plans in your plan cache
I tend to disagree with this because the CACHESTORE_SQLCP cache store also stores plans for Prepared Queries which has their plans re-used the same way Stored Procs would.
Unless my understanding is off?
Any thoughts on this?
SELECT TOP(20) [type], [name], SUM(single_pages_kb)/1024 AS [SPA Mb] ,SUM(multi_pages_kb)/1024 AS [MPA Mb]
FROM sys.dm_os_memory_clerks
GROUP BY [type], [name]
ORDER BY SUM(single_pages_kb) DESC;