Using the default system_health Extended Event

The default extended event system_health appeared in SQL 2008 and provides several key monitors, which can prove useful when investigating issues. In the past I have mentioned this in the capturing deadlocks post as it is one of the monitors automatically in place and it is an easy way to grab the deadlock xml.

However deadlocks are not the only thing captured. An overview of what is inside system_health  can be found here:

  • The sql_text and session_id for any sessions that encounter an error with severity >=20
  • the sql_text and session_id for any sessions that encounter a “memory” type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
  • A record of any “non-yielding” problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
  • Any deadlocks that are detected
  • The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
  • The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
  • The callstack, sql_text, and session_id for any session that have waited for an extended period of time for “external” waits or “pre-emptive waits”.

I going to single out errors (error_reported) and provide a query to parse the data. Note I have commented out the additional where filters, but have left them in to show how this can be done.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
DECLARE @target_data XML
SELECT @target_data = CAST([target_data] AS XML)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'

SELECT
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM (SELECT @target_data) [target_data]([target_data])
CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1
--AND x.y.exist('.//data[@name="severity"]/value/text()[. = "20"]') = 1
--AND x.y.value('(@timestamp)[1]','DATETIME') = '2015-10-21 16:29:00.000'

The above query I have used a variable to store @target_data, this could obviously be removed and the queries combined. When I am writing these xml queries I tend to keep a static copy of target_data so as not too impact the instance more than needed.

DECLARE @target_data XML
SELECT
CAST([target_data] AS XML) [target_data] INTO #target_data
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'
SELECT @target_data = [target_data] FROM #target_data

Finally for completeness, as well as the ring_buffer there is also another target for system_health and that is to file.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
SELECT 
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM
(
	SELECT 
	CAST([event_data] AS XML) as [target_data],*
	FROM sys.fn_xe_file_target_read_file('*system_health*.xel',NULL,NULL,NULL)
) e CROSS APPLY [target_data].nodes('/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1