Introduction

In my previous 2 articles, What is a SQL Server Deadlock and Understanding the graphical representation of the SQL Server Deadlock Graph, I discussed what a deadlock is, how SQL Server handles deadlocks, some tips on how to reduce deadlocks and ultimately what information you can glean by just looking at the graphical representation of the Deadlock Graph. In this article, I will look at what information is available in the XML description of the Deadlock Graph. The XML contains information which is not visible in the graphical representation of the Deadlock Graph, which makes it necessary for us to look at the XML description.

Extracting the XML Description from the Deadlock Graph

Depending on how you captured your Deadlock Graph you have multiple ways of viewing the XML text.

1. Open the same .xdl file in a text viewer such as notepad Figure 1: Deadlock XML in notepad
2. Change the file extension from .xdl to .xml and open it in SSMS Figure 2: Deadlock Graph as XML in SSMS
3. If you extracted your deadlock using Extended Events you can double click on the xml_report line in the event details which will open the xml description as an .xml file in SQL Server Management Studio Figure 3: Double click to get XML report

Understanding the XML

The Deadlock XML consists out of 3 sections:

• The victim list
• The process list and
• The Resource list

Figure 4: Deadlock XML sections We will now look at each of these in detail.

The victim list

The victim list is exactly what it says it’s a list of process ids which have been selected to be the victims of the deadlock. In this particular instance there is only one victim, but it is possible to have multiple victims involved in a deadlock. Figure 5: The victim list

The process list

The process list contains one node for each process involved in the deadlock. The following details are displayed for each process per MSDN:

Id	The process id of the specific process
Task priority	This is the deadlock priority. Typical values are LOW : -5, NORMAL : 0 and , HIGH: 5
Log used	The amount of transaction log used by the process
Waitresource	The waitresource has a format like this: KEY: 20:72057594045595648 (8194443284 a0) The first part: KEY indicates the resource type. In this case a key. The second part: 20 is the database id The third part: 72057594045595648 is the Heap or B tree ID The part in brackets is the hash value for the index key
Waittime	The amount of time the process waited for the resource to become available
Ownerid	This displays the transaction id of the transaction which owns the process. This value corresponds to the request_owner_id field in the sys.dm_tran_locks DMV.
Input buffer	This describes the event type and the code that is executed. In other words the SQL script that is being executed will be shown here.
Statement	The statement type such as: SELECT UPDATE INSERT Delete NOP (No Operation)

If you look at the XML itself, you will notice a couple of other details not mentioned in MSDN. It seems MSDN is referring to the details available only in the graphical representation of the deadlock graph. Which is why we need to look at the XML. Additional fields include:

transactionname	The name of the transaction or the transaction type: I.e. user_transaction
Lasttranstarted	The date time the last transaction started
XDES	Transaction description structures
LockMode	The type of lock the process wants to take
Schedulerid	The ID of the processor on which the process is running
Kpid	The Windows thread ID
Status	The status of the process. I.e.: Suspended Dormant Running Background Rollback Pending Runnable Spinloop
Spid	The SQL Process ID
Sbid	The batch ID
Ecid	This is the execution context when the process is executed in parallel. If not executed in parallel this value will be 0.
Priority	The same as the deadlock task priority
Trancount	The number of transactions involved in the deadlock
lastbatchstarted	Date time the last batch was started
lastbatchcompleted	Date time the last batch completed
lastattention	The timestamp of the processing of the last attention event such as a timeout or a cancellation on the thread involved in the deadlock.
Clientapp	The client application that created the process
Hostname	The host name on which the client is running
Hostpid	The process ID of the client application
Loginname	Name of the logged in user
Isolationlevel	The isolation level of the process
Xactid	The transaction ID of the process
Currentdb	Database ID of the database on which the process was run
lockTimeout	The maximum time a process can wait for a lock to be released
Clientoption1	These are SET options such as ANSI_NULLS or QUOTED_IDENTIFIER
Clientoption2	More SET options

The main thing that I find of interest in the process list which you cannot see on the graphical view of the graph is the isolation level of the process. For example: If the isolation level is serializable then that could very well be the reason for the deadlock. You then have to go back and determine if this isolation level is required and if so, review all queries involved to ensure that they are always accessing tables in the same order. Figure 6: The Process List

The resource list

The resource list provides detailed information about each resource involved in the deadlock. A resource can be a row, a key, a page, an extent, a heap or a B-tree (HoBT) , a table , a file, an allocation unit or metadata. In this example, the deadlock involved Keys or rather indexes. The following information is available in the resource list:

HoBTId	The heap or B-tree ID
dbID	The database id on which the resource exists
objectName	The name of the resource
indexname	This is the ID or name of the index involved in the deadlock
Id	The id of the key lock
Mode	The lock mode
associatedObjectId	The id of the object on which deadlock occurred, mostly the same as the HoBTID.
Owner-list	Information about the owner of the process and corresponding lock mode
Waiter-list	Information on the process waiting on the resource, lock type requested and the request type.

A resource node will be included for each resource involved in the deadlock. Figure 7: The resource list

Conclusion

The deadlock graph is the easiest way to get information about a deadlock. A lot of information can be seen simply by looking at the graph, but usually more in depth information is required to be able to resolve the deadlock, such as the types of locks involved, the isolation level used etc. The XML description of the deadlock provides a ton of additional information which makes troubleshooting deadlocks a lot easier. So instead of just using the deadlock graph, be sure to also look at the XML description of the graph.

The previous articles in this series

• What is a SQL Server deadlock?
• Understanding the graphical representation of the SQL Server Deadlock Graph