Viewing RING_BUFFER_OOM Errors

Posted by & filed under , , , , , .

Onion Ring Buffer?

Onion Ring Buffer?

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:

image

  • Amanda Bates

    I'm struggling mightily with this view. I suspect I have a memory issue on my sql server (to go along with the cpu and i/o issues) but I can't seem to figure out how to interpret things. It seems like the results to the following query would indicate some problems:
    ring_buffer_typeEvents
    RING_BUFFER_EXCEPTION256
    RING_BUFFER_MEMORY_BROKER9
    RING_BUFFER_RESOURCE_MONITOR128
    RING_BUFFER_SCHEDULER12717
    RING_BUFFER_SCHEDULER_MONITOR256
    RING_BUFFER_SECURITY_ERROR128

    But I'm not sure?
    (i've googled the heck out of this, but haven't found a lot of information regarding interpretation)
    Amanda

    • Jason Strate

      Sorry on the delay, Amanda. If you are still having an issue with this, give me a ring. Dealing with the ring buffer items can be quite a pain.

  • Pingback: SQLskills Training, Day 5: Beyond Training | Erin Stellato | Erin Stellato()