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 = 'Bob'
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 = 'Greg' </frame>
</executionStack>
<inputbuf>
update employees set firstname = 'Greg' </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.