Understanding, Analysing and Troubleshooting Deadlocks – Part3

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. In Second Part Understanding, Analysing and Troubleshooting Deadlocks – Part2 we discussed how to get Deadlock Graph from Error Log using Trace Flags.

In this Part we’ll see how to get the same Deadlock graph XML data along with a GUI Representation using Profiler.

Create a New Trace, connect to the relevant server and change the default trace name as desired, you can use any template – for this particular example to keep it simple I used Blank Template which doesn’t pre-select any Event.

In the Events Selection tab, expand the locks and you’ll find the first sub-event as Deadlock Graph, select that – you can select other events also if required.

Now whenever a deadlock will occur you’ll get something similar to below – Profiler by default represent the complete scenario in a GUI flow chart. You can Hover your mouse to any part to get more info on the events & Processes.

The Oval shape is used for different processes and the Rectangular share is used for different objects\resources where the deadlock occurred. The Victim process is the one which is crossed by two diagonal lines (x).

We’ve got the GUI Representation but let’s say you still want to get the XML raw information – you can extract the event details from Profiler itself, just Right-click on the event in the upper Tab of trace and select Extract Event Data from the context menu.

A SaveAs Dialog will appear which will help you to save the information in a XDL File. Once saved you can open the file in the SSMS to get same GUI representation.

In case you need XML Data you can open the same XDL file in a Notepad.

In the next Part we’ll discuss how to get this information using Extended Events and how to read the Deadlock Graph XML Data.

Happy Learning 🙂

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