Of Profiler, Deadlock Graphs and XDLs

SQL Server Profiler is deprecated in favour of the more versatile Extended Events, but it still is a mighty useful tool to have.

You can save the Deadlock graphs separately (as .XDL files) when setting up Profiler. Once you select the necessary lock/deadlock events in the “Events Selection” tab, a new tab will appear called “Event Extraction Settings”. There, you have the option of saving the Deadlock graphs separately, perhaps in another folder. This can be very useful instead of sifting through a large Profiler trace file trying to find the Deadlock graphs.

Of course, if you havent done this earlier, and all you have is the profiler trace file, there is still another method to get the XDL files. Find the line with the deadlock graph (in Profiler) and right click on it and select “Extract event data”. You can then save it as an XDL file in another folder.

So now you have saved the XDLs and you want to see them. You can open them with SQL Server Management Studio to see the graphical view. To zoom in, there is no right click option; instead the solution is embarassingly simple : hold down CTRL and roll the mouse scroll wheel ….

But to get the full info, its better to open with Notepad++, which automatically formats the XML to give a nice presentable view.

The XML output might look daunting at first, but we can decipher the contents with a few chosen tags. You will notice that the entire XML is located between <deadlock-list> and </deadlock-list> tags.

The first tag to observe is the <deadlock victim=”xxx”> tag. As the name implies, this is the victim; ie this process was killed in the cyclic lock. The process is represented by a process id (“xxx” in this example), and you can also get other details in the next tag.

That next tag is <process id>. Here, it begins the tag by repeating the process id (eg: <process id=”xxx” ) and continues with other details. The important one to take away is the spid detail. It will show you what was the spid of this process that was terminated.

Now observe the first <fname> tag. It will show you what was the exact query that was deadlocked. It can be a single query, or a procedure. If it’s a procedure (or a function), it will show you the exact line that caused the deadlock rollback. Even though this is very useful, it can sometimes be more intuitive to go into the procedure and look at the preceding code. This is simple. Suppose line # 99 caused this rollback. Now script out your procedure and remove the formatting lines that SQL Server has added. This will include the USE database line, the GO line, perhaps a SET ANSI_NULLS ON or SET QUOTED IDENTIFIER On, the procedure name enclosed within “/***” and “***/” tags, more GO lines until you reach the first line of your actual procedure. Then Ctrl+G to goto the line number you want, eg: 99.

If you skip the removing of the first lines as above, you may find yourself puzzled when line 99 refers to an empty line or a comment 😉

Now you know the details of the victim using just 3 tags : the <deadlock victim>, <process id> and <fname> tags. Look for the </process> which will signal the end of the victim process information.

You can use the three tags above to now find the victorious process(es). Depending on your query, you may be lucky to find just two processes interlocked, ie a victor and a victim. But in some cases, there may be many more of these. In either situation, the 3 tags above will help you find out the details of these processes.

Note, for example, the first <process id> tag that appears immediately after the </process> tag of your victim. You can similarly deduce the spid details, the lock modes, the time waited on it, the query details and so forth.

Ok thus far, we have spoken of the Process details. There is also a Resource section in this XML, which is represented by <resource-list> and </resource-list>. Within each <resource-list> tag, you can see very concise text regarding what the problem code is, and what type of lock was applied, etc. This corresponds exactly to the details that were captured earlier in the <process> section.

Once you have isolated the Profiler details like this, you can return to the code and start figuring out how to prevent deadlocks.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s