none
Ring buffers - are the messages the same between types?

    Question

  • Im looking to capture low and out-of-memory conditions by querying sys.dm_os_ring_buffers

    reference this blog post
    http://blogs.msdn.com/b/mvpawardprogram/archive/2012/06/04/using-sys-dm-os-ring-buffers-to-diagnose-memory-issues-in-sql-server.aspx

    The query just looking at RING_BUFFER_RESOURCE_MONITOR, but I want to change the where filter.

    Can I filter on both buffer types, as long as the messages are the same format/schema?
    like this?

    WHERE dorb.ring_buffer_type IN('RING_BUFFER_RESOURCE_MONITOR','RING_BUFFER_OOM')

    WITH RingBuffer

    AS (SELECT CAST(dorb.record AS XML) AS xRecord,

    dorb.timestamp

    FROM sys.dm_os_ring_buffers AS dorb

    WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

    )

    SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,

    xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,

    xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem,

    DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime

    FROM RingBuffer AS rb

    CROSS APPLY rb.xRecord.nodes('Record') record (xr)

    CROSS JOIN sys.dm_os_sys_info AS dosi

    ORDER BY RmDateTime DESC;

    - - - - - - - - -


    Wednesday, July 24, 2013 12:36 PM

Answers

  • Hi ,

    I tried running this code and it ran successfully but what result it will produce depends on your scenario.whether you actually have that condition or not

    WITH RingBuffer
    AS (SELECT CAST(dorb.record AS XML) AS xRecord,
    dorb.timestamp
    FROM sys.dm_os_ring_buffers AS dorb
    WHERE dorb.ring_buffer_type in( 'RING_BUFFER_RESOURCE_MONITOR','RING_BUFFER_OOM')
    )
    SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,
    xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,
    xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem,
    DATEADD(ms, -1 * dosi.ms_ticks - rb.timestamp, GETDATE()) AS RmDateTime,
    xr.value('(MemoryNode/TargetMemory)[1]','bigint') AS TargetMemory,
    xr.value('(MemoryNode/ReserveMemory)[1]','bigint') AS ReserveMemory,
    xr.value('(MemoryNode/CommittedMemory)[1]','bigint') AS CommitedMemory,
    xr.value('(MemoryNode/SharedMemory)[1]','bigint') AS SharedMemory,
    xr.value('(MemoryNode/PagesMemory)[1]','bigint') AS PagesMemory,
    xr.value('(MemoryRecord/MemoryUtilization)[1]','bigint') AS MemoryUtilization,
    xr.value('(MemoryRecord/TotalPhysicalMemory)[1]','bigint') AS TotalPhysicalMemory,
    xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]','bigint') AS AvailablePhysicalMemory,
    xr.value('(MemoryRecord/TotalPageFile)[1]','bigint') AS TotalPageFile,
    xr.value('(MemoryRecord/AvailablePageFile)[1]','bigint') AS AvailablePageFile,
    xr.value('(MemoryRecord/TotalVirtualAddressSpace)[1]','bigint') AS TotalVirtualAddressSpace,
    xr.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]','bigint') AS AvailableVirtualAddressSpace,
    xr.value('(MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]','bigint') AS AvailableExtendedVirtualAddressSpace
    FROM RingBuffer AS rb
    CROSS APPLY rb.xRecord.nodes('Record') record (xr)
    CROSS JOIN sys.dm_os_sys_info AS dosi
    ORDER BY RmDateTime DESC;


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, July 24, 2013 2:43 PM