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/
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


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.

Craig Bones said...
This comment has been removed by the author.
Craig Bones said...

Picture is just perfect. Perfect illustration of stupidity and stubbornness of people. I still can't believe that there are so much idiots in our world. And no one will step up and pass another. Awful. Better they were checking prices of, not driving.

Mika Farron said...

permainan domino online ini juga gampang dimenangkan. Terlebih lagi apabila Anda bermain di situs domino online yang memang di referensikan oleh berbagai blogger Indonesia dan bahkan yang direferensikan oleh
bandar ceme
paito warna
forum prediksi

Unknown said...

paito warna china
data sydney
syair sydney
paito warna
live hk 6d
live sydney