"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."







Follow @MyTechMantra on Twitter
We're on Facebook





Identify Deadlocks Using Graphical Deadlock Chain Event in SQL Server Profiler


Jan 30, 2014

Introduction

This article explains how to identify deadlocks in SQL Server using graphical deadlock chain event in SQL Server Profiler.

We would highly recommend you to read “Identify Deadlocks in SQL Server Using Trace Flag 1222 and 1204” to learn more about how to write to SQL Server Error Logs in the event of a Deadlock in SQL Server.

How to Capture Deadlock Events in SQL Server Using SQL Profiler

Once you have opened SQL Server Profiler. Click File -> New Trace -> Connect to SQL Server Instance to open up Trace Properties window as shown in the snippet below.

In the Trace Properties window specify Trace Name and choose Standard Template and then click on Events Selection Tab.

Identify Deadlock Using SQL Server Profiler

In the Events Selection Tab choose the respective Events as shown in the snippet below. Most importantly Deadlock Graph, Lock: Deadlock, Lock: Deadlock Chain and the “Run” button to start the SQL Server Profiler Trace.

Choose Deadlock Graph, Lock Deadlock, Lock Deadlock Chain in SQL Server Profiler

Newsletter Signup

If you wish to save Deadlock XML separately then go to Events Extraction Settings tab and select the checkbox “Save Deadlock XML events separately” and specify the file path.

Save Deadlock XML and XML Showplan events in SQL Server Profiler

In the below snippet you could see that SQL Server Profiler has captured a deadlock event and the information is represented in a Graphical Format for easier understanding. The deadlock graph shows that Server Process ID 51 was the victim when there was a deadlock between Server Process ID 65. Move the cursor over the Server Process ID’s to view the TSQL script run be each of the SPID’s.

Capture Deadlock Using SQL Server Profiler Deadlock Graph Event

SQL Server Profiler Deadlock Graph Event is shown in the below snippet

Deadlock Graph Captured by SQL Server Profiler

Learn more about How to Enable and Disable Trace Flags 1204 and 1222 to caputure deadlock events in SQL Server Error Logs.

Conclusion

In this article you have seen how to identify deadlocks using graphical deadlock chain event in SQL Server Profiler to get a detailed overview of the events which lead to the deadlock.

References

Detecting and Ending Deadlocks
Analyze Deadlocks with SQL Server Profiler


Share this Article