Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (2024)

Jes Schultz

Locking, Blocking, and Isolation Levels, SQL Server

33 Comments

What’s a deadlock? Well, let’s say there’s a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from the grappling gun at Cheetah. The problem is that Wonder Woman already has a lock on heropponent, and Batman has his. This would be a superhero (and super) deadlock.

When a deadlockoccursin SQL Server, two or more tasks are running and holding locks on data. Then, each task requeststo lock the data the other task is already holding. Both tasks wait for the other to give up. Neither does. SQL Server could let this showdown continue indefinitely, but it won’t. It picks one task – usually, whichever will be the least expensive to roll back – as the victim, and that task is killed.

How do I know if it’s happening to me?

You might notice slowness in your application. Users might complain about slowness or error messages. And, you’ll see a messagein the SQL Server Log that says “Transaction (Process ID 103) was deadlocked on resources with another process and has been chosen as the deadlock victim.”

Your server has encountered a deadlock.

How can I capture more information about it?

You have several options: you can enable a trace flag to write more information to the log, you can capture deadlocks graphs using Profiler or Extended Events, and you can track the number of deadlocks occurring using Performance Monitor.

Use trace flags to write to the log

Two trace flags can be enabled to capture more information in the log: 1204 and 1222. 1204 lists the information by node; 1222 lists it by process and resource. You can enable both simultaneously. To enable the flags, use the command

Transact-SQL

1

DBCC TRACEON (1204, 1222)

Here’s a small sample of what would appear in the log:

Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (1)

No one wantsto read through that, manually parsing it to figure out what database it occurred in, what tasks were involved, and which was the victim. Wouldn’t it be great if this was represented graphically?

Capture a deadlock graph with Profiler or Extended Events

It is – you just have to know where to look to for a deadlock graph. In SQL Server 2005, 2008, and 2008R2, I prefer to use a Profiler trace; in SQL Server 2012 and 2014 I rely on Extended Events.

Profiler

When you set up a Profiler session to gather deadlock information, select the “Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events. Make sure you go to the Events Extraction Settings tab and select the option “Save Deadlock XML events separately”. This ensures that the.xdl files – the graphs – will be saved as separate files. When you start the session, if a deadlock occurs, you will see “Deadlock graph” captured as an event.

Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (2)

The task with the blue X on it is the victim. By holding your mouse over the process oval, you can see what statement was being executed by that task. The Key Lock rectangles will help you find the object and index that the locking and blocking occurred on.

Extended Events

In the future, Profiler will be removed from SQL Server. Extended Events (EE) is taking its place. You have two options for getting deadlock information using EE. First, there is the system_health session that, by default, runs continuously. You can mine this for deadlocks that have happened in the past. You can also set up your own session to capture only deadlock information (and any other relevant events).

If using the system_health session, filter on “xml_deadlock_report”. If setting up your own session, capture the same event. If you are using the GUI available in 2012 and 2014, you can open the file and view the deadlock graph on the “Deadlock” tab.

Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (3)

If parsing the results using T-SQL and XQuery, you woulduse the .query method to extract the deadlock XML, like such:

Transact-SQL

1

SELECT DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph

This generates the XML, which you would then save as an .xdl, then close and re-open with SSMS to view the graph.

Track Performance Counters

Performance Monitor (PerfMon) is valuable for tracking manystatistics. One of the metrics you can track for SQL Server is SQLServer:Locks – Number of Deadlocks/sec. This is the least informational of the methods mentioned here – it only gives you a count of how many are happening per second in your system. It can be helpful, however, to track this to see what times of the day produce the most deadlocks. You can then use the other methods mentioned here to research what is happening during that time to cause the deadlocks.

Don’t be a victim!

Take charge of your SQL Servers! Become familiar with detecting deadlocks, finding out what caused them, and fixing them! For more tools and advice, visit Locking and Blocking in SQL Server.

Previous PostOur SQL Server Performance Troubleshooting Class: Attendee FeedbackNext PostSizing SQL Server for AWS

33 Comments. Leave new

  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (4)

    Marios Philippopoulos

    July 21, 2014 6:06 am

    Hi,

    Question on the “SQLServer:Locks – Number of Deadlocks/sec” perfmon counter.

    This measures deadlocks that happen to be occurring at the exact time this counter is polled?

    If that is the case, it may not be representative of the deadlock occurrence, as it would be very circ*mstantial to happen to poll it at exactly the right time the deadlocks occurred.

    In my case, I poll this counter every 2 minutes, so I wonder how useful my data would be.

    Thank you for this link and webcasts, I am a loyal fan!

    Marios Philippopoulos

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (5)

      Jes Schultz Borland

      July 21, 2014 7:41 am

      Correct. If you’re looking for an average idea of how many occur in your system on a day-to-day basis, that PerfMon counter is a good start. If you need a more in-depth analysis at any one point in time – say, users complain the system regularly locks up at 9:00 am – then you would want a more in-depth tool.

      Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (6)

    Rauf Gill

    August 6, 2014 3:03 pm

    Hi Jes,

    Thanks for sharing such a nice article. I have a question on deadlocks. We have a database where we are experiencing deadlock issues but if we use a few months older copy of the same database with same structure we don’t get deadlock issues. There is more data in latest copy but difference is not huge. We are wandering what might be causing deadlock issues on the latest copy when no structure changes has been done.

    Your early response is much appreciated.

    Kind Regards
    Rauf Gill

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (7)

      Jes Schultz Borland

      August 7, 2014 8:27 am

      Are you running the same queries against both sets of data? Have you looked a the execution plans to see what is different?

      Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (8)

    murari

    January 23, 2015 5:23 am

    Please provide query to get dead lock information- thanks.

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (9)

      Brent Ozar

      January 23, 2015 1:39 pm

      Murari – check out the post above. Enjoy!

      Reply
      • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (10)

        John Zabroski

        June 7, 2018 3:12 pm

        I think you only posted a piece of it:

        SELECT DeadlockEventXML.query(‘(event/data[@name=”xml_report”]/value/deadlock)[1]’) AS deadlock_graph

        generates error:

        Msg 4121, Level 16, State 1, Line 1
        Cannot find either column “DeadlockEventXML” or the user-defined function or aggregate “DeadlockEventXML.query”, or the name is ambiguous.

        Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (11)

    Lakshmi

    March 4, 2015 12:30 pm

    Nice article. Thanks a lot.

    Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (12)

    Michael

    August 11, 2015 5:05 am

    Great article, thanks. Is there a delay in events getting to the Extended Event log? Had a deadlock over half an hour ago but it doesn’t show up.

    Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (13)

    Peter

    April 29, 2016 5:51 am

    I’m looking at the screenshot in “Use trace flags to write to the log” it only seems to show one query?
    What use is that if we can’t se the other query? (2 queries are needed to deadlock?)

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (14)

      Peter

      April 29, 2016 5:56 am

      Also are there any drawbacks to enabling those 2 flags?

      Reply
      • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (15)

        Brent Ozar

        April 29, 2016 12:42 pm

        Peter – yeah, generally speaking anytime you enable trace flags, that gives you non-default routes through the SQL Server engine code, so you’re using less-tested code.

        Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (16)

      Brent Ozar

      April 29, 2016 12:43 pm

      Peter – not necessarily, even 1 query can experience parallelism deadlocks by itself. The screenshot just doesn’t show the whole screen.

      Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (17)

    Jared

    May 12, 2016 3:10 pm

    I captured deadlock graph with sql profiler in SQL 2008 r2. The deadlock victim request mode is ‘X’ where us owner mode is ‘U’ and the other resource request mode is ‘U’ and owner mode ‘U’ – Both transactions are trying to update one table with same index (in this case clustered index) as shown on the rectangle key info. I can see the queries, server process ids …. but need help how to prevent these kind of deadlock please?

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (18)

      Brent Ozar

      May 12, 2016 3:14 pm

      Jared – this is kinda beyond what we can explain in a blog post comment, but check out our indexing training, videos, and blog posts like this:

      https://www.brentozar.com/archive/2011/09/indexing-for-deletes/

      Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (19)

    Kalen Delaney

    June 29, 2016 12:33 pm

    Great analogy. I love Wonder Woman. I used to have a deadlock example I used in classes involving Luke and Han. And I had action figures to go along with it.

    You said: “When a deadlock occurs in SQL Server, two or more tasks are running and holding locks on data. ”

    Actually, deadlocks can occur on other resources besides data. Most common (after data) is deadlocking on thread resources. Less common is deadlocking on memory resources.

    I know this post was just talking about deadlocking on data resources, but your sentence makes it sound like all deadlocks are on data.

    Cheers
    Kalen

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (20)

      Erik Darling

      June 29, 2016 1:52 pm

      One of the prettiest deadlock graphs I’ve ever seen was on parallel threads. It looks like a very ornate Christmas tree ornament.

      Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (21)

    Markus

    August 18, 2016 9:32 am

    Is there any chance to activate the “Save Deadlock XML events separately” option in a server side trace without using Profiler? I generally prefer this way of tracing by using a SQL Script but I could not see any difference in the generated Trace Definition file created from two different traces with the above option turned on and off. they were 100% similar, so the option to get the XDL files seems to be ignored…

    Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (22)

    Goud

    September 6, 2016 7:00 am

    Hi Brent Ozar,
    If we use extended Events/Trace flags as system parameters/Performance counters for long time to retrieving dead lock information from server, Is creating any performance issues. Please help me.
    Thanks in advance.

    Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (23)

    Bob

    September 7, 2016 12:17 pm

    When I save the XML results as a .xdl file and reopen in ssms i’m getting an “invalid” error. Please help.

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (24)

      Bob

      September 7, 2016 12:20 pm

      Additionally, when I click the graph tab, I don’t see anything remotely close to what you show in your screenshot above. Just a bunch of lines:-(

      Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (25)

    Kamlesh Chauhan

    November 20, 2016 5:38 am

    How do I automatically get mail of deadlock graph(Graphically) through t-sql or powershell.

    Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (27)

    Randal

    May 24, 2017 1:57 pm

    Hello,
    When I run this query against my local 2012 instance:
    SELECT DeadlockEventXML.query(‘(event/data[@name=”xml_report”]/value/deadlock)[1]’) AS deadlock_graph

    I get this:
    Cannot find either column “DeadlockEventXML” or the user-defined function or aggregate “DeadlockEventXML.query”, or the name is ambiguous.

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (28)

      Erik Darling

      May 24, 2017 6:46 pm

      That query looks pretty woefully incomplete. It’s missing a FROM, at minimum.

      Reply
      • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (29)

        Paul Lee

        June 12, 2017 4:41 pm

        I copy and paste what was post on this article and get same error.

        Reply
        • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (30)

          Brent Ozar

          June 12, 2017 5:06 pm

          Paul – she’s saying LIKE that. You’re going to have to write your own query. She only got you started. If you’re looking for a pre-baked solution to deadlocks, go pick up a monitoring tool.

          Reply
          • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (31)

            zeke

            June 29, 2017 1:32 pm

            Brent – thanks so much for the pedantic and useless response. Clearly, people reading this article are looking for the steps to capture deadlock information through query. Some steps appear to be missing.

          • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (32)

            Erik Darling

            June 29, 2017 3:45 pm

            Zeke — you’re commenting on a three year old post by someone who doesn’t work here anymore.

            If you’re looking for a query to do it, the internet is full of them.

            Don’t be afraid to Google what you’re after, buddy.

  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (33)

    Stefan Sehlberg

    April 24, 2018 3:51 pm

    On a hiring interview: We hire you if you can explain deadlock to us. The applicant: Hire me and I explain deadlock to you 🙂
    Thanks for a good article.

    Reply
    • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (34)

      Brent Ozar

      April 24, 2018 4:05 pm

      HAHAHA, I see what you did there. Nicely done.

      Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (35)

    Bob Holmes

    October 16, 2018 9:29 am

    I think the Wonder Woman – Batman – Cheetah analogy needs a diagram!

    Reply
  • Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (36)

    Phani kumar

    August 12, 2021 9:11 am

    IS THERE A WAY TO CAPTURE THE COMPLETE XML OF THE DEADLOCK IN THE CATCH BLOCK OF THE STORED PROCEDURE

    Reply

Leave a Reply

Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (37)

Hi! I’m Brent Ozar.

I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him.I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.

Want to advertise here and reach my savvy readers?

Capturing Deadlocks in SQL Server - Brent Ozar Unlimited® (2024)
Top Articles
Latest Posts
Article information

Author: Van Hayes

Last Updated:

Views: 5571

Rating: 4.6 / 5 (46 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Van Hayes

Birthday: 1994-06-07

Address: 2004 Kling Rapid, New Destiny, MT 64658-2367

Phone: +512425013758

Job: National Farming Director

Hobby: Reading, Polo, Genealogy, amateur radio, Scouting, Stand-up comedy, Cryptography

Introduction: My name is Van Hayes, I am a thankful, friendly, smiling, calm, powerful, fine, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.