Memory Clerks Discrepancy

Question

Post reply

Memory Clerks Discrepancy

SQLSACT

SSC-Insane

Points: 21838
More actions
February 27, 2013 at 3:53 am

#274675

Hi all
Environment: SQL 2008 Ent SP3
I am noticing a discrepancy in my Memory Clerks for SQL Plans (Adhoc plans)
Running this query produces 1101MB for SQL Plans
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;
Running this query produces 150MB
SELECT
sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
FROM sys.dm_exec_cached_plans
where objtype = 'Adhoc'
Is this a bug? Am I missing something here?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

The 'SQL Plans' cache includes plans for both 'Ad Hoc' and 'Prepared' queries.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

opc.three (2/28/2013)
The 'SQL Plans' cache includes plans for both 'Ad Hoc' and 'Prepared' queries.

Thanks

Taking into account the fact that SQL plans consists of Adhoc and Prepared queries - The total MB's reported in sys.dm_exec_cached_plans is still very far off from the value in MB's reported in memory clerks.

Any Ideas?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

The queries are a little off in my environments too, with clerks being about 10-15% higher. All I can think of is that clerks are operating a lower level than plans so may be accounting for some additional memory overhead not shown in the plans query. Just a guess though.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

opc.three (3/1/2013)
The queries are a little off in my environments too, with clerks being about 10-15% higher. All I can think of is that clerks are operating a lower level than plans so may be accounting for some additional memory overhead not shown in the plans query. Just a guess though.

Thanks for your input

I definately think something is up here - I'm seeing differences of 1GB+

Definately stumped on this one

Thanks

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Is your system running NUMA of any flavor?

Does this yield multiple rows?

SELECT [type],

[name],

memory_node_id,

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

FROM sys.dm_os_memory_clerks

WHERE type = 'CACHESTORE_SQLCP'

GROUP BY [type],

[name],

memory_node_id;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

opc.three (3/1/2013)
Is your system running NUMA of any flavor?
Does this yield multiple rows?
SELECT [type],
[name],
memory_node_id,
SUM(single_pages_kb) / 1024 AS [SPA Mem, Mb]
FROM sys.dm_os_memory_clerks
WHERE type = 'CACHESTORE_SQLCP'
GROUP BY [type],
[name],
memory_node_id;

Nope - 1 row