How to read Deadlock from system health of SQL server

Here is a sample query to help read deadlock from sys.health extended events.

SELECT       xed.value('@timestamp', 'datetime') as Creation_Date,
	xed.query('.') AS Extend_Event,
	xed.value('(//deadlock/process-list/process[1]/@clientapp)[1]', 'NVarChar(50)') AS VictimClientApp,
	xed.value('(//deadlock/process-list/process[1]/@spid)[1]', 'NVarChar(15)') AS VictimProcessID,
	CAST(REPLACE(xed.value('(//deadlock/process-list/process[1]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DATETIME) AS VictimLastBatchStarted,
	xed.value('(//deadlock/process-list/process[1]/@lockMode)[1]', 'NVarChar(15)') AS VictimLockMode,
	xed.value('(//deadlock/process-list/process[1]/@xactid)[1]', 'NVarChar(15)') AS VictimXActID,
	xed.value('(//deadlock/process-list/process[1]/@clientapp)[1]', 'NVarChar(50)') AS VictimClientApp,
	--Live
	xed.value('(//deadlock/process-list/process[2]/@spid)[1]', 'NVarChar(15)') AS LiveProcessID,
	CAST(REPLACE(xed.value('(p//deadlock/process-list/process[2]/@lastbatchstarted)[1]', 'NChar(23)'), N'T', N' ') AS DATETIME) AS LiveLastBatchStarted,
	xed.value('(//deadlock/process-list/process[2]/@lockMode)[1]', 'NVarChar(15)') AS LiveLockMode,
	xed.value('(//deadlock/process-list/process[2]/@xactid)[1]', 'NVarChar(15)') AS LiveXActID,
	xed.value('(//deadlock/process-list/process[2]/@clientapp)[1]', 'NVarChar(50)') AS LiveClientApp,
	--Live resource.
	xed.value('(//deadlock/resource-list/pagelock[1]/@fileid)[1]', 'NVarChar(15)') AS LiveFileID,
	xed.value('(//deadlock/resource-list/pagelock[1]/@pageid)[1]', 'NVarChar(15)') AS LivePageID,
	xed.value('(//deadlock/resource-list/pagelock[1]/@objectname)[1]', 'NVarChar(50)') AS LiveObjName,
	xed.value('(//deadlock/resource-list/pagelock[1]/@mode)[1]', 'NVarChar(50)') AS LiveLockModeHeld,
	xed.value('(//deadlock/resource-list/pagelock[1]/waiter-list/waiter/@mode)[1]', 'NVarChar(50)') AS VictimLockModeRequest,    
	--Victim resource.
	xed.value('(//deadlock/resource-list/pagelock[2]/@fileid)[1]', 'NVarChar(15)') AS VictimFileID,
	xed.value('(//deadlock/resource-list/pagelock[2]/@pageid)[1]', 'NVarChar(15)') AS VictimPageID,
	xed.value('(//deadlock/resource-list/pagelock[2]/@objectname)[1]', 'NVarChar(50)') AS VictimObjName,
	xed.value('(//deadlock/resource-list/pagelock[2]/@mode)[1]', 'NVarChar(50)') AS VictimLockModeHeld,  
	xed.value('(//deadlock/resource-list/pagelock[2]/waiter-list/waiter/@mode)[1]', 'NVarChar(50)') AS LiveLockModeRequest,
	--Inputbuffers
	xed.value('(//deadlock/process-list/process[1]/executionStack/frame/@procname)[1]', 'NVarChar(100)') AS VictimProcName,
	xed.value('(//deadlock/process-list/process[1]/executionStack/frame)[1]', 'VarChar(max)') AS VictimExecStack,
	xed.value('(//deadlock/process-list/process[2]/executionStack/frame/@procname)[1]', 'NVarChar(max)') AS LiveProcName,
	xed.value('(//deadlock/process-list/process[2]/executionStack/frame)[1]', 'VarChar(max)') AS LiveExecStack,
	RTRIM(LTRIM(REPLACE(xed.value('(//deadlock/process-list/process[1]/inputbuf)[1]', 'NVarChar(2048)'), NCHAR(10), N''))) AS VictimInputBuffer,
	RTrim(LTrim(Replace(xed.value('(//deadlock/process-list/process[2]/inputbuf)[1]', 'NVARCHAR(2048)'), NChar(10), N''))) AS LiveInputBuffer
FROM (
       SELECT CAST([target_data] AS XML) AS Target_Data
       FROM sys.dm_xe_session_targets AS xt
       INNER JOIN sys.dm_xe_sessions AS xs       ON xs.address = xt.event_session_address
       WHERE xs.name = N'system_health'       AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY Creation_Date DESC
Spread the love

Leave a Comment