Gathering Deadlock Information with Deadlock Graph

Deadlock graph is an event class that first appeared in SQL Server Profiler for SQL Server 2005 and remains essentially unchanged in SQL Server 2008. Because of the many important new features introduced in SQL Server 2005, Deadlock graph didn't get a lot of attention. Consequently, this useful feature isn't as well-known as it should be—which is unfortunate because it makes monitoring and fixing deadlock problems much simpler than it was in earlier versions of SQL Server.

If a deadlock occurs while you're running a SQL Server Profiler trace that includes Deadlock graph, SQL Server Profiler displays the deadlock information in an easily readable graphical form. It also generates an XML document containing additional data about the deadlock. Most of this information could be extracted in versions of SQL Server prior to SQL Server 2005 by using a clumsy process of setting trace flags and reading the text results in the SQL Server error log; Deadlock graph makes it much simpler to capture the data, and the graphical presentation makes it much easier to quickly interpret the results. In this article, I'll discuss and illustrate Deadlock graph in SQL Server 2008, but the details also apply to SQL Server 2005.

Creating a Deadlock Graph Trace

The next time you're chasing a deadlock problem, try using Deadlock graph by following these simple steps. Open SQL Server Profiler from the Start menu or by selecting it from the Tools menu in SQL Server Management Studio (SSMS). If SQL Server Profiler doesn't display a login dialog box automatically, select New Trace from the File menu. Log in to the SQL Server instance that has the deadlock problem.

On the General tab of the Trace Properties dialog box, select Blank from the Use the template drop-down menu. You might also consider saving the trace output to a file using the Save to file option on the General tab. However, this is only one of several options for saving the output of Deadlock graph; I'll discuss other options later.

On the Events tab, select the Deadlock graph event under Locks as Figure 1 shows. Accept all the default columns for this event. I typically run this event in its own trace without adding other events because tracing deadlocks puts a significant load on the server.

When you select the Deadlock graph event, a new tab titled Events Extraction Settings appears on the Trace Properties dialog box. As Figure 2 shows, you use this tab to select the location and mode for saving the XML output. The Deadlock XML section of this tab gives you the options available for saving the Deadlock graph XML, but the wording can be a bit confusing. The Save Deadlock XML Separately check box is selected by default, and you should leave it that way. By separately, it means that the XML data is saved in a file separate from any file you might have chosen on the General tab to save the entire output of the trace. With the Save Deadlock XML Separately option selected, you can specify the path and filename for the XML output in the box below.

The radio buttons at the bottom of this section let you save the XML output from all the Deadlock graph events into a single file or save each deadlock graph event in its own distinct file. The correct selection here depends on how you want to view and work with the data.

The graphical view. SQL Server appends the file extension .xdl to the file you create for the XML output. SSMS recognizes the file extension and automatically renders the XML content 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 in SSMS, you'll need to save the Deadlock graph events to distinct files.

If you saved the entire trace to a trace file using the options on the General tab, you have another option for viewing the information in graphical format. You can open the saved trace file in SQL Server Profiler and view each Deadlock graph event in the file.

Viewing the XML directly. If you want more detail and are comfortable with reading a little XML, you can change the file extension of the .xdl file to .xml and open the XML document using SSMS or an XML editor such as XML Notepad. Viewing the XML document gives you considerably more information than is available in the graphical view. If you choose to use this method, you might want to save all the deadlocks into the same file for convenience.

When you've made your selections for saving the output, run the trace until you've captured some deadlock events.

The Graphical View vs. the XML View

As Figure 3 shows, graphical rendering in SQL Server Profiler or SSMS provides an intuitive view of the most important information in the file at a glance. There's a node for each process involved, and the node shows the details of the deadlock. When you hover your mouse over one of the process nodes, a text box appears showing the code executed by that node that led to the deadlock. You can view this graphical representation directly in SQL Server Profiler when it encounters a deadlock or you can open the resulting .xdl document later with SSMS.

However, the graphical view doesn't show everything you might need to know to resolve complex deadlock problems. Often, you'll want to see more details about the problem, and the easiest way to do that is to use the XML file. Figure 4 shows an example of an XML file displayed in XML Notepad. You'll discover your own preferred work method. I use the graphical rendering for a quick first look. You can often find enough information in the graph to resolve your problem. For more difficult problems, I prefer working with the XML document directly because of the additional information it provides.

An Overview of the Data and What It Means

The deadlock document produced by Deadlock graph contains three nodes that are especially useful for troubleshooting problems:

Browsing these nodes can give you a good idea of the data you have to work with. Let's look at some of the data points I've found most useful within each node.

Victim node. The ID in the victim node is derived from the process ID of the process that was killed. (For some reason, Deadlock graph prefixes the word process to the hexadecimal form of the process ID.) Before digging into any other details, find out which of the processes was killed by SQL Server and which completed. Compare the victim process ID to the ID in the process nodes that follow.

In the graphical view, the victim node is shown with a large X drawn across it, and the process is identified by the server process ID (SPID) rather than by the process ID.

Process-list/process nodes. The process node includes many more items than I can cover here, but I'll highlight some that are especially useful for troubleshooting.

Resource-listResource nodes. Here are a couple of the resource node items that you'll find useful in troubleshooting deadlock problems:

Learning More

As I mentioned, troubleshooting deadlocks can be a complex problem. It's beyond the scope of this article to cover the fundamentals of locking and deadlocking that must be understood in order to fix deadlocks. My focus here is to introduce you to a tool that simplifies the collection of the necessary information. If you would like to improve your understanding of the issues involved in troubleshooting deadlocks, Microsoft has good articles available on the subject. For instance, try "Deadlock Troubleshooting, Part 1" from Bart Duncan's SQL Weblog or "Troubleshooting Deadlocks in SQL2005" from SQL Server Storage Engine.

Fixing deadlocks is never easy, but Deadlock graph makes it much easier than it used to be. To read more about Deadlock graph, check out the Microsoft article "Analyzing Deadlocks with SQL Server Profiler."