SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

Identify Deadlocks Using Graphical Deadlock Chain Event in SQL Server Profiler

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

Step 1: 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.

Step 2: 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

Step 3: In the Events Selection Tab choose the respective Events as shown in the snippet below. Most importantly Deadlock GraphLock: DeadlockLock: 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


Step 4: 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

Step 5: 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


Step 6: SQL Server Profiler Deadlock Graph Event is shown in the below snippet.

Deadlock Graph Captured by SQL Server Profiler

Learn How to Enable and Disable Trace Flags 1204 and 1222 to Capture 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.

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement