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

30 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

PMP Certification said...


What an extremely wonderful post this is. Genuinely, perhaps the best post I've at any point seen to find in as long as I can remember. Goodness, simply keep it up.


HRDF claimable

tejaswini said...

I was basically inspecting through the web filtering for certain data and ran over your blog. I am flabbergasted by the data that you have on this blog. It shows how well you welcome this subject. Bookmarked this page, will return for extra.
360DigiTMG training provider in malaysia

PMP Certification said...

I like review sites which grasp the cost of conveying the incredible valuable asset complimentary. I really venerated perusing your posting. Much obliged to you!
360DigiTMG HRDF Claimable training

PMP Certification said...


Super site! I am Loving it!! Will restore again, Im taking your food in like manner, Thanks.
hrdf claimable

PMP Certification said...

I looked at some very important and to maintain the length of the strength you are looking for on your website
360DigiTMG HRDF claimable

PMP Certification said...

I need to communicate my deference of your composing aptitude and capacity to make perusers read from the earliest starting point as far as possible. I might want to peruse more up to date presents and on share my musings with you.
https://360digitmg.com/hrdf-scheme

tejaswini said...

I feel extremely glad to have seen your site page and anticipate such a large number of additionally engaging occasions perusing here. Much obliged again for all the subtleties.
what is hrdf

PMP Certification said...


I need to communicate my deference of your composing aptitude and capacity to make perusers read from the earliest starting point as far as possible. I might want to peruse more up to date presents and on share my musings with you.
HRDF Claimable Courses

PMP Certification said...


I looked at some very important and to maintain the length of the strength you are looking for on your website
360DigiTMG HRDF claimable

tejaswini said...

Happy to visit your blog, I am by all accounts forward to more solid articles and I figure we as a whole wish to thank such huge numbers of good articles, blog to impart to us.
https://360digitmg.com/hrdf-training

PMP Certification said...

On the off chance that your searching for Online Illinois tag sticker restorations, at that point you have to need to go to the privileged place.
360DigiTMG HRDF Claimable training

PMP Certification said...



stunning, incredible, I was thinking about how to fix skin inflammation normally.I've bookmark your site and furthermore include rss. keep us refreshed.

https://360digitmg.com/hrdf-scheme

PMP Certification said...


Welcome to an incredible gathering here you will pick up every little thing about me HRDF Claimable Courses

PMP Certification said...

I'm cheerful I found this blog! Every now and then, understudies need to psychologically the keys of beneficial artistic articles forming. Your information about this great post can turn into a reason for such individuals.
360DigiTMG HRDF claimable

PMP Certification said...

I truly like your style of blogging. I added it to my preferred's blog webpage list and will return soon…
360DigiTMG HRDF Claimable Courses

PMP Certification said...

It is an obligation of gratitude to share information, continue the great work ... I sincerely enjoy researching your website. great asset ...
https://360digitmg.com/hrdf-scheme

PMP Certification said...

Especially superb!!! Exactly when I search for this I found this webpage at the top of every single online diary in web crawler.
HRDF Claimable

PMP Certification said...

You really make it look so natural with your exhibition however I see this issue as really something which I figure I could never understand. It appears to be excessively entangled and incredibly expansive for me.
360DigiTMG HRDF Claimable training

PMP Certification said...

Welcome to an incredible gathering here you will pick up every little thing about me 360DigiTMG HRDF claimable courses

PMP Certification said...

"I can set up my original thought from this post. It gives all around data. A commitment of gratefulness is all together for this essential data for all,

"
HRDF Claimable Training

PMP Certification said...

On the off chance that your searching for Online Illinois tag sticker restorations, at that point you have to need to go to the privileged place.
360DigiTMG HRDF claimable courses

PMP Certification said...

I have bookmarked your site since this site contains significant data in it. You rock for keeping incredible stuff. I am a lot of appreciative of this site.
HRDF claimable courses

PMP Certification said...

Much obliged for such an extraordinary post and the audit, I am completely intrigued! Keep stuff like this coming.
HRDF claimable courses

PMP Certification said...

What an extremely wonderful post this is. Genuinely, perhaps the best post I've at any point seen to find in as long as I can remember. Goodness, simply keep it up. HRDF claimable courses