Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Memory Clerks Discrepancy Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 3:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 1,375, Visits: 2,660
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
Post #1424451
Posted Thursday, February 28, 2013 10:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1425349
Posted Friday, March 1, 2013 6:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 1,375, Visits: 2,660
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?
Post #1425469
Posted Friday, March 1, 2013 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1425491
Posted Friday, March 1, 2013 7:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 1,375, Visits: 2,660
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
Post #1425505
Posted Friday, March 1, 2013 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
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
Post #1425530
Posted Saturday, March 2, 2013 2:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 1,375, Visits: 2,660
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
Post #1425901
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse