One of the nightmares of a developer is the deadlock. The main source of a deadlock is the backend code, SQL Server scripts. In this article, we will discuss about how to use the SQL Profiler to capture the deadlocks in SQL Statements.

Introduction

One of the nightmares of a developer is the deadlock. The main source of a deadlock is the backend code, SQL Server scripts. In this article, we will discuss about how to use the SQL Profiler to capture the deadlocks in SQL Statements.

Deadlock

Deadlock is nothing but the situation where one request is waiting for another resource to complete the process, mean time the requested resource is with another request which is waiting for some resources with the first request. Here, both the requests waiting for the completion of the other one. After a specified time, the system will kill one request and process the second request.

SQL Profiler – Deadlock Graph

In this article, we will look into one of the tool available with the SQL Server for detecting or capturing the deadlock caused by SQL statement. SQL Profiler is mainly used to track the stored procedure calls and for finding the time, reads and writes taken by a particular stored procedure or SQL statement.

Profiler is having the option to capture the deadlocks occurring in the current run and it will display the deadlock in a graphical format as well as XML format. From the graphical representation, we can easily identify the statements caused the deadlock and which statement is the victim of the same.

Trace Setting

First, setup the SQL Profiler to capture the deadlock graph. Open the SQL Profiler, New Trace. Move to the Event Selection tab in Trace Properties Window. Select the Show all events Checkbox on the right bottom side. This will show all the event categories available with the profiler.

Expand the Locks events category and select the Deadlock Graph option. This will add a new tab to the Trace Window – Events Extraction Settings.

Move to the Events Extraction Settings and select the folder where we want to save the deadlock graphs. Specify a name for the graph. Also, select whether we need to save all the deadlocks as a single file or want to save each deadlock in separate files option.

Create Deadlock

Open two query windows in SQL Server Management studio for NorthWind database. Run the following commands from the first window

begintran

update products set supplierid = 2

Run the following statements from second query window

begintran

update employees set firstname ='Bob'

update products set supplierid = 1

Again, run the following statement from first query window

update employees set firstname ='Greg’

Now observe that we got a deadlock graph file. Open the file in SQL Server Management studio and see the dead graph indicating the deadlock victim as the statements executed in the second query window.

We can open the deadlock file in any XML editor. The XML Code corresponding to the above deadlock graph is shown below. From the XML code, we can understand which statement is terminated as deadlock victim and which all statements involved in this deadlock and if it is in a procedure, the name of procedures involved, etc.

Conclusion

SQL Profiler is a tool installed as part of SQL Server and used for analyzing different performance parameters of SQL statements. The same can be used for tracking the deadlocks occurrences in SQL statements.

About the Author

Full Name: Ambily KKMember Level: Silver Member Status: Member,Microsoft_MVP,MVPMember Since: 5/18/2010 1:05:25 AMCountry: India Thanks
Ambily K K
http://ambilykk.com/ http://ambilykk.com/
I have over 9 years of experience working on Microsoft Technologies. I am carrying the passion on Microsoft technologies specifically on web technologies such as ASP .Net and Ajax. My interests also include Office Open XML, Azure, Visual Studio 2010. Technology adoption and learning is my key strength and technology sharing is my passion.