WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Tuesday, March 29, 2011

Deadlocks: There are too many of them!

Back in the day, deadlock tracing was a bit more painful than it is now.  Relegated to dumping them to the SQL Error log and parsing typically.  A bunch of years ago, Bart Duncan wrote a series on troubleshooting deadlocks that is fantastic.  I couldn't possibly add anything to what he produced, but I did create something to help, especially if you have several deadlocks and are searching for specific ones.  Now that the deadlock is stored in the ring buffer, you can grab out the data and use XQuery to get a bit more out the deadlock graph so you do not have to open each deadlock XML individually.  Local MVP Jason Strate put together a great series of blog posts on the subject of XQuery, in case that is not your strong suit.

So, if you have lots of inconsistent data access patterns, you too might need a script to mine out a few of the key attributes of the deadlock to find the one you are looking for. 

This works on SQL 2008 and specifically have SP1 CU #6 installed.  (There is a workaround to prior installs, I think it is on SQLServerCentral.com.)

This will pull several columns out of the deadlock graph, the datetime of the deadlock, the logins involved and the types of locks involved (Page, Object, Key, etc). 

WITH deadlocks(DeadLockGraph)
    SELECT CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph
     (select CAST(target_data as xml) as TargetData
      from sys.dm_xe_session_targets st
        join sys.dm_xe_sessions s on s.address = st.event_session_address
      where name = 'system_health') AS Data
    CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
SELECT DeadLockGraph.query('data(deadlock/process-list/process[1]/@lasttranstarted)').value('.', 'datetime') as DDate
    , DeadLockGraph.query('data(deadlock/victim-list/victimProcess/@id)').value('.', 'varchar(128)') VictimPID
    , DeadLockGraph.query('data(deadlock/process-list/process/@id)').value('.', 'varchar(128)') as PIDS
    , DeadLockGraph.query('data(deadlock/process-list/process/@waitresource)').value('.', 'varchar(128)') as Waits
    , DeadLockGraph.query('data(deadlock/process-list/process/@lockMode)').value('.', 'varchar(128)') as Mode
    , DeadLockGraph.query('data(deadlock/process-list/process/@loginname)').value('.', 'varchar(128)') as Logins
    , DeadLockGraph.query('count(deadlock/process-list/process)').value('.','int') as ProcNum
    , DeadLockGraph.query('deadlock/resource-list/child::node()') as ResourceList
FROM deadlocks 
WHERE DeadLockGraph.query('data(deadlock/process-list/process[1]/@lasttranstarted)').value('.', 'datetime') > '2010-12-03 9:00'

Hope this helps you find the deadlocks you are looking for a bit quicker, when there are many of them, parsing out a few pieces can make identification easier.

No comments:

Post a Comment