sys.dm_os_memory_objects – Day 21 – One DMV a Day

Hello Geeks and welcome to the Day 21 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday as part of troubleshooting memory problems I have covered step 2. Today I will be explaining sys.dm_os_memory_objects. As explained yesterday, memory objects are used by SQL to allocate memory to different components.

Memory Objects provide finer granular allocations than Memory Clerks. Memory Objects use page allocator interface of Memory Clerks to allocate pages. Different types of memory objects are created based on the allocation patterns needed. Typically a page size is 8 KB. But the memory objects can be incremental allocations which range from 512 B – 8 KB. 8 KB page size is not a limit. It is the memory clerk granularity of page allocations. A larger page can be requested from a memory object.

Sys.dm_os_memory_objects can tell you which type of object is taking up more memory and check if this is abnormal. That is a simple query which looks like this.

SELECT type, SUM(pages_in_bytes)
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 2 DESC

Sys.dm_os_memory_objects are really helpful when you are debugging out of memory exception scenarios. Everyone is fighting for memory and SQL Server is not able to provide memory for all. You would generally see a 701 error logged in the error log similar to the below.

Error: 701, Severity: 17, State: 193.
There is insufficient system memory to run this query.

sys.dm_os_memory_objects

Before this error there would be also message like below and the output of the whole DBCC MEMORYSTATUS dumped into your error log. You can query sys.dm_os_memory_clerks first to see which clerk has high usage and get the corresponding objects having more allocations.

Eg: Open cursors can lead to Out of Memory errors.

Failed allocate pages: FAIL_PAGE_ALLOCATION 1

You can enable the below trace flags which will enable o get filtered memory dumps. But remember that these are UNDOCUMENTED and should be used at your own risk.

2551 – Gets filtered dump
8004 – Gets dump on first occurrence of OOM

So today we have learned when to use sys.dm_os_memory_objects. Tomorrow I will be covering the next step in troubleshooting memory issues using DMVs. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *