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.
- Launch the SQL Server profiler and connect to the desired SQL Server instance
- Start a new trace
- 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
- Once the trace is running, in SSMS Query window #1, run the query intended for window #1
- In SSMS Query window #2, run the query intended for window #2. Repeat this process for query #3
- Within 20 seconds, two of the 3 queries would have failed execution because they would have been chosen as the deadlock victims
- The Profiler trace would look like the following:
- 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.
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…
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.
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.
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!
SQLTwins by Nakul Vachhrajani 