I often receive the question – what is the best way to find issues when client/user complaints of deadlock issues?
Msg 1205, Level 13, State 51, Line 10
Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
So, let’s say that user is complaining of query deadlock situation that occurred around 5:30 PM server time. Now, best approach would be to find all the Deadlock graph around that time. Starting from SQL Server 2012 and further, we have a default system event session named ‘system_health’. So nagivate to Management > Extended Events > Sessions > sytem_health.
Double click on ‘package0.event_file’ to view Target Data collected by extended event. You shall get a screen as below:-
Now, click filter from ‘Extended Events’ menu, or by directly clicking the filters button as shown in above screenshot.
Choose ‘name’ as field, and provide value equal to ‘xml_deadlock_report’ under value and then Press Apply. We can also, apply filter on time here.
Once filters applied, we should see only deadlock report items on Target Data window. Now, select any xml_deadlock_report line item, and double click on value field inside Details tab. This will open deadlock report in xml format in another window.
Now, save the newly opened *.xml window as *.xdl (Deadlock File) as shown in below screenshot.
Once saved as *.xdl file, the Deadlock File can be either opened in SSMS or tool like SQL Sentry Plan Explorer. I personally prefer to analyze deadlock files in Plan explorer as it quite easy due to the details available for sessions involved in deadlock.
I hope this will be helpful for anyone working on deadlock issues. We can also watch the video SQL Server Deadlocks by SentryOne for analyzing deadlock files using Plan Explorer.
Friends, please feel free to correct me by comments. If you like the article, do Like & Share. Happy Coding 🙂
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.