Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Profiler Graphical Deadlock Chain


By:   |   Last Updated: 2007-04-13   |   Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Locking and Blocking

Problem
I have read your tip on SQL Server locking, blocking and deadlocking (Finding and troubleshooting SQL Server deadlocks) which are applicable to some of issues I have been recently facing.  I have been noticed locking on my SQL Server and have heard about the issues from my users.  I have been trying to troubleshoot it, unfortunately, I have been having a hard time understanding the issues with large deadlock chains.  Does SQL Server have any way of identifying, understanding and trouble shooting deadlocks other than your previous tip (Finding and troubleshooting SQL Server deadlocks)?

Solution
That is a great question and can be a serious problem with a complex deadlock chain.  In both SQL Server 2000 and SQL Server 2005, the Trace Flag 1204 and Profiler have the ability capture the results of a deadlock as outlined in the Finding and troubleshooting SQL Server deadlocks tip.  One SQL Server 2005 Profiler feature that was not covered in the previous tip is graphically reviewing the deadlock in Profiler.  This information may be what you are looking for to help analyze your issue to begin to resolve the issue.  As such, below outlines the steps to capture the graphical deadlock from Profiler in a SQL Server 2005 instance.

Profiler Setup - Graphical Deadlock Chains

ID Description Screen Shot
1 General Tab - Specify the name, template and save location (table or file).

2 Events  Selection Tab - Specify the deadlock graph, Lock:Deadlock and Lock:Deadlock Chain events in addition to any other counters desired.

Press the 'OK' button to start the data collection.

3 Profiler Execution - Review the data captured from Profiler based on the counters that were selected.

For additional tips on Profiler check these out:

Profiler Results - Graphical Deadlock

ID Description Screen Shot
1 Deadlock Graph - Based on the queries that are issued, the deadlock chain will graphically show, the locking conflict, the completed spid and failed spid. 

In this circumstance, the locking was at a key level, but based on your deadlock situation may be much different.

Next Steps

  • Deadlocks can be a serious performance issue and if severe enough can significantly degrade your SQL Server performance.
  • If you are experiencing significant deadlock issues, it is necessary to capture the necessary information to begin the analysis process.
  • Based on the deadlock scenario, you may need to do design, develop, test and implement one or more of the following:
    • Reschedule batch processes or reschedule competing processes trying to access the same data
    • Change the order for table access in the queries to ensure they are the same
    • Determine if the explicit transactions are unnecessarily extending transactions
    • Build indexes to support the needed queries
    • Perform maintenance to improve data access
    • Rather than using a home grown incrementing process for columns like primary keys, convert to using identities
    • Split reporting from OLTP databases so these 2 very different types of processes do not compete with one another
    • Reduce the amount of data (via a WHERE clause as an example) that is sent to the front end application if it is not reviewed on the screen by the users (i.e. return 10,000 rows and only display 10 results)
  • Be sure to thoroughly test the solution you implement to ensure it will not have any negative impacts on other portions of the application.


Last Updated: 2007-04-13


next webcast button


next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, October 29, 2010 - 1:18:31 PM - Jeremy Kadlec Back To Top

Linda,

Thank you so much for sharing your experiences.

If you would be interested in sharing the items as a formal tip, please let me know ([email protected]).

Have a great day.

Thank you,
Jeremy Kadlec


Wednesday, October 27, 2010 - 6:54:35 PM - Linda Leslie Back To Top

I have found using the WMI events a great way to be proactive and see every deadlock on your server(s).  I have also set up stored procedures to pull some of the data apart in the resulting XML to display in a reporting server report so that the developers can view the info any time when I notify them they have a problem.  I also get an email via database mail when ever a deadlock happens so I'm aware of them as soon as there seems to be a problem.  Either way works, but I like the email notifications.

We host a database for an application for an outside vendor and I was able to see deadlocks occuring frequently, when they had not occured before. I was able to notify them very quickly that they had a problem that needed fixing.  They were then able to start working on the fix before customer/end users really noticed a problem.

I use profiler when I am troublehshooting performance issues or tracking down a bug. I use it, but only usually while researching a specific issue.

I do love that you can solve many problems many ways with SQL Server :-)

Linda Leslie
University of Cincinnati


Monday, October 25, 2010 - 6:45:59 PM - Jeremy Kadlec Back To Top

Linda,

Thank you for the post.  Using a SQL Server Agent Job to capture the WMI event is a viable solution as well.

In both circumstances (Profiler or SQL Server Agent with a WMI event) can capture the results to a table or file and alert the team.  I use SQL Trace (scheduled) and Profiler (interactive) for a variety of needs on a regular basis.

If you have some experiences where Profiler\SQL Trace did not meet your needs I would be interested to hear about them.

If you are interested in sharing your experiences using WMI events, I think they could make a good set of tips.

One of the good and bad situations with SQL Server is that you have more than one way to approach an issue.

Thank you,
Jeremy Kadlec


Monday, October 25, 2010 - 5:55:52 PM - Linda Leslie Back To Top

Why not set up a deadlock Alert via a WMI event, and insert the result into a table (select * from deadlock_graph). Then you have a history of all deadlocks and problems as they arise.

 

http://msdn.microsoft.com/en-us/library/ms186385(v=SQL.90).aspx

 


Learn more about SQL Server tools