SQL: Deadlocks
Author: jason
Date: 2014-07-04 19:42:25
Category: Musing

I ran into a bunch of deadlocks the other day. Unfortunately the quick and dirty fix was adding the NOLOCK hint. I profiled to find the query. But here is a better method with TSQL. Pretty sure it works with 2008 forward. And some links!

https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
http://sqlsunday.com/2013/03/03/deadlocks/
http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlocks_2d00_in_2d00_sql_2d00_server.aspx
http://blog.codinghorror.com/deadlocked/

Code

SELECT CAST(event_data.value('(event/data/value)[1]',
'varchar(max)') AS XML) AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS event_data
FROM ( -- Cast the target_data to XML
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'
AND target_name = 'ring_buffer'
) AS Data -- Split out the Event Nodes
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
AS XEventData ( XEvent )
) AS tab ( event_data )




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009