Sunday, May 1, 2011

sys.dm_os_memory_clerks

The sys.dm_os_memory_clerks is a very useful DMV for monitoring SQL Server memory performance.  You could use sys.dm_os_memory_clerks to identify exactly where SQL's memory is going.  Basically, which SQL components are using exactly how much memory.  See here:


SELECT 
   type,
   sum(virtual_memory_reserved_kb) [VM Reserved],
   sum(virtual_memory_committed_kb) [VM Committed],
   sum(awe_allocated_kb) [AWE Allocated],
   sum(shared_memory_reserved_kb) [SM Reserved], 
   sum(shared_memory_committed_kb) [SM Committed],
   sum(multi_pages_kb) [MultiPage Allocator],
   sum(single_pages_kb) [SinlgePage Allocator]
FROM 
   sys.dm_os_memory_clerks 
GROUP BY 
   type
ORDER BY 
   8 desc

No comments:

Post a Comment