Tag: Ring Buffer

  • Monitoring and Troubleshooting using sys.dm_os_ring_buffers

    sys.dm_os_ring_buffers: You can use the undocumented Ring Buffer DMV to troubleshoot the below issues.

    • Security Exceptions
    • Exception raised at SQL Operating System level
    • Connection Dropped By the Server
    • System Resource Utilization
    • Memory Pressure
    • CLR Integration Scheduler State
    • Extended Events Subsystems State

    Execute the below query to get the distinct ring buffer type.

    select distinct ring_buffer_type from sys.dm_os_ring_buffers
    • RING_BUFFER_RESOURCE_MONITOR
    • RING_BUFFER_SCHEDULER_MONITOR
    • RING_BUFFER_MEMORY_BROKER
    • RING_BUFFER_SECURITY_ERROR
    • RING_BUFFER_XE_BUFFER_STATE
    • RING_BUFFER_SCHEDULER
    • RING_BUFFER_CONNECTIVITY
    • RING_BUFFER_EXCEPTION
    • RING_BUFFER_XE_LOG

    Check below script as example to troubleshoot the Security Issue using ring buffer. You can change the ring buffer type in below script to troubleshoot the different issues.

    -- Check the Ring Buffer in SQL Server 2008

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON

SELECT CONVERT (varchar(30), GETDATE(), 121) as Run_Time,
dateadd (ms, (ST.[RecordTime] - sys.ms_ticks), GETDATE()) as [Notification_Time],
ST.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY ST.[RecordTime] ASC

-- Script to Check the Ring Buffer in SQL Server 2005

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - ST.[RecordTime]), GETDATE()) AS NotificationTime,
ST.* , sys.ms_ticks AS [CurrentTime]
FROM
(SELECT
RBXML.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
RBXML.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
RBXML.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
RBXML.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
RBXML.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
RBXML.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
RBXML.value('(//Record/@time)[1]', 'bigint') AS [RecordTime]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS RB(RBXML)) ST
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY ST.[RecordTime] ASC

    From the output we can see the hexadecimal error code 0x6FD. You have to convert these error code into decimal value, which will be 0x6FD = 1789

    Check the above decimal error codes using the NET HELPMSG command, which will give you more information on the issue.