[System_Health] 2 dynamic and full parsers for the system_health session

I needed to look at some locking info in the system_health session recently, and realized nobody ever wrote one - all the ones I've seen are explicit calls. 
Everybody does stuff like '/event/data[3]', but there was no comprehensive shredding done.  I decided to fix that.  

While I've written something for SP_SERVER_DIAGNOSTICS a while ago (which will handle 2012+), I needed something specifically for 2008/2008R2, . 
 At it's core is a piece of code I picked up a while ago from stack_overflow and a couple other Xquery posts:

              SELECT  
                    A.B.value('@name[1]', 'varchar(128)') AS EventType,
                    A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
                    X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
                    X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
                    X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
             FROM    cte_healthsession
             CROSS APPLY EventXML.nodes('/*') AS A (B)
             CROSS APPLY EventXML.nodes('//*') AS X (N)



All this really does is shred out every node at the various levels, including names and values.  Which for system_health seems to work pretty well (not perfectly; there are some values I need to filter in my pivot)

But once it's gotten all the potential details, the code automatically goes through each group and shreds it.  As a bonus, I realized that by doing it slightly differently, I could return all the details in one table.  The table does have a ton of fields, since many are specific to each type of event - error_reported doesn't have duration or wait_type, for instance, whereas waitinfo doesn't include error or messsage.  However, it means that you can read them in time order quite easily, which makes it easier to correlate issues.



However, I also wound up with a version that returns multiple tables, one for each event type.  (This looks similar to the sp_server_diag parser I wrote)





 


Now, when you run this (if you're not already doing a ton of stuff with system_health) you may be wondering why you're not seeing anything recent.  There are two potential reasons:

1) events that are too large, most likely deadlocks.  There's no good way around it other than to modify the system_health session to also write to a file (which 2012 does do).  In order to make sure I'm not susceptible to it, I added deadlock monitoring via Event Notifications (add the event DEADLOCK_GRAPH).  I will probably modify the system_health session again and add a file.

2) a really nasty bug in system_health, which Jonathon Kehayias mentioned in http://www.sqlskills.com/blogs/jonathan/incorrect-timestamp-on-events-in-extended-events/ . I can easily tell it's happening due to crappy monitoring software - I show a new error message every 5 minutes, although the time is several days ago (and not exactly X days ago, either).  So, I added the system time, as suggested in the post. 
Here's the code, holler if you have any questions!

One table:

	if object_id('tempdb..#systemhealthsessiondata') is not null
	DROP TABLE #systemhealthsessiondata
	SELECT CAST(xet.target_data AS XML) AS XMLDATA
	INTO #SystemHealthSessionData
	FROM sys.dm_xe_session_targets xet
	JOIN sys.dm_xe_sessions xe
	ON (xe.address = xet.event_session_address)
	WHERE xe.name = 'system_health'
	if object_id('tempdb..#ParsedData') is not null
	DROP TABLE #ParsedData
	CREATE TABLE #ParsedData (id INT IDENTITY, Actual_Time DATETIME, EventType NVARCHAR(128), ParsedName NVARCHAR(128), NodeValue VARCHAR(MAX))
	;WITH CTE_HealthSession (EventXML) AS
	(
	SELECT C.query('.') EventXML
	FROM #SystemHealthSessionData a
	CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
	--WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') in ('wait_info','wait_info_external')
	)
	INSERT INTO #ParsedData (Actual_Time, EventType, ParsedName, NodeValue)--(id, ParsedName, NodeValue)
	SELECT --id,
	DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), UTC_Time) AS Actual_Time,
	EventType,
	ParsedName,
	NodeValue
	FROM (
	SELECT
	A.B.value('@name[1]', 'varchar(128)') AS EventType,
	A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
	X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
	X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
	X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
	FROM cte_healthsession
	CROSS APPLY EventXML.nodes('/*') AS A (B)
	CROSS APPLY EventXML.nodes('//*') AS X (N)
	) T
	--WHERE NodeName = 'value'
	--AND event_type = 'wait_info'
	--you can run this entire next batch separately
	--And now use the standard dynamic pivot to shred.
	-- Because of the way the pivot works, the fields are alphabetical; not a big deal, but not easily fixable when running multiserver
	DECLARE @SQL AS VARCHAR (MAX)
	DECLARE @Columns AS VARCHAR (MAX)
	DECLARE @min INT, @max INT, @eventtype VARCHAR(128)
	SET @Columns = NULL
	SET @SQL = NULL
	SELECT @Columns=
	COALESCE(@Columns + ',','') + QUOTENAME(ParsedName)
	FROM
	(
	SELECT DISTINCT ParsedName
	FROM #ParsedData
	--excluded it here, but the tsql_stack can be used to get the exact statement from the plan cache
	--see http://blogs.msdn.com/b/extended_events/archive/2010/05/07/making-a-statement-how-to-retrieve-the-t-sql-statement-that-caused-an-event.aspx
	WHERE ParsedName <> 'callstack'
	) AS B
	ORDER BY B.ParsedName
	SET @SQL='
	SELECT Actual_Time, EventType,' + @Columns + ' FROM
	(
	SELECT EventType, Actual_Time, ParsedName, NodeValue FROM
	#ParsedData) AS source
	PIVOT
	(max(NodeValue) FOR source.ParsedName IN (' + @columns + ')
	)AS pvt order by actual_time'--, attach_activity_id'
	PRINT @sql
	EXEC (@sql)

view raw system_health_parser_one_table hosted with ❤ by GitHub

Multiple tables:

	if object_id('tempdb..#systemhealthsessiondata') is not null
	DROP TABLE #systemhealthsessiondata
	SELECT CAST(xet.target_data AS XML) AS XMLDATA
	INTO #SystemHealthSessionData
	FROM sys.dm_xe_session_targets xet
	JOIN sys.dm_xe_sessions xe
	ON (xe.address = xet.event_session_address)
	WHERE xe.name = 'system_health'
	if object_id('tempdb..#ParsedData') is not null
	DROP TABLE #ParsedData
	CREATE TABLE #ParsedData (id INT IDENTITY, Actual_Time DATETIME, EventType NVARCHAR(128), ParsedName NVARCHAR(128), NodeValue VARCHAR(MAX))
	;WITH CTE_HealthSession (EventXML) AS
	(
	SELECT C.query('.') EventXML
	FROM #SystemHealthSessionData a
	CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)
	--WHERE C.query('.').value('(/event/@name)[1]', 'varchar(255)') in ('wait_info','wait_info_external')
	)
	INSERT INTO #ParsedData (Actual_Time, EventType, ParsedName, NodeValue)--(id, ParsedName, NodeValue)
	SELECT --id,
	DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), UTC_Time) AS Actual_Time,
	EventType,
	ParsedName,
	NodeValue
	FROM (
	SELECT
	A.B.value('@name[1]', 'varchar(128)') AS EventType,
	A.B.value('./@timestamp[1]', 'datetime') AS UTC_Time,
	X.N.value('local-name(.)', 'varchar(128)') AS NodeName,
	X.N.value('../@name[1]', 'varchar(128)') AS ParsedName,
	X.N.value('./text()[1]', 'varchar(max)') AS NodeValue
	FROM cte_healthsession
	CROSS APPLY EventXML.nodes('/*') AS A (B)
	CROSS APPLY EventXML.nodes('//*') AS X (N)
	) T
	--WHERE NodeName = 'value'
	--AND event_type = 'wait_info'
	--And now use the standard dynamic pivot to shred.
	-- Because of the way the pivot works, the fields are alphabetical; not a big deal, but not easily fixable when running multiserver
	DECLARE @SQL AS VARCHAR (MAX)
	DECLARE @Columns AS VARCHAR (MAX)
	DECLARE @min INT, @max INT, @eventtype VARCHAR(128)
	DECLARE @events_to_do TABLE (id INT IDENTITY, eventtype varchar(128))
	INSERT INTO @events_to_do
	SELECT DISTINCT eventtype FROM #ParsedData
	SELECT @min = MIN(id), @max = MAX(id) FROM @events_to_do
	WHILE @min <= @max
	BEGIN
	SET @Columns = NULL
	SET @SQL = NULL
	SELECT @eventtype = eventtype FROM @events_to_do WHERE id = @min
	SELECT @Columns=
	COALESCE(@Columns + ',','') + QUOTENAME(ParsedName)
	FROM
	(
	SELECT DISTINCT ParsedName
	FROM #ParsedData
	--excluded it here, but the tsql_stack can be used to get the exact statement from the plan cache
	--see http://blogs.msdn.com/b/extended_events/archive/2010/05/07/making-a-statement-how-to-retrieve-the-t-sql-statement-that-caused-an-event.aspx
	WHERE ParsedName <> 'callstack'
	AND EventType = @eventtype
	) AS B
	ORDER BY B.ParsedName
	SET @SQL='
	SELECT Actual_Time, EventType,' + @Columns + ' FROM
	(
	SELECT EventType, Actual_Time, ParsedName, NodeValue FROM
	#ParsedData where eventtype = ''' + @eventtype + ''') AS source
	PIVOT
	(max(NodeValue) FOR source.ParsedName IN (' + @columns + ')
	)AS pvt order by actual_time'--, attach_activity_id'
	PRINT @sql
	EXEC (@sql)
	set @min = @min+1
	END
	-- SELECT * FROM #ParsedData

view raw system_health_parser_multi_table hosted with ❤ by GitHub