SQL Server / Extended Events / Finding Deadlocks

Recently we experienced deadlocks on one of our staging servers, so I wanted to capture those deadlocks using Extended Events. This how I did it.

First of all you need location where you gonna keep your XEL file, then we can create Extended Event session

Using SSMS

  • Creating new Extended Event session


  • Selecting Events you want to capture, in this case xml_deadlock_report


  • Save the event data to an XEL file


Using T-SQL


Now we can start extended event session


I managed to replicate and capture deadlock event on my staging environment and this is how you read  results from extended events.



Or we can query the file target directly using T-SQL and create deadlock_xml column so we can examine deadlock graph further


If you have lots of events and you want to manipulate the XML values this can be really slow process. In that case you can dump the event data values into temporary table first.

In this example I am querying file target directly.

From here you can open deadlock_xml (deadlock graph) with SQL Sentry Explorer and analyze graph further





Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *