Today Am going to explain something about the ring buffer which is an internal structure in SQL server that captures various information like security errors, any kind of exceptions, scheduler information, resource monitor information etc.

How did I reach to RING BUFFER all on a sudden is nothing but when I looked at the extended events I have come across an extended event -"system health" session where I could find something interesting in my one of the production servers. That lead me to take a break from the wait type series blog, albeit its interesting to know.

When I was checking the XEvents in my production system, I could see a session entry as system_health with the target_name "ring_buffer". This is a sesion thats always on the server.(As long as the target is ring_buffer, when the buffer fills up the old entries will be cleared/overwritten.)

--Use the below query 
Select name,target_data,CAST(target_data as XML),* From sys.dm_xe_sessions s
Inner Join sys.dm_xe_session_targets t On s.address =t.event_session_address

When I looked at the target data in an XML format, it gives me a lot of data points to look at system_health session.This session would give us information about the exceptions encountered for

an error with severity >=20,
memory errors (such as error type 17803,701),
non-yielding issues,
deadlock related issues,
waiting on latches more than >15 seconds,
waiting on locks for more than 30 seconds and waiting for external or pre-emptive waits.
Later I found a good article by Jonathan Kehayias for system_health session(a million dollar worth reading)

<http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/13/an-xevent-a-day-13-of-31-the-system-health-session.aspx>.

RING_BUFFER_RESOURCE_MONITOR - Resource Monitor activity like was physical memory pressure signaled or not.
RING_BUFFER_SCHEDULER_MONITOR  - What is the state of the logical schedulers, the health record type is very helpful.
RING_BUFFER_MEMORY_BROKER  - Actions the internal memory broker is taking to balance the memory between caches.
RING_BUFFER_SECURITY_ERROR  - Errors that occur during security operations.  Ex: login failed may have more details about the OS error code and such
RING_BUFFER_SCHEDULER - Actual scheduler activity such as context switching.  You can reconstruct the execution order from these entries.
RING_BUFFER_EXCEPTION - Any exceptions encountered in the server.  SQL uses throw internally for errors so you can see SQL errors as well.
RING_BUFFER_CONNECTIVITY - Core connectivity information - useful in tracking down connection failure information

Below are few queries that can be used to get more information from system_health session event.

The below query return the event types present in the session.
SELECT e.event_name AS [Event Type] 
FROM sys.dm_xe_sessions s 
JOIN sys.dm_xe_session_events e 
	ON s.address = e.event_session_address 
WHERE s.name = 'system_health' 

The below query retuns the error events occured in the server

SELECT XEventData.XEvent.query('.') 
      FROM (SELECT CAST(xet.target_data AS xml) AS TargetData 
            FROM sys.dm_xe_session_targets xet 
            INNER JOIN sys.dm_xe_sessions xe 
            ON (xe.address = xet.event_session_address) 
            WHERE xe.name = 'system_health' ) AS Data 
      CROSS APPLY 
      TargetData.nodes ('//RingBufferTarget/event') 
                              AS XEventData (XEvent) 

To see the complete information of all the event present in the System_health use the following code:(I do not really know to whome I should give the credit for the below code, however it credits the person who wrote it.)

declare @summary table
		( [ring_buffer_type] NVARCHAR(128)
		, [type_count] INT )
		INSERT @summary ([ring_buffer_type]
		, [type_count])
	SELECT [ring_buffer_type]
		, COUNT(*) AS [type_count]
	FROM sys.dm_os_ring_buffers r
	CROSS JOIN sys.dm_os_sys_info i
	GROUP BY [ring_buffer_type]

	SELECT * FROM @summary

	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE [ring_buffer_type] = 'RING_BUFFER_CLRAPPDOMAIN')
		SELECT COUNT(*) AS [count]
			, 'RING_BUFFER_CLRAPPDOMAIN' AS [Type]
			, Action
		FROM (	SELECT RingBuffer.[Record].value('Action[1]', 'varchar(100)') AS [Action] 
				FROM (SELECT CAST([Record] AS [XML]) AS [TargetData] 
					  FROM sys.dm_os_ring_buffers
					  WHERE [ring_buffer_type] = 'RING_BUFFER_CLRAPPDOMAIN') AS Data
				CROSS APPLY [TargetData].nodes('//Record') AS RingBuffer([Record])) derived
		GROUP BY [Action]
		ORDER BY [Action]


	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE [ring_buffer_type] = 'RING_BUFFER_RESOURCE_MONITOR')
	SELECT 'RING_BUFFER_RESOURCE_MONITOR' AS [Type], RingBuffer.[Record].query('.')--RingBuffer.Record.value('Error[1]', 'int') as error
				FROM (SELECT CAST([Record] AS [XML]) AS [TargetData] 
					  FROM sys.dm_os_ring_buffers
					  WHERE [ring_buffer_type] = 'RING_BUFFER_RESOURCE_MONITOR') AS Data
				CROSS APPLY [TargetData].nodes('//Record') AS RingBuffer([Record])

			
				

	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')
		SELECT COUNT(*) AS [count]
			, 'RING_BUFFER_MEMORY_BROKER' AS [Type]
			, [Pool]
			, [Broker]
			, [Notification]
		FROM (	SELECT RingBuffer.Record.value('Pool[1]', 'int') AS [Pool]
					, RingBuffer.Record.value('Broker[1]', 'varchar(100)') AS [Broker]
					, RingBuffer.Record.value('Notification[1]', 'varchar(100)') AS [Notification]
				FROM (SELECT CAST(Record AS XML) AS TargetData 
					  FROM sys.dm_os_ring_buffers
					  WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') AS Data
				CROSS APPLY TargetData.nodes('//Record/MemoryBroker') AS RingBuffer(Record)) derived
		GROUP BY [Pool]
			, [Broker]
			, [Notification]
		ORDER BY [Pool]
			, [Broker]
			, [Notification]

	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR')
		SELECT COUNT(*) AS [count]
			, 'RING_BUFFER_SECURITY_ERROR' AS [Type]
			, [APIName]
			, [CallingAPIName]
			, [ErrorCode]
		FROM (	SELECT RingBuffer.Record.value('APIName[1]', 'varchar(128)') as [APIName]
					, RingBuffer.Record.value('CallingAPIName[1]', 'varchar(128)') as [CallingAPIName]
					, RingBuffer.Record.value('ErrorCode[1]', 'varchar(128)') as [ErrorCode]
				FROM (SELECT CAST(Record AS XML) AS TargetData 
					  FROM sys.dm_os_ring_buffers
					  WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS Data
				CROSS APPLY TargetData.nodes('//Record/Error') AS RingBuffer(Record)) derived
		GROUP BY [APIName], [CallingAPIName], [ErrorCode]
		ORDER BY [APIName], [CallingAPIName], [ErrorCode]

	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE ring_buffer_type = 'RING_BUFFER_XE_BUFFER_STATE')
		SELECT COUNT(*) AS [count]
			, 'RING_BUFFER_XE_BUFFER_STATE' AS [Type]
			, [NewState]
		FROM (	SELECT RingBuffer.Record.value('NewState[1]', 'varchar(100)') as [NewState]
				FROM (SELECT CAST(Record AS XML) AS TargetData 
					  FROM sys.dm_os_ring_buffers
					  WHERE ring_buffer_type = 'RING_BUFFER_XE_BUFFER_STATE') AS Data
				CROSS APPLY TargetData.nodes('//Record/XE_BufferStateRecord ') AS RingBuffer(Record)) derived
		GROUP BY [NewState]
		ORDER BY [NewState]

	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER')
		SELECT COUNT(*) AS [count]
			, 'RING_BUFFER_SCHEDULER' AS [Type]
			, [Action]
		FROM (	SELECT RingBuffer.Record.value('Action[1]', 'varchar(128)') as [Action]
				FROM (SELECT CAST(Record AS XML) AS TargetData 
					  FROM sys.dm_os_ring_buffers
					  WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER') AS Data
				CROSS APPLY TargetData.nodes('//Record/Scheduler') AS RingBuffer(Record)) derived
		GROUP BY [Action]
		ORDER BY [Action]

	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE ring_buffer_type = 'RING_BUFFER_EXCEPTION')
        SELECT derived2.[count]
			, derived2.[Type]
			, derived2.[error]
			, m.text AS [sys.messages text for error] 
		FROM(	SELECT COUNT(*) AS [count]
					, 'RING_BUFFER_EXCEPTION' AS [Type]
					, [error]
				FROM (	SELECT RingBuffer.Record.value('Error[1]', 'int') as error
						FROM (SELECT CAST(Record AS XML) AS TargetData 
							  FROM sys.dm_os_ring_buffers
							  WHERE ring_buffer_type = 'RING_BUFFER_EXCEPTION') AS Data
						CROSS APPLY TargetData.nodes('//Record/Exception') AS RingBuffer(Record)) derived1
				GROUP BY [error])derived2
		LEFT JOIN sys.messages m
		ON derived2.[error] = m.message_id 
		AND m.[language_id] = SERVERPROPERTY('LCID')
		ORDER BY [error] 

	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY')
		SELECT COUNT(*) AS [count]
			, 'RING_BUFFER_CONNECTIVITY' AS [Type]
			, [RecordType]
		FROM (	SELECT RingBuffer.Record.value('RecordType[1]', 'varchar(100)') as [RecordType]
				FROM (SELECT CAST(Record AS XML) AS TargetData 
					  FROM sys.dm_os_ring_buffers
					  WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY') AS Data
				CROSS APPLY TargetData.nodes('//Record/ConnectivityTraceRecord') AS RingBuffer(Record)) derived
		GROUP BY [RecordType]
		ORDER BY [RecordType]

	IF EXISTS (SELECT * 
			   FROM @summary 
			   WHERE ring_buffer_type = 'RING_BUFFER_XE_LOG')
		SELECT COUNT(*) AS [count]
			, 'RING_BUFFER_XE_LOG' AS [Type]
			, [XE_LogRecord]
		FROM (	SELECT RingBuffer.Record.value('(@message)[1]', 'varchar(max)') AS [XE_LogRecord]
				FROM (SELECT CAST(Record AS XML) AS TargetData 
					  FROM sys.dm_os_ring_buffers
					  WHERE ring_buffer_type = 'RING_BUFFER_XE_LOG') AS Data
				CROSS APPLY TargetData.nodes('//Record/XE_LogRecord') AS RingBuffer(Record)) derived
		GROUP BY [XE_LogRecord]
		ORDER BY [XE_LogRecord]

The bottom line is , any SQL server issues coming under the category discussed will report to system_health session of XEvent that can be used extensively used to analyse the issues in the production server if do not have a way for profiling. Important point to be noted here is nothing but the scope of the entries in the ring buffer. As explained, once again, as long as the target is ring_buffer, when the buffer fills up the old entries will be cleared/overwritten.