opc.three (3/12/2013)
Are you sure the queries you're using to check both the cached plans and the clerks are meant to tie out? i.e. that there are not other mitigating internal factors hidden from the view and undocumented that would prevent them from tying? What is your overarching concern?
I'm using the below to check clerks
SELECT TOP(20) [type], [name], SUM(single_pages_kb)/1024 AS [SPA Mem, Mb]
FROM sys.dm_os_memory_clerks
GROUP BY [type], [name]
ORDER BY SUM(single_pages_kb) DESC;
SELECT SUM(multi_pages_kb)/1024 AS [MPA Mem, Mb]
FROM sys.dm_os_memory_clerks
SELECT SUM(single_pages_kb)/1024 AS [SPA Mem, Mb]
FROM sys.dm_os_memory_clerks
What is your overarching concern?[/
No concern really, I'm just trying to understand the process
In a testing environment (with nothing else running on it), I set up an ad-hoc workload by setting up an agent job that executes 15000 ad-hoc queries against my database. (The purpose of this was to monitor the affect on the plan cache).
The size of each plan is 40960 bytes (40kb)
Out of interest I checked sys.dm_os_memory_clerks and I noticed SPA memory increasing and MPA memory stagnant. Because the plans are definately more than 8KB each, I would think that the memory for these plans would be allocated though MPA and not SPA
SQL 2008 Sp2 Ent
Thanks