SQL Server – SSMS – Profiler – Extracting Deadlock Event Data to XDL files


A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. We can use the SQL Server Profiler to capture deadlock information. Today, we will see how to use the SQL Server Profiler to capture deadlock information, and also how to export this information for distribution to the development team.

Setting up the Environment

First of all, let’s create a scenario that would create the necessary test data for us to generate a deadlock. In the query below, you will find 3 sets of transaction-bound queries, which need to be executed in 3 different SSMS windows – all connected to the same SQL Server instance.

/*
WARNING: This script is provided "as-is" and without warranty.
         Please do not use this in quality assurance and/or production environments.
*/

-- Step 01: To be run in query window #1
USE tempdb
GO

CREATE TABLE DeadlockTestTable1 (DL1Id   INT, DL1Name VARCHAR(20))
CREATE TABLE DeadlockTestTable2 (DL2Id   INT, DL2Name VARCHAR(20))

INSERT INTO DeadlockTestTable1 VALUES (1, 'Deadlock'), (2, 'SQL Server'), (3, 'Test')
INSERT INTO DeadlockTestTable2 VALUES (1, 'Deadlock'), (2, 'SQL Server'), (3, 'Test')

-- Step 02: To be run in query window #1
USE tempdb
GO
BEGIN TRANSACTION 

UPDATE DeadlockTestTable1 SET DL1Name = 'Uplock' WHERE DL1Id = 2
WAITFOR DELAY '00:00:20'
UPDATE DeadlockTestTable2 SET DL2Name = 'Downlock' WHERE DL2Id = 2

ROLLBACK TRANSACTION

-- Step 03: To be run in query window #2
USE tempdb
GO
BEGIN TRANSACTION 

UPDATE DeadlockTestTable2 SET DL2Name = 'Downlock' WHERE DL2Id = 2
WAITFOR DELAY '00:00:20'
UPDATE DeadlockTestTable1 SET DL1Name = 'Uplock' WHERE DL1Id = 2

ROLLBACK TRANSACTION

-- Step 04: To be run in query window #3
USE tempdb
GO
BEGIN TRANSACTION 

UPDATE DeadlockTestTable2 SET DL2Name = 'Uplock' WHERE DL2Id = 2
WAITFOR DELAY '00:00:10'
UPDATE DeadlockTestTable1 SET DL1Name = 'Downlock' WHERE DL1Id = 2

ROLLBACK TRANSACTION

Using the Profiler to Capture & Export the Deadlock trace

In this section, we will see how to use the SQL Server profiler to capture the deadlock trace.

  1. Launch the SQL Server profiler and connect to the desired SQL Server instance
  2. Start a new trace
  3. Under Event selection, clear out any default events. Only keep the Locks –> “deadlock graph” event class as checked. You may also have “Lock:Deadlock” and “Lock:Deadlock chain” as checked
  4. image
  5. Once the trace is running, in SSMS Query window #1, run the query intended for window #1
  6. In SSMS Query window #2, run the query intended for window #2. Repeat this process for query #3
  7. Within 20 seconds, two of the 3 queries would have failed execution because they would have been chosen as the deadlock victims
  8. The Profiler trace would look like the following:
  9. image
  10. One can analyze the deadlock graph here itself. In case this deadlock needs to be sent over to the development team, one can follow one of the many steps highlighted below

Export Deadlock Data – Export Selected deadlock information only

If only a selected deadlock needs to be forwarded to the development team for analysis, then, simply right-click on the deadlock and choose “Extract Event Data”. Deadlock graph information can then be saved to any desired location of your choice. The deadlock graph information is stored in the form of SQL Server Deadlock XML files (*.xdl). As the name indicates, it is ultimately an XML file containing the XML representation of the deadlock graph.

image

Export Deadlock Data – Export all deadlock information

If you need to extract all deadlock graphs from a SQL Server profiler trace, go to File->Export->Extract SQL Server events->Extract Deadlock events…

image

Export this information to a location of your choice in the form of SQL Server Deadlock XML files (*.xdl). You may choose to save all events in a single file, or have separate files for each deadlock event.

image

Opening the trace in SQL Server Management Studio

Now that the deadlock information has been exported to the file system, you can now E-mail these files over to the development team who can in-turn analyze the deadlock graphs for you, and arrive at a resolution to the deadlock issue.

To open these files, one does not need to use the SQL Profiler. The Management Studio (SSMS) is capable of opening the XDL files and representing the information as a deadlock graph.

image

image

As you can see from the image above, hovering the mouse over the deadlock victim also reveals the affected query. You may also open the XDL file in an XML editor of your choice, if you would like to see the deadlock information in the XML format.

I hope that the above method will help you quickly & efficiently exchange deadlock data & related analysis with your development team.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Advertisements

6 thoughts on “SQL Server – SSMS – Profiler – Extracting Deadlock Event Data to XDL files

  1. vanne040

    Well explained post…I always wanted to see posts on SQL profiler that detail how to use it…Can anyone point me to good posts on how to use Profiler for performance issues?

    Like

    Reply
  2. Nakul Vachhrajani

    @vanne040: You got it. I will write up a couple of posts to get you started, and point you to other posts which I may encounter during my research on the topic. Stay tuned – I will post the URL of the 1st part of the series on this post.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s