Capturing SQL Server Deadlock Information in XML Format

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

Problem
In your recent tips on SQL Server dead locks (How To: Graphical Deadlock Chain and Deadlock Priority Configuration) I can see the value of using Profiler to capture the process related information.  Do any other options exist in Profiler to capture the information is an easier format that I can review?  If so, what is the format and how can I begin to take advantages of this configuration in Profiler?

Solution
SQL Server 2005 Profiler has the ability to capture the deadlock related information as XML files which can be analyzed to determine the overall locking and blocking issue.  Capturing this additional information can be beneficial if you experience long locking and blocking chains frequently. 

 

XML Deadlock File Considerations

• The XML Deadlocks feature gives you the ability to capture the deadlock related information and review the deadlocks independent of the Profiler interface.
• Each of the deadlock files can be analyzed in order to determine trends with the deadlocks on your SQL Server.
• Each deadlock situation can be stored in a separate file for per deadlock analysis.
• The XML format gives you the ability to programmatically review the deadlocks.

Next Steps

• If you run into locking and blocking chains causing deadlocks frequently, then SQL Server 2005 Profiler can be used to capture the process related information to troubleshoot the situation.  By using the Deadlock XML events, you have the ability to take this to the next level for analysis and troubleshooting.
• As you can see from the XML, it is helpful in determining the issue and really drilling down into the root cause, because all of the information needed for the analysis is stored in the file.
• If you are currently experiencing deadlocking, try out this new feature and see if it is helpful for the analysis process.
• If you are not currently experiencing deadlocking, keep this tip in your back pocket for the next time you encounter deadlocks.
• Check out the following MSSQLTips on SQL Server deadlocks:
  • SQL Server Performance Statistics Using a Server Side Trace
  • Correlating Performance Monitor and Trace Data
  • Deadlock Priority Configuration
  • Finding and troubleshooting SQL Server deadlocks
  • Troubleshooting Performance Problems in SQL Server 2005

Related Tips: More | Become a paid author

Last Update: 4/30/2007

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor ROCKS! As soon as I ran it, problems that have been giving me headaches were identified and cured." It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check starting at $995. Get SQL Server Tips Straight from Kevin Kline. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....