Retrieving Historical Deadlock Graphs

When I visit a client site to troubleshoot deadlocks, it is always a cause of annoyance when I cannot go through historical deadlock events to determine the frequency and causes of these deadlocks. I either have to talk them through enabling trace flag 1222 or explain to them how to enable profiler to capture specific deadlock graphs. Even then its not possible to retrospecivley go back and examine deadlocks before this point.

I recently came accross this article by Jonathan Kehayias about how to retrieve historical deadlock graphs with 2008 extended events. These scripts have proved invaluable for me in going back in time.

You may need to upgrade to Service Pack 2 to get it to work correctly, but heres a copy of the final script:

declare @xml xml

select @xml = target_data

from sys.dm_xe_session_targets

join sys.dm_xe_sessions on event_session_address = address

where name = 'system_health'

select CAST(

REPLACE(

REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),

'<victim-list>', '<deadlock><victim-list>'),

'<process-list>','</victim-list><process-list>')

as xml) as DeadlockGraph

FROM

(select @xml as TargetData) AS Data

CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)

Published Saturday, October 23, 2010 5:54 PM by blakmk

Comments

No Comments