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.

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s