Troubleshoot Deadlocks using SQL Server Profiler 2005/2008

Troubleshoot Deadlocks using SQL Server Profiler 2005/2008

In this article, we will see how to capture deadlocks while modifying data using SQL Server Profiler in SQL Server 2005/2008.

What are Deadlocks? Deadlocks occur when two processes are holding an exclusive lock by mutually blocking each other. It can also occur if the same rows in the same table are accessed in a different sequence by two processors.

When a deadlock is detected by SQL Server, it will choose one of the processes as a ‘Victim’, abort and rollback the process. Deadlocks can be reduced by designing the applications carefully and by following good indexing practices. The best way to prevent a deadlock is to keep the transactions as short as possible. SQL Server allows you to save deadlock graphs in XML. SQL Server profiler will allow you to read the deadlock XML file and display it graphically.

So let’s see the how to use the SQL Profiler. Let’s start by opening a SQL Profiler. To open the SQL Profiler, go to SQL Server 2008 > Performance Tools > SQL Server Profiler as shown below –

Once you open the profiler, it will ask you to connect to the server. Click on ‘New Trace’ button and Connect to your SQL Server instance as shown below –



Once you connect to the server, you will see the ‘Trace Properties’ window. In the first tab ‘General’, call the Trace name as ‘CheckDeadlocks’. Then choose a template ‘Blank’. Check the checkbox ‘Save to File’ and save the file in a preferred location. I am saving it in the ‘Documents’ folder as shown below –



Now let’s click on the ‘Event Selection’ tab and expand the ‘Locks’ category. Choose the following events –

1. Deadlock Graph
2. Lock: Deadlock Chain

Now expand the ‘Stored Procedure’ Category and choose the following events –

1. RPC: Starting
2. SP: Stmt Starting
3. SP: Stmt Completed
4. RPC: Completed

Now let’s scroll to the right to find the ‘DatabaseID’ column. Right click the column and click on ‘Edit Column Filters’ as shown below –



Expand the ‘Not Equal To’ node and type ‘4’ number as shown below –

Click the ‘OK’ button. This will filter out all the activities in ‘msdb’ database. Now click on ‘Events Extraction Settings’ tab and check the checkbox ‘Save Deadlock XML events separately’ and save the file in a preferred location. The file will be saved with the extension ‘.xdl’. Name the file ‘DeadlockXMLEvents’. Now choose ‘Each Deadlock XML batch in a distinct file’ option and click on the ‘Run’ button.

Now let’s open SQL Server Management Studio and write a new query. Write the following query which will create two tables in our Northwind Database –

Once your tables are created, let’s open a New Query window and write the following transaction statement –

TRANSACTION 1

USE Northwind
GO
BEGIN TRAN
    --First Query
    UPDATE CopyCustomer SET ContactTitle='Owner' WHERE
    CustomerID='ALFKI'
   
    --Second Query
    UPDATE CopyOrders SET OrderDate=GETDATE() WHERE
    CustomerID='ALFKI'

Now let’s create another Query Window and start a new transaction statement as shown below –

TRANSACTION 2

USE Northwind
GO
BEGIN TRAN
    --First Query
    UPDATE CopyOrders SET OrderDate=GETDATE() WHERE
    CustomerID='ANATR'
   
    --Second Query
    UPDATE CopyCustomer SET ContactTitle='Sales Representative'
    WHERE CustomerID='ANATR'

If you observe TRANSACTION 1 statement, the T-SQL code is trying to change the contact title of customer ‘ALFKI’. Similarly in TRANSACTION 2, the query (written in a new query window) is trying to change the OrderDate of customer ‘ANATR’.

Let’s execute TRANSACTION 1 (first query) and then execute the TRANSACTION 2 (first query) respectively. Go back to TRANSACTION 1 and execute the second query which is trying to update Orderdate of customer ‘ALFKI’ and observe what happens! This query will not get executed.

Now go back to TRANSACTION 2 and execute he second query which is updating ContactTitle of customer ‘ANATR’ and see what happens! This query will get executed after some time. But how did this happen! Let’s go back to the first transaction query windows and observe the message. The message is as follows –

This above scenario is a ‘TRUE DEADLOCK’. If you observe TRANSACTION 1 ,  has become a deadlock victim and got roll back. Now let’s go back to our SQL Server Profiler and see the graphical view of this deadlock. Click on the ‘Stop’ button.

Go to the path where we have saved the ‘.xdl’ file and open that file. It will look similar to the following (image divided in two screenshots) –




On the same topic, make sure you read these posts too: