Tracing deadlock with sql profiler

Hi folks, In this article “Trace deadlock sql profiler”, we are going to learn, how we can capture a deadlock in a graphical form in sql server using an inbuilt tool of sql server named “SQL Server Profiler”.

First of all note that, don’t try to reproduce this sample deadlock and start SQL Server Profiler trace on your production server unless you are well versed of Profiler and event tracing. I recommend to do all these stuffs on you local machine to learn capturing deadlocks using SQL Server Profiler.

We are going to cover below points in this article;

1. What is a deadlock?
2. How to create a demo purpose deadlock?
3. Using SQL Server Profiler to capture deadlock details and graphs.
4. Understanding the deadlock important events in sql server profiler.

What is a deadlock?

A short note on deadlock:

Deadlock occurs when two sessions are waiting for a lock to clear on the other while holding it’s own resources. If session “A” has a lock on resource “R1” and waiting for resource “R2” and another session “B” has a lock on resource “R2” and waiting for resource “R1”, this circular chain will generate the deadlock situation in database. This is a permanent blocking situation and would not be resolve by waiting. SQL server is capable to detect deadlocks and can declare one of the process as deadlock victim and kills that process. The process which declared as deadlock victim is the one which is less resources intensive and has less cost than another process.

To understand this in more detail, click this Lock vs Block vs Deadlock

How to create a demo purpose deadlock?

To create a deadlock, we have to follow the steps as discussed in it’s definition. We are going to create two demo tables to create this situation and then we will execute two transactions simultaneously.

In transaction 1, we have an UPDATE command on table 1 and then with holding lock on table 1, i am requesting another UPDATE (Lock request for update) on table 2. And in transaction 2, we have an UPDATE command on table 2 and with holding lock on table 2, i am requesting another UPDATE (Lock request for update) on table 1. The locks are not compatible and will create a deadlock. So now we have a circular chain with permanent blocking.

To produce a deadlock, create tables as below;

CREATE TABLE tbl_Test1
(
Col VARCHAR(100)
)
GO

CREATE TABLE tbl_Test2
(
Col VARCHAR(100)
)
GO

Insert these demo values;

INSERT INTO tbl_Test1(Col)
VALUES(1),
(2)

GO

INSERT INTO tbl_Test2(Col)
VALUES(1),
(2)

Open a new query editor window and run this below transaction;

Transaction 1

BEGIN TRAN
UPDATE tbl_Test1 SET Col = 1 WHERE Col = 1
WAITFOR DELAY '00:00:05'
UPDATE tbl_Test2 SET Col = 1 WHERE Col = 1
COMMIT TRAN

Also open another query window immediately (within 5 seconds) and execute transaction;

Transaction 2

BEGIN TRAN
UPDATE tbl_Test2 SET Col = 1 WHERE Col = 1
WAITFOR DELAY '00:00:05'
UPDATE tbl_Test1 SET Col = 1 WHERE Col = 1
COMMIT TRAN

As and when you will run this query just after a few seconds, you will get below message;

(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 4
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Using SQL Server Profiler to capture deadlock details and graphs

Now we have to capture the detail of this deadlock using SQL Server profiler. Before this just try to understand that what is a Profiler.

SQL Server Profiler is a tool to create and manage traces and analyze the captured results. We can save these events capturing details in a trace file that can be analyzed and / or used later to generate a specific series of steps when trying to diagnose a problem. Using profiler GUI, we can trace internal activity in SQL Server. We can capture all or only those events in which we are interested.

Remember that you are using your local machine and not duplicating this scenario or planning to use Profiler on your production Server unless you are well versed of profiler and traces.

Start SQL Server Profiler –

Open SSMS (SQL Server Management Studio) and

Go to Tools -> SQL Server Profiler

“Connect to Server” dialog box will appear. Put the appropriate credentials and click on “Connect”.

Trace Properties window will appear as below. Put the trace name and select Template type “TSQL_Locks”.

Now go to “Events Selection” tab on top left corner in Trace Properties dialog box and select the below events;

Now click on “Run” button at bottom.

SQL Server Profiler has started to capture the events selected above.

After clicking “Run” button, create an artificial deadlock as you have learn in section “How to create a demo purpose deadlock?”.

And have a look on the output of Profiler. In this figure (Figure 3), i have selected the deadlock graph. You can also select and check other Event Class in profiler trace result.

In graph, we can easily see that, two processes 57 and 58 have been deadlocked and process 57 has been declared as victim of deadlock and killed. We can also see that the object name is also captured in this graph. Other details like DBID, FileID, PageID, associated object id are also captured in this graph.

To get more information, just hoover your mouse over any circle and you can get the details of SQL statement as below;

You can check both the statements and take appropriate action. Now in next section we are going to understand the meanings of events which have been captured in our trace.

Understanding the deadlock important events in sql server profiler

These are the events captured in our trace:

1. Deadlock graph
2. Lock : Deadlock
3. Lock : Deadlock Chain
4. SP: StmtCompleted
5. SP: StmtStarting
6. SQL: StmtCompleted
7. SQL : StmtStarting

Deadlock graph : This is the only main event which i should capture to get all the details for the deadlock. The Deadlock Graph event class provides an XML as well as graphical description of the deadlock.

Lock : Deadlock : This event Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources that the other transaction owns. This event get fired when a deadlock occurs. You can leave this event in your trace.

Lock : Deadlock Chain : This event will capture each of the events leading up to the deadlock. So in case more than two processes are causing the deadlock, each will fire this event.

SP: StmtCompleted : When a stored procedure execution gets completed. It indicates that a Transact-SQL statement within a stored procedure has completed.

SP: StmtStarting : Fired when stored procedure is started and indicates that a Transact-SQL statement within a stored procedure has started.

SQL: StmtCompleted : Occurs when the Transact-SQL statement has completed.

SQL : StmtStarting : Occurs when the Transact-SQL statement is starting.

Conclusion

So hope you have enjoyed the topic “Trace deadlock sql profiler”.  You can produce to understand the details captured in deadlock graph on your local machine. There are some other ways to produce capture such type of details also. Like Trace Flag and Extended Events. We can also capture this detail in XML using profiler tool.

DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)

Above commands can be used to capture the deadlock details in SQL Server Error Log.

1204 – Provides details of nodes involved in the deadlock
1222 – Produce deadlock information in XML format

In our real life scenario, it’s not possible to run this tool for a day or more to capture the deadlock being occurred on our production server. Profiler is resource intensive and will put extra load on server and can make it very slow. Extended events are preferable in comparison of profiler on production server to capture such types of event tracing.

Now at the end, i appreciate  your patience for reading this post and also request you to do share this on your social media. Kindly put your feedback in comments and don’t forget to rate this post.

Thanks and keep reading!