27 March,2013 by Jack Vamvas
Once SQL Server memory pressure is identified – drill down into what objects are SQL Server allocated . From this query , you can get clues , about which objects are using the highest amount of memory .
The DMV Sys.dm_os_memory_objects - Returns current SQL Server allocations of memory objects.
The sys.dm_os_memory_clerks Returns the set of all memory clerks that are currently active in the instance of SQL Server
Joined at the “page_allocator_address” . It’s a unique address for a memory clerk
SELECT SUM (omo.pages_allocated_count * omo.page_size_in_bytes)/1024 as 'KB_Used', omo.type as 'Memory Object Type', omc.type as 'Memory Clerk Type' FROM sys.dm_os_memory_objects omo join sys.dm_os_memory_clerks omc on omo.page_allocator_address=omc.page_allocator_address GROUP BY omo.type, omc.type, omc.type ORDER BY 1 DESC;