Back to: SQL Server Tutorial For Beginners and Professionals
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.
1 thought on “Capturing Deadlocks using SQL Profiler”
Thanks a lot