Query to Parse Deadlock Extended Event Data

ConvertFrom-DeadlockXEL.sql

IF(OBJECT_ID('tempdb..#Tempxel') IS NOT NULL)
BEGIN
	Drop Table #Tempxel
END
CREATE TABLE #Tempxel(XEL_XML XML)

IF(OBJECT_ID('tempdb..#Tempxml') IS NOT NULL)
BEGIN
	DROP TABLE #Tempxml
END

-- Get latest deadlock extended events file path
DECLARE @xelFile NVARCHAR(255);

WITH event_data AS (
	SELECT ed = CONVERT(XML, target_data) 
	FROM sys.dm_xe_session_targets xet
	INNER JOIN sys.dm_xe_sessions xe
	ON xe.[address] = xet.event_session_address
	WHERE xe.name = N'DeadlockMonitor'
	AND xet.target_name = N'event_file'
)
SELECT @xelFile = b.value('./@name','nvarchar(255)')
FROm event_data
CROSS APPLY ed.nodes('/EventFileTarget/File') a(b)

-- store XML event Data in temp table
INSERT INTO #Tempxel SELECT event_data FROM sys.fn_xe_file_target_read_file(@xelFile,NULL,NULL,NULL)

-- Get count of deadlock nodes, this will be what we iterate over
SELECT 	ROW_NUMBER() OVER (ORDER BY Process.value('(process[1]/@id)[1]', 'NVARCHAR(15)')) AS RowNumber
	,Process.value('count(process)', 'int') AS value
INTO #Tempxml
FROM #Tempxel 
CROSS APPLY	XEL_XML.nodes('/event/data/value/deadlock/process-list') AS ProcessList(Process)

-- Loop through count of deadlock nodes and get info from nodes
-- Store each iterations results in a new global temp table with a row number for joining later
DECLARE @dynamic_sql NVARCHAR(MAX)
		,@max INT
		,@Counter INT = 1

SELECT @max = value FROM #Tempxml WHERE RowNumber = 1

WHILE @Counter <= @max
BEGIN
	IF(@Counter = 1)
	BEGIN
		--Victim
		SET @dynamic_sql = N'
		SELECT
			ROW_NUMBER() OVER (ORDER BY Process.value(''(process[1]/@id)[1]'', ''NVARCHAR(15)''))														AS RowNumber

			,Process.value(''(resource-list/keylock/@objectname)[1]'',''nvarchar(255)'')																AS Keylock_objectname
			,Process.value(''(resource-list/keylock/@indexname)[1]'',''nvarchar(255)'')																	AS Keylock_indexname
			,Process.value(''(resource-list/keylock/@mode)[1]'',''nvarchar(255)'')																		AS Keylock_mode
			,Process.value(''(resource-list/keylock/owner-list/owner/@id)[1]'',''nvarchar(255)'')														AS Keylock_Owner_id	

			,Process.value(''(resource-list/objectlock/@objectname)[1]'',''nvarchar(255)'')																AS Objectlock_objectname
			,Process.value(''(resource-list/objectlock/@mode)[1]'',''nvarchar(255)'')																	AS Objectlock_mode
			,Process.value(''(resource-list/objectlock/owner-list/owner/@id)[1]'',''nvarchar(255)'')													AS Objectlock_Owner_id

			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/executionStack/frame/@procname)[1]'', ''NVARCHAR(255)'')		AS Victim_ProcName
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/executionStack/frame/@sqlhandle)[1]'', ''NVARCHAR(255)'')	AS Victim_sqlhandle
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/executionStack/frame/text())[1]'',''NVARCHAR(MAX)'')			AS Victim_sql

			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@id)[1]'', ''NVARCHAR(15)'')									AS Victim_id
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@waittime)[1]'', ''INT'')									AS Victim_waittime
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@transactionname)[1]'', ''varchar(255)'')					AS Victim_transactionname
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@lasttranstarted)[1]'', ''varchar(30)'')						AS Victim_lasttranstarted
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@lockMode)[1]'', ''varchar(15)'')							AS Victim_lockMode
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@status)[1]'', ''varchar(30)'')								AS Victim_status
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@priority)[1]'', ''INT'')									AS Victim_priority
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@clientapp)[1]'', ''varchar(30)'')							AS Victim_clientapp
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@hostname)[1]'', ''varchar(60)'')							AS Victim_hostname
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@loginname)[1]'', ''varchar(60)'')							AS Victim_loginname
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@isolationlevel)[1]'', ''varchar(100)'')						AS Victim_isolationlevel
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@currentdb)[1]'', ''INT'')									AS Victim_currentdb
		INTO ##Deadlock_Query' + CONVERT(NVARCHAR(10),@Counter) + '
		FROM #Tempxel 
		CROSS APPLY	XEL_XML.nodes(''/event/data/value/deadlock'') AS ProcessList(Process)
		'
	END
	Else
	BEGIN
		-- Other query/queries
		SET @dynamic_sql = N'
		SELECT
			ROW_NUMBER() OVER (ORDER BY Process.value(''(process[1]/@id)[1]'', ''NVARCHAR(15)''))														AS RowNumber

			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/executionStack/frame/@procname)[1]'', ''NVARCHAR(255)'')		AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_ProcName
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/executionStack/frame/@sqlhandle)[1]'', ''NVARCHAR(255)'')	AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_sqlhandle
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/executionStack/frame/text())[1]'',''NVARCHAR(MAX)'')			AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_sql

			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@id)[1]'', ''NVARCHAR(15)'')									AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_id
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@waittime)[1]'', ''INT'')									AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_waittime
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@transactionname)[1]'', ''varchar(255)'')					AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_transactionname
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@lasttranstarted)[1]'', ''varchar(30)'')						AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_lasttranstarted
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@lockMode)[1]'', ''varchar(15)'')							AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_lockMode
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@status)[1]'', ''varchar(30)'')								AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_status
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@priority)[1]'', ''INT'')									AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_priority
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@clientapp)[1]'', ''varchar(30)'')							AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_clientapp
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@hostname)[1]'', ''varchar(60)'')							AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_hostname
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@loginname)[1]'', ''varchar(60)'')							AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_loginname
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@isolationlevel)[1]'', ''varchar(100)'')						AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_isolationlevel
			,Process.value(''(process-list/process[' + CONVERT(NVARCHAR(10),@Counter) + ']/@currentdb)[1]'', ''INT'')									AS Query' + CONVERT(NVARCHAR(10),@Counter) + '_currentdb
		INTO ##Deadlock_Query' + CONVERT(NVARCHAR(10),@Counter) + '
		FROM #Tempxel 
		CROSS APPLY	XEL_XML.nodes(''/event/data/value/deadlock'') AS ProcessList(Process)
		'
	END

	EXEC sp_executesql @dynamic_sql
	--PRINT @dynamic_sql

	SET @Counter = @Counter + 1
END

IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME LIKE '%##Deadlock_Query%')
BEGIN
	SET @dynamic_sql = N'SELECT * FROM (SELECT * FROM ##Deadlock_Query1) AS Query1'
	SELECT @Counter = CONVERT(INT,SUBSTRING(TABLE_NAME,17,2)) FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME LIKE '%Deadlock%' ORDER BY TABLE_NAME DESC

	WHILE @Counter <= @max
	BEGIN
		If (@Counter >= 2)
		BEGIN
			SET @dynamic_sql = @dynamic_sql + ' INNER JOIN ##Deadlock_Query' + CONVERT(NVARCHAR(10),@Counter) + ' AS Query' + CONVERT(NVARCHAR(10),@Counter) + ' ON Query1.RowNumber = Query' + CONVERT(NVARCHAR(10),@Counter) + '.RowNumber'
		END
		SET @Counter = @Counter + 1
	END

	EXEC sp_executesql @dynamic_sql
	--PRINT @dynamic_sql
END

SET @Counter = 1

-- iterate and delete temp tables
WHILE @Counter <= @max
BEGIN
	SET @dynamic_sql = N'DROP TABLE ##Deadlock_Query' + CONVERT(NVARCHAR(10),@Counter)

	EXEC sp_executesql @dynamic_sql

	SET @Counter = @Counter + 1
END