Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Memory objects allocated by SQL Server

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;

 Read More

SQL Server – Troubleshoot memory pressure with Default Trace and Server

SQL Server – Troubleshooting Internal Memory pressure – stolen pages and buffer count

SQL Server – Memory: Pages sec, memory pressure and thrashing

SQL Server Performance ,memory pressure and memory usage





 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer