Deadlock Graph is a trace event that first appeared in SQL Profiler for 2005. Because of the number of important new features in that release, Deadlock Graph did not get a lot of attention. Consequently, this useful feature is not as well-known as it should be. That’s unfortunate because Deadlock Graph makes monitoring and fixing deadlock problems much simpler than it was in previous versions of SQL Server.
If you are running a SQL Profiler trace that includes Deadlock Graph, when a deadlock occurs, Profiler will display the deadlock information in an easily readable graphical form. It also generates an XML document containing additional data about the deadlock.
While most of this information could be extracted in earlier versions of sql server using a clumsy process of setting trace flags and reading the text results in the SQL error log, Deadlock Graph makes it much simpler to capture the data. The graphical presentation also makes it much easier to quickly interpret the results.
The process of fixing deadlocks begins with understanding what deadlocks are and why they occur. We assume that readers of this article already have a basic understanding of these concepts. The focus of this short article is to make you aware of this new tool for gathering and interpreting the information required to fix the problem.
We will explain how to use the tool and describe the most useful data elements it provides. We will also describe how we use some of the data elements to resolve specific deadlock problems. However this is not a thorough discussion of deadlocks. There are links at the end of this article that point to background information if you would like to refresh your knowledge in this area.
Creating a Deadlock Graph
The next time you are chasing a deadlock problem, open SQL Profiler and start a trace on the server having the deadlock issues. Select a blank Profiler template and add the Locks: Deadlock Graph event. Accept all the default columns. I normally run this event in its own trace and do not add other events because tracing deadlocks in itself puts a significant load on the server.
You will notice that when you select the DeadLock Graph event, another tab titled Events Extraction Settings appears on the Trace Properties form. Open this tab to select the location and mode for saving the xml output. In the Deadlock XML section of this tab use the radio buttons to save the documents all into the same file or save them to separate files. Whether you save the deadlock events separately or together depends on how you want to view the information.
Saving the XML
Note that SQL Server appends the file extension .xdl to the file you create. SQL Management Studio recognizes that file extension and automatically renders the xml data in graphical format when you open the file. However, only the first deadlock in the file is displayed. If you want to view each deadlock in the graphical view, it is best to save the deadlocks separately.
If you want more detail and are comfortable with reading a little xml, you can change the file extension to .xml and open the XML document using Management Studio or an XML editor like XMLNotepad. In that case you would probably want to save all the deadlocks into the same file for convenience.
Once you have specified a file name and a mode for the output, run the trace until you have captured a number of Deadlock Graph events.
Viewing the results
Graphical rendering in SQL Profiler or Management Studio provides an intuitive view of the most important information in the file at a glance. There is a node for each process involved and the node contains the details of the deadlock. When you hover your mouse over one of the process nodes, a textbox will appear showing the code that led to the deadlock. You can view this directly in SQL Profiler when it encounters a deadlock or you can open the resulting .xdl document later with SQL Management Studio.
However, the graphical view does not show everything you might need to know in order to resolve complex deadlocking issues. Often you will want to view more of the details. The easiest way to do that is to change the file extension to .xml. Then if you open the document with Management Studio or another xml editor, you will see the xml document itself. The examples that follow all refer to the XML document view with additional description for the graphical representation where appropriate.
How to use the data
There are three nodes in the deadlock document produced by Deadlock Graph that are especially useful.
- The victim node contains the id of the process that was the deadlock victim.
- The process-list node has at least two process nodes below it, one for each process involved in the deadlock.
- The resource-list node has at least two nodes below it describing the resources (database objects) owned by the processes in the deadlock.
Just browsing these nodes can give you a good idea of the data you have to work with. The data points we find most useful are these:
This is the ID of the process that was killed before completion. Before digging into the details, find out which of the processes was killed by SQL Server and which completed. In the graphical view, the Victim node is presented with a large X drawn across it.
This is the code that each process was executing when the deadlock occurred. You should view it for all nodes. Often you can stop right here. When you know what code is causing the problem, often the solution is obvious. A locking hint or an index might fix the problem. In the graphical rendering hover your mouse over the node to see this information.
This tells you what level of locking the process is running at. Often a SELECT process involved in the deadlock is running at the default of READCOMMITED and holding a shared lock when the other process needs an exclusive lock on the same resource. Placing a NOLOCK hint on the SELECT query (if that is appropriate) might fix the problem quite simply.
This tells you what mode the lock is in. S for shared, X for exclusive, U for update, etc. You can look up any lock mode values you don’t know here: http://msdn.microsoft.com/en-us/library/ms175519.aspx. If LockMode indicates your problem involves update statements, you might try using an (UPDLOCK ) or (ROWLOCK, UPDLOCK) hint in the update statement. However this can have negative performance consequences in some cases. You must test and be prepared to roll back the change if necessary.
ClientApp, Hostname, LoginName
These can help you identify where the deadlocked code is coming from if it needs to be modified.
The fully qualified name of the resource involved in the deadlock. Often in the form dbname.schema.table.
The index where the deadlock is occurring. If the deadlocks are occurring because of a hotspot ( a single page where many inserts, updates, or deletes are occurring simultaneously) you might try changing the Allow Page Locks setting in the index properties to “No”. To do this, right-click the index where the deadlock is occurring and select Properties. Select Options and uncheck “Allow page locks when accessing the index”. Be aware that in some situations this change can have negative performance consequences. You must test and be prepared to re-set this option if it causes other problems.
The suggestions above for resolving the deadlock problems are rules of thumb that often help but will not work in all cases. As I mentioned above, troubleshooting deadlocks can be a complex process. I f you would like more background information about deadlocks, here is a good paper. Even though it was written about an earlier version of SQL Server, very little has changed in regard to locking and deadlocking in SQL Server.
Fixing deadlocks is never easy, but Deadlock Graph makes it much easier than it used to be. If you want to read more about Deadlock Graph, here is a good place to start: