Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Viewing RING_BUFFER_OOM Errors

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

Related posts:

  1. Generating RING_BUFFER_OOM Errors
  2. SSWUG Fall 2010 Virtual Conference Starts Today!
  3. SSWUG Fall 2010 Virtual Conference This Week

Comments

Posted by Anonymous on 15 December 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Viewing RING_BUFFER_OOM Errors - StrateSQL         [sqlservercentral.com]        on Topsy.com

Posted by Anonymous on 18 December 2010

Pingback from  Internet Marketing Guru-Internet Marketing Guru – Outsource Some of the Work For Maximum Efficiency | Internet Marketing Strategies

Leave a Comment

Please register or log in to leave a comment.