The hidden gems among DMVs: sys.dm_os_sys_memory

I have always been a big fan of troubleshooting SQL Server performance issues with the help of DMVs. Not only does this allow me to track down the culprit without the help of any external tools/EXEs but it also gives a sysadmin an unbridled sense of power. Before I become the subject of some scathing comments, the tools/EXEs like PSSDIAG/MDW and other health monitoring tools have their place and are really helpful while performing post mortem analysis but live troubleshooting is something that is really a forte of DMVs. Most monitoring tools like Performance Dashboard make use of these DMVs under the hood. I intend to document a series of not commonly used DMVs and demonstrate how they can be used for troubleshooting/monitoring purposes.

The first one in this series is: sys.dm_os_sys_memory. This DMV was introduced in SQL Server 2008 with an intention of making life easier for all those systems where monitoring memory usage was a mandatory requirement due to the nature of deployment. DBCC MEMORYSTATUS can provide a host of information to the DBA regarding the memory usage of the SQL instance and so can the other memory related DMVs. Then why am I talking about this particular DMV. The usefulness and succinctness of the information provided by this DMV is the prime reason for this blog post. This DMV tracks the memory notifications sent out using the CreateMemoryResourceNotification API. The “system_memory_state_desc” column output of this DMV has three common states:

· Available physical memory is high

· Available physical memory is low

· Physical memory usage is steady

Based on the value of the aforementioned column of this DMV, you can create an SQL Agent Job which tracks the system memory state and sends out notifications as appropriate. If you want to take this a step further, you can even write your own little T-SQL to track the Ring Buffers for the Resource Monitor Health records and send additional information out.

What more can be done?

While troubleshooting SQL Server memory contention issues, we have had multiple issues with

· Available Memory (physical RAM) being low (available_physical_memory_kb)

· OR the System Cache memory usage being high (system_cache_kb)

· OR the working set being trimmed/excessive paging, which is proportional to page file usage ( [total_page_file_kb - available_page_file_kb]/total_page_file_kb)

All the above states can be tracked by setting threshold values for the column value outputs mentioned in the parenthesis which are again exposed by this DMV. No more Perfmon data collection coupled with DBCC MEMORYSTATUS output captures!

This DMV has been added to PSSDIAG (a tool that CSS engineers use to collect data for troubleshooting SQL Performance issues) data collection for SQL Server 2008 instances and higher.

Sample T-SQL Script to convert Resource Monitor Health record into tabular output

Happy monitoring! J

SELECT record_id,

dateadd (ms, (y.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],Notification

FROM (

SELECT

record.value('(./Record/@id)[1]', 'int') AS record_id,

record.value('(./Record/ResourceMonitor/Notification)[1]', 'varchar(50)') AS Notification,timestamp

FROM (

SELECT TIMESTAMP, CONVERT(XML, record) AS record

FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR') AS x) AS y

cross join sys.dm_os_sys_info tme

ORDER BY record_id DESC

In case, you are interested in fetching the complete list of columns from a Ring Buffer Resource Monitor entry, then use the script below:

SELECT

dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],

cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],

cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],

cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],

cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],

cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],

cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],

cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],

cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],

cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],

cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],

cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],

cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],

cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],

cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],

cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],

cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],

cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],

cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type]

FROM sys.dm_os_ring_buffers rbf

cross join sys.dm_os_sys_info tme

where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
ORDER BY rbf.timestamp ASC

Regards,
Amit Banerjee
SEE, Microsoft SQL support
Sudarshan Narasimhan
TL , Microsoft SQL support