Viewing RING_BUFFER_OOM Errors

A client asked a co-worker to take a look at a query for reviewing  RING_BUFFER_OOM messages in sys.dm_os_ring_buffers.  He remembered that I’ve recently had a thing for XQuery and asked me to take a look at it.

To play around with the output of this query, generate some RING_BUFFER_OOM messages in the sys.dm_os_ring_buffer.  This can be accomplished with the script in this post.

Here’s is the script that I would use to review this information:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

WITH cRingBufferOOM
AS (
SELECT CAST (record as xml) record_xml FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_OOM'
)
SELECT
rx.value('(@id)[1]', 'bigint') AS RecordID
,DATEADD (ms, -1 * osi.ms_ticks - rx.value('(@time)[1]', 'bigint'), GETDATE()) AS DateOccurred
,rx.value('(OOM/Action)[1]', 'varchar(30)') AS MemoryAction
,rx.value('(OOM/Pool)[1]', 'int') AS MemoryPool
,rx.value('(MemoryNode/SharedMemory)[1]', 'bigint')/1024 AS SharedMemoryMB
,rx.value('(MemoryNode/AWEMemory)[1]', 'bigint')/1024 AS AWEMemoryMB
,rx.value('(MemoryNode/SinglePagesMemory)[1]', 'bigint')/1024 AS SinglePagesMemoryMB
,rx.value('(MemoryNode/MultiplePagesMemory)[1]', 'bigint')/1024 AS MultiplePagesMemoryMB
,rx.value('(MemoryNode/@id)[1]', 'bigint') AS NodeID
,rx.value('(MemoryNode/ReservedMemory)[1]', 'bigint')/1024 AS SQL_ReservedMemoryMB
,rx.value('(MemoryNode/CommittedMemory)[1]', 'bigint')/1024 AS SQL_CommittedMemoryMB
,rx.value('(MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilization
,rx.value('(MemoryRecord/TotalPhysicalMemory)[1]', 'bigint')/1024 AS TotalPhysicalMemoryMB
,rx.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint')/1024 AS AvailablePhysicalMemoryMB
,rx.value('(MemoryRecord/TotalPageFile)[1]', 'bigint')/1024 AS TotalPageFileMB
,rx.value('(MemoryRecord/AvailablePageFile)[1]', 'bigint')/1024 AS AvailablePageFileMB
,rx.value('(MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint')/1024 AS TotalVASMB
,rx.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]', 'bigint')/1024 AS AvailableExtendedVASMB
FROM cRingBufferOOM rbo
CROSS APPLY rbo.record_xml.nodes('Record') record(rx)
CROSS JOIN sys.dm_os_sys_info osi
ORDER BY rx.value('(@id)[1]', 'bigint')

The output looks like this: