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.
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
