SQL Profiler - DeadLock Graph

Ambily.raj
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 18725 red flag
Rating: 5 out of 5  
 2 vote(s)

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

For capturing the deadlock graph, I am using the NorthWind database and few update statements.  For creating the deadlock, I used the statements from http://www.mssqltips.com/tip.asp?tip=1036

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

begin tran

update products set supplierid = 2

Run the following statements from second query window

begin tran

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.

<deadlock-list>

 <deadlock victim="process80969048">

  <process-list>

   <process id="process80969048" taskpriority="0" logused="1968" waitresource="KEY: 8:289180401860608 (010086470766)" waittime="7132" ownerId="289327" transactionname="user_transaction" lasttranstarted="2010-12-08T16:35:17.643" XDES="0x81117970" lockMode="U" schedulerid="2" kpid="4704" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-12-08T16:35:17.583" lastbatchcompleted="2010-12-08T16:33:13.810" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LABCLUST24" hostpid="7968" loginname="FAREAST\v-amk" isolationlevel="read committed (2)" xactid="289327" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

    <executionStack>

     <frame procname="adhoc" line="3" stmtstart="108" sqlhandle="0x0200000050877022ebb43bd41412d6078ec93299797b7a65">

     </frame>

     <frame procname="adhoc" line="3" stmtstart="108" sqlhandle="0x02000000df774d151bc3879e0b2e0100503bbbe5fe11ffc3">

update products set supplierid = 1     </frame>

    </executionStack>

    <inputbuf>

begin tran

update employees set firstname = &apos;Bob&apos;

update products set supplierid = 1

    </inputbuf>

   </process>

   <process id="process80968bc8" taskpriority="0" logused="40720" waitresource="KEY: 8:411043454976000 (03000d8f0ecc)" waittime="3558" ownerId="289319" transactionname="user_transaction" lasttranstarted="2010-12-08T16:35:16.960" XDES="0x81c55970" lockMode="U" schedulerid="2" kpid="2476" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-12-08T16:35:21.280" lastbatchcompleted="2010-12-08T16:35:17.033" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LABCLUST24" hostpid="7968" loginname="FAREAST\v-amk" isolationlevel="read committed (2)" xactid="289319" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">

    <executionStack>

     <frame procname="adhoc" line="1" stmtstart="36" sqlhandle="0x020000009889e5073492f278180b42312796b34665be75dc">

UPDATE [employees] set [firstname] = @1     </frame>

     <frame procname="adhoc" line="1" sqlhandle="0x02000000cc00eb176bbab93d703b023de85137b27f0d665e">

update employees set firstname = &apos;Greg&apos;     </frame>

    </executionStack>

    <inputbuf>

update employees set firstname = &apos;Greg&apos;     </inputbuf>

   </process>

  </process-list>

  <resource-list>

   <keylock hobtid="289180401860608" dbid="8" objectname="Northwind.dbo.Products" indexname="PK_Products" id="lock81be0e80" mode="X" associatedObjectId="289180401860608">

    <owner-list>

     <owner id="process80968bc8" mode="X"/>

    </owner-list>

    <waiter-list>

     <waiter id="process80969048" mode="U" requestType="wait"/>

    </waiter-list>

   </keylock>

   <keylock hobtid="411043454976000" dbid="8" objectname="Northwind.dbo.Employees" indexname="PK_Employees" id="lock842d4600" mode="X" associatedObjectId="411043454976000">

    <owner-list>

     <owner id="process80969048" mode="X"/>

    </owner-list>

    <waiter-list>

     <waiter id="process80968bc8" mode="U" requestType="wait"/>

    </waiter-list>

   </keylock>

  </resource-list>

 </deadlock>

</deadlock-list>

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.  

 

Page copy protected against web site content infringement by Copyscape

About the Author

Ambily.raj
Full Name: Ambily KK
Member Level: Silver
Member Status: Member,Microsoft_MVP,MVP
Member Since: 5/18/2010 1:05:25 AM
Country: 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.

Login to vote for this post.

Comments or Responses

Posted by: PandianS on: 12/9/2010 | Points: 25
Hi

Nice Explanation...

Cheers
Posted by: Raja on: 12/9/2010 | Points: 25
Yup, very good explanations. 5 from my side Ambily. Keep it up!

Cheers
Posted by: Ambily.raj on: 12/9/2010 | Points: 25
Thanks

Ambily
Posted by: Eswarreddy on: 1/9/2011 | Points: 25
Good One. Have 5 from me.
Posted by: Lakhwinder.Ghuman on: 1/14/2013 | Points: 25
Good Article.

Login to post response

Comment using Facebook(Author doesn't get notification)