Analyzing Deadlocks with SQL Server Profiler

Modified on 2011/09/21 04:08 PM by cathal Categorized as Uncategorized
Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files. The following article shows how to extract the deadlock data from the profiler trace.

This article has a section entitled "A Brief Profiler Primer" that lays this out in further detail. Note: please take care to run traces for a long period of time (typically at least 24hours) and ensure that you are only capturing the deadlock graph class otherwise the trace will impact of server performance.

Once you have a deadlock graph trace you will want to analysis it - the following blogs contain lots of useful information to help with this:

Try Evoq
For Free
Start Free Trial
a Demo
See Evoq Live
5 Things You (Probably) Don’t Know About Evoq
Latest Blog
How to Make Advocate Marketing Work for You
Read It