Monday, October 21, 2013

Overview of the latest deadlocks on your SQL Server as of SQL 2008



In case you want to analyze the deadlocks which occur on your server, you can use the information from the SYSTEM_HELATH session.
Use next query to retrieve the latest deadlock information.
SELECT CAST(event_data.value('(event/data/value)[1]',
'varchar(max)') AS XML) AS DeadlockGraphFROM ( 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 )
 
Be aware that, due to changes in the deadlock graph to support multi-victim deadlocks, and to minimize the size of the event data, the resulting XML cannot be saved as an XDL file for graphical representation.
More information about analyzing deadlocks can be found here

6 comments:

John Sansom said...

Just a quick tip that I recently discovered that you can save the 2008 Deadlock XML output to a .xdl file and then open it in SSMS 2012 and above.

360digitmgdelhi said...

I looked at some very important and to maintain the length of the strength you are looking for on your website
iot courses in delhi

360DigiTMG said...

This is a great post I saw thanks to sharing. I really want to hope that you will continue to share great posts in the future.
HRDF training

PMP Certification said...

Here at this site actually the particular material assortment with the goal that everyone can appreciate a great deal.
360DigiTMG

dataanalyticscourse said...

Your work is generally excellent and I value you and jumping for some more educational posts
supply chain analytics training

tejaswini said...

I truly like your style of blogging. I added it to my preferred's blog webpage list and will return soon…
digital marketing course