How to capture deadlocks using SQL Server Profiler:

How to capture deadlocks using SQL Server Profiler:

To capture a deadlock, first connect to a SQL Server database. To open the SQL Profiler in SQL Server Management Studio:

• Click on Tools
• Click on SQL Server Profiler
• Connect to the server on which we need to perform profiling
• On the Trace Properties window, under General tab, select the blank template
• On the Events Selection tab, select Deadlock graph under Locks leaf

Let’s execute the below queries:

In the following order:

1. Execute the query in the blue rectangle
2. Execute the query in green rectangle
3. Execute the query highlighted in grey
4. Execute the query highlighted in blue

Notice that the process ids are shown on the information bar in SQL Server:

The dead lock will be generated on execution of the 4th step:

For later analysis, this can be saved from File -> Export -> Extract SQL Server Events -> Extract Deadlock Events…

Analyzing the deadlock graph:

1. Oval with a blue cross represents the transaction/process that was chosen as the deadlock victim by SQL Server
2. The ovals represents the processes, the one without cross represents the transaction/process which completed successfully
3. The deadlock priority is set to default i.e. 0
4. We also have log used, this represents the transactional log used. If the transaction has done a lot of updates, the log size will be larger. Hence to roll the a transaction which has done a large number of updates would take a lot of cost. In our case, the deadlock victim is the one with less transaction log, because that would take less cost. The rectangles represents the resource nodes.
5. The rectangles represents the resource nodes.
6. The HoBtID (heap or binary tree id) associated with the resource node is used to find the database object involved in the deadlock from sys.partitions view by using the following query:

SELECT object_name([object_id]) from sys.partitions 
WHERE hobt_id = xxxxxx

1. The arrows represent the types of locks we have on each resource node
2. The notations X and S on the arrows represents the exclusive and shared locks

Thanks to original publisher -xameeramir.github.io