MSSQL - How to Trace Deadlock Using SQL Profiler

Introduction

This tutorial provides a step-by-step guide on how to trace deadlocks using SQL Server Profiler. Deadlocks occur when two or more transactions are waiting for each other to release resources, leading to a standstill. By following these steps, you'll learn how to set up SQL Server to capture deadlock events and analyze them for troubleshooting.

Step 1: Enable Deadlock Tracing

To start tracing deadlocks, you need to enable the deadlock tracing feature in SQL Server. Use the following command to activate it:

DBCC TRACEON (1222, -1)

This command enables the logging of deadlock information in the SQL Server error log.

Step 2: Create Necessary Tables

For the purpose of testing deadlocks, create two tables named ParentTable and ChildTable. Use the following SQL commands:

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ParentTable')
    DROP TABLE [ParentTable]

CREATE TABLE [ParentTable] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] varchar(100) NULL,
    [Value] varchar(100) NULL,
    [DateChanged] datetime DEFAULT(GETDATE()) NULL,
    CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED ([Id] ASC)
)

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ChildTable')
    DROP TABLE [ChildTable]

CREATE TABLE [ChildTable] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] varchar(100) NULL,
    [Value] varchar(100) NULL,
    [DateChanged] datetime DEFAULT(GETDATE()) NULL,
    CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED ([Id] ASC)
)

Step 3: Insert Test Data

Insert sample data into both tables to simulate a deadlock scenario. Use the following commands:

INSERT INTO [ParentTable](Name, Value)
SELECT 'Name1', 'Value1' UNION ALL
SELECT 'Name2', 'Value2' UNION ALL
SELECT 'Name3', 'Value3'

INSERT INTO [ChildTable](Name, Value)
SELECT 'Name1', 'Value1' UNION ALL
SELECT 'Name2', 'Value2' UNION ALL
SELECT 'Name3', 'Value3'

Step 4: Simulate Deadlock Scenario

To create a deadlock, you will run two separate transactions in different sessions.

Session 1

In the first session, set the deadlock priority and execute the following commands:

SET DEADLOCK_PRIORITY LOW
BEGIN TRAN
UPDATE ParentTable SET Name = Name + Name WHERE ID = 2
WAITFOR DELAY '00:00:10'
UPDATE ParentTable SET Name = Name + Name WHERE ID = 1
COMMIT TRAN

Session 2

In the second session, set the deadlock priority and run:

SET DEADLOCK_PRIORITY NORMAL
BEGIN TRAN
UPDATE ParentTable SET Name = Name + Name WHERE ID = 1
WAITFOR DELAY '00:00:10'
UPDATE ParentTable SET Name = Name + Name WHERE ID = 2
COMMIT TRAN

Step 5: Capture Deadlock Information

Once the deadlock occurs, SQL Server will log the deadlock information. You can capture this data using the following SQL query:

SELECT 
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), DeadlockEventXML.value('(event/@timestamp)[1]', 'datetime2')) AS [EventTime],
    DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@hostname)[1]', 'nvarchar(max)') AS HostName,
    DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@clientapp)[1]', 'nvarchar(max)') AS ClientApp,
    DB_NAME(DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@currentdb)[1]', 'nvarchar(max)')) AS [DatabaseName],
    DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@transactionname)[1]', 'nvarchar(max)') AS VictimTransactionName,
    DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@isolationlevel)[1]', 'nvarchar(max)') AS IsolationLevel,
    DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadLockGraph,
    DeadlockEventXML 
FROM (
    SELECT 
        XEvent.query('.') AS DeadlockEventXML,
        Data.TargetData 
    FROM (
        SELECT 
            CAST(target_data AS XML) AS TargetData 
        FROM sys.dm_xe_session_targets st 
        JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address 
        WHERE s.name = 'system_health' 
        AND st.target_name = 'ring_buffer'
    ) AS Data 
    CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS DeadlockInfo

Conclusion

You have successfully traced deadlocks using SQL Server Profiler by enabling deadlock tracing, creating necessary tables, inserting test data, simulating a deadlock scenario, and capturing deadlock information. This process can help you troubleshoot and resolve deadlocks in your SQL Server databases. For further analysis, consider reviewing the captured deadlock graphs to identify the root causes and optimize your transactions accordingly.