Introducing sp_BlitzLock: For Troubleshooting SQL Server Deadlocks

Deadlocks are hard

Especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain.

I had a script written that parsed out the XML, but really, having a folder full of random scripts is for old people.

Like Twitter and reverse mortgages.

So I turned it into a Stored Procedure, and added it to the FRK.

Notables

Right now, this proc is only compatible with SQL Server 2012+. Even though it’s possible to capture deadlocks in older versions via the System Health (or a custom) Extended Event session, it’s a different batch of XML. If anyone out there on an older version wants to add separate parsing, well, that’s the beauty of Open Source. Head on over to our GitHub repo to learn how to contribute.

With that out of the way, here are the current set of variables:

  • @Top: Use if you want to limit the number of deadlocks to return. This is ordered by event date ascending
  • @DatabaseName: If you want to filter to a specific database
  • @StartDate: The date you want to start searching on.
  • @EndDate: The date you want to stop searching on.
  • @ObjectName: If you want to filter to a specific able. The object name has to be fully qualified ‘Database.Schema.Table’
  • @StoredProcName: If you want to search for a single stored proc. The proc name has to be fully qualified ‘Database.Schema.Sproc’
  • @AppName: If you want to filter to a specific application.
  • @HostName: If you want to filter to a specific host.
  • @LoginName: If you want to filter to a specific login.
  • @EventSessionPath: If you want to point this at an XE session rather than the system health session.

Pretty standard stuff for a Blitz script! Note that by default we look at the System Health session for deadlocks.

If you have a custom session, use the path to the files for it like so: 'c:\temp\deadlocks*.xel'.

We can’t get any custom fields that you’ve added to a session. There didn’t seem to be a lot of joy in reverse engineering a session definition from the XE DMVs, and then constructing dynamic XQuery to parse it.

If you wanna spend your weekend on that hobby horse, well, prepare for some saddle sores.

Output

So what does running it get you?

The first set of output looks like this:

Take 1

And scrolling right…

Take 2

Helpful stuff! This is all of what I’d consider useful information from the deadlock XML laid out in front of you.

And of course, we do high-level analysis of all the stuff we pull out.

Probably right!

The information in here can help you track down which tables and stored procs are involved in your deadlocks, and give you queries to get more information about them using our other scripts — sp_BlitzIndex for indexes and sp_BlitzCache/sp_BlitzQueryStore for queries/procs.

Support

If you run into any problems, please use GitHub for support. We can’t do troubleshooting via blog comments.

Thanks for reading!

Brent says: I already used this at a client and was absolutely dumbfounded by how much cool information it pulled back instantly. In less than one minute, we were able to see exactly which logins were causing the deadlocks, what code was at fault, and what index we needed to add. Good job, sir.

Previous Post
Rebuilding Indexes Can Slow Queries Down
Next Post
Maintenance Plans Enable Your Disabled Indexes

32 Comments. Leave new

  • Thank you, thank you, thank you! Trying to figure out a deadlock – day 2 today, and I thought let’s go download the latest “First Responder Kit”, and what do I see this blog post, with a new SProc called “sp_BlitzLock”. And now I know, where the deadlock is – Brilliant!! Now I just have to figure out, how to fix it.

  • looks good, however after I installed in on a server which was being monitored by Idera, I got an alert from DM that I had a deadlock, ran the script for date and no data returned

  • How sick is that boi. ?

  • Does this require full install of all the blitz stuff? Attempting to install/run separately with defaults gives me…

    Msg 25718, Level 16, State 3, Procedure sp_BlitzLock, Line 158 [Batch Start Line 2]
    The log file name “(null)” is invalid. Verify that the file exists and that the SQL Server service account has access to it.
    The statement has been terminated.

    • Gary,

      Please follow the directions for support in the post.

      Thanks!

      • Cool – after spending another 5 minutes looking for any instructions on github including the code for the sproc itself…sorry for missing the obvious but I’m missing the obvious…

        • Wow, 5 minutes! Hope you didn’t break a sweat.

          Here’s the documentation: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/README.md

          And here’s the source: https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_BlitzLock.sql

          In most cases the 6th minute is when everything makes sense. Hold on a little longer next time!

          • Dear Erik Snarky, I did break a sweat unfortunately and I blame you 😉 I actually spent more like 10 minutes but hate to admit that much wasted time…I was actually looking to find something regarding what was needed to install the procedure wrongly assuming that the error had something directly to do with your sproc. I had found the links immediately but they weren’t helpful so didn’t communicate well what I was looking for.

            However, the much larger mystery (and am guessing would be Brent’s question reading this might be) was why it appears you thought getting a “The log file name “(null)” is invalid” error was normal with running your procedure 😉
            ps…If you hadn’t figured it out already, it turned out we were out of log space on the server at the time which just happened to be an odd coincidence.

          • your mom

  • This is awesome. Thank you again for adding another really helpful tool to the kit.

  • Old people! What is the world coming to? God forbid they should be male/female, black/white/hispanic/asian… too. How do such people get here?

    • AJ — I speak to God personally on a regular basis and the only thing I’ve ever heard explicitly forbidden is left handed dinosaurs. Thankfully that situation was avoided.

  • I get this error when running it on our DB (i run the sp with all defaults):

    Msg 9455, Level 16, State 1, Procedure sp_BlitzLock, Line 613 [Batch Start Line 4]
    XML parsing: line 2, character 805, illegal qualified name character

  • Great new addition to your amazing toolkit. Thank you! It works great.

  • I love this tool. But I did find a potential bug (or maybe I misunderstand the value of the 2nd query result set?)… If I run: sp_BlitzLock @DatabaseName = ‘client_db’ the first query result set is limited to only data within the “client_db” database, but the second data set shows results from ALL db’s within the server… ?

    • J — yeah, that sounds like I didn’t add filtering. If you have GitHub, go ahead and open an issue there (firstresponderkit.org).

      Thanks!

  • Derrick Bradley
    February 15, 2018 11:26 am

    We are running a newer ERP system and deadlocks are a massive issue (Couple of hundred a day) and I’m trying to get to the bottom of it, but it feels like I’m drinking from the fire hose. I have been running sp_Blitzlock but would like to clarify something in the results. From what I can tell the “query” column shows the Object_ID of the Stored procedure that was running at the time of deadlock and the “object_names” column shows the table(s) that are involved in the execution of the SP for each side of the deadlock. Is that correct?

    • Derrick — please read the instructions for Support. I can’t see what’s on your screen.

      Thanks for understanding.

  • Bouke Bruinsma
    February 23, 2018 2:25 am

    This procedure is excellent. Really helps us in analyzing deadlocks.
    We ran into an issue when the default collation of the instance conflicted with SQL_Latin1_General_CP1_CI_AS. We had to change 2 lines as below in the results common table expression:

    cao.wait_type collate SQL_Latin1_General_CP1_CI_AS AS owner_mode,
    caw.wait_type collate SQL_Latin1_General_CP1_CI_AS AS waiter_mode

  • I have serveral 2016 databases running on SQL Server 2016. The one I’m trying to run sp_blitzlock against is running for a very long time. I stopped it and limited it to the top 20 and set the start date to yesterday and it has been running for more than 30 minutes. Do you know of a way to speed up the process?

Menu
{"cart_token":"","hash":"","cart_data":""}