Capturing Deadlocks using SQL Profiler

Back to: SQL Server Tutorial

Capturing Deadlocks using SQL Profiler

In this article, I am going to discuss Capturing Deadlocks using SQL Profiler i.e. how to capture deadlock using SQL Profiler. Please read our previous article where we discussed SQL Server Deadlock Analysis and Prevention in detail.

How to capture deadlock graph using SQL Profiler?

To capture the deadlock graph, what we need to do is add the Deadlock graph event to the trace in SQL Profiler. To do this please following steps.

Open the SQL Profiler. To open SQL Profiler, go to the Tools options and select SQL Profiler from SQL Server Management Studio and it will open a window asking for the credentials to connect to the server. Provide the credentials to connect to the server as shown in the below image.

Once you click on the Connect button it will take you to the SQL Profiler Trace Properties window. From that window select the General tab and then select the “Blank” template from the “Use the template” dropdown list as shown in the image below.

Then Select the “Events Selection” tab, and expand the “Locks” section and select “Deadlock graph” event and then click on the Run button as shown in the image below.

Now we need to execute the code that causes deadlock.

Modify the following two stored procedure as shown below so that it causes deadlock.

ALTER PROCEDURE spTransaction1
AS
BEGIN
    BEGIN TRANSACTION
    UPDATE TableA SET Name = 'Anurag From Transaction 1' 
  WHERE Id = 101

    WAITFOR DELAY '00:00:10'

    UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
  WHERE Id = 1001
    COMMIT TRANSACTION
END

ALTER PROCEDURE spTransaction2
AS
BEGIN
    BEGIN TRANSACTION
  UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
  WHERE Id = 1001
    
    WAITFOR DELAY '00:00:10'
  
  UPDATE TableA SET Name = 'Anurag From Transaction 2' 
  WHERE Id = 101
    COMMIT TRANSACTION
END

Now open two instances of SQL Server Management Studio and From Instance1 execute spTransaction1 stored procedure and from instance2 execute the spTransaction2 stored procedure. Once you execute the above two procedures then the deadlock graph should be captured in the profiler as shown below.

The data of the deadlock graph is captured in the form of XML. If you want to extract the XML data to a physical file for later analysis, you can do it very easily by following the below steps. In SQL profiler click on the “File – Export – Extract SQL Server Events – Extract Deadlock Events” as shown in the below image

Then provide a name for the file. The extension for the deadlock XML file is .xdl

Once you save that file, then open that file using notepad and you will see that the deadlock information in the XML file is similar to what we have captured using the trace flag 1222 that we discussed in our previous article.

Let us understand the deadlock graph

The oval with the blue cross on the graph represents the transaction that was chosen by the SQL Server as the deadlock victim whereas the other oval on the graph represents the transaction that was completed successfully.

When you move the mouse pointer over the oval with blue cross, you can see that the SQL code that was running that caused the deadlock as shown in the below image.

In the next article, I am going to discuss how to handle the Deadlock in SQL Server using TRY/CATCH implementation and how to implement the Retry Logic to rerun the transaction. Here, In this article, I try to explain Capturing Deadlocks using SQL Profiler. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.