Understanding, Analysing and Troubleshooting Deadlocks – Part2

In First Part Understanding, Analysing and Troubleshooting Deadlocks – Part1 we discussed what is Deadlock, How SQL Server engine behaves when a deadlock is encountered, how a victim is identified and why deadlocks occurs etc.

After reading the error message people often think that either this is the only information SQL Server has for the deadlock or SQL Engine doesn’t share more information since that is internal. But that’s not true, you can get plenty of information that you may need in order to resolve\avoid Deadlocks in Future (yes this is quite possible, well- in most of the cases) This information about Deadlock is also known as Deadlock Graph.

There are many ways to get Deadlock Graph, let’s discuss a few in detail:

1. Trace Flags

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Prior to SQL Server 2005 using trace flag was the only option to get Deadlock information. In SQL Server 2000 you can use Trace Flag 1204 & 1205 in order to get the information about the Types of locks held, commands being executed and more detailed information about the deadlock.

Starting from SQL Server 2005 Microsoft Introduced Trace Flag 1222 and Profiler trace Event that can be used to get Deadlock Graph. In Trace Flag 1222 you get a detailed information about all the processes running and participating in the deadlock, which query they were running, what all resources were locked, what was the isolation level and what type of lock request was being blocked and why. You can use all this information to troubleshoot the problem.

You can switch-on Trace Flags either by using DBCC Commands or by Startup Parameters.

 

a. Enable Trace Flag using DBCC Commands

DBCC TraceON(1222)

Once the trace flag is enabled you can check the status by using DBCC TraceStatus command, it will provide an output similar to below screenshot.

In the above screenshot you can see that the trace flag 1222 has been enabled but on Session level. Means the trace flag will work for only the current session. If you want to enable this for the full instance you need to give one more parameter i.e., -1. This will enable the trace flag on global level. To enable the trace flag on Global level let’s first disable\switch off the trace flag on session level and then we’ll enable it on global level.

DBCC TraceOFF(1222)

DBCC TraceON(1222,-1)

Now let’s check the status one more time.

So the trace flag has been enabled on Global\Instance level.

 

b. Enable Trace Flag using –T Startup Option

SQLServer uses Startup Parameters during startup to find Master DB Data\Log & Error Log Files, along with this one can also specify some server wide Conditions. Most users do not need to use anything apart from Data\Log & Error log File paths, until and unless you want to change the default startup behavior of DB Engine.

Trace Flags are used to start the server with nonstandard behavior. When using this functionality use an Uppercase T to pass trace flags, a lower case t enables other internal flags that are required by only SQL Server support engineers. The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option.

You can do this in SQL Server Configuration Manager, Choose the right SQL Server Service – right click and open properties. Follow the screenshot below to specify the trace flag and press Add. (the below screenshot is from SQLServer 2012)

Note: Changing any Startup Parameter\option needs a SQL Service Restart.

 

The Deadlock Graph

Once the Trace Flag is enabled (no matter using which way you’ve enabled the Trace Flag) if any deadlock gets captured by the Deadlock monitor the relevant information is captured and logged into the error log. If you read Relevant Error log, you’ll find the Deadlock graph (the deadlock information about all participating processes in XML Format) information gets captured in the error log in clear text.

Refer the screenshot below to see how it looks:

In next part we’ll see how to get same Deadlock Graph (text Information as well as a GUI Representation) using Profiler and how to interpret it.

 

Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLDB