Deadlocks are hard – especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain.
sp_BlitzLock gives you a slice-and-dice analysis of which tables, indexes, and queries are involved in most of your deadlocks so you can quickly get to the root cause.
sp_BlitzLock works on Azure SQL DB, Amazon RDS, and Microsoft SQL Server. Parameters include:
- @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:
And scrolling right…
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.
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.
How to get support for sp_BlitzLock
The sp_BlitzLock documentation covers additional parameters that will return more results, store the results to a table, and more.
For free interactive support, you’ve got a few good options:
- Talk with us in the #FirstResponderKit Slack channel (if you need a free invite, hit SQLslack.com)
- Post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include the version of sp_Blitz, your SQL Server version number (including the build #), and tag it with sp_Blitz.
- To make code improvements or report a bug, visit the Github issues list and read the Contributing Guide
34 Comments.
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.
Great! Lemme know if you come across anything wonky in there.
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
I had the same thing happen as well.
Read the big bold text under “Support”, pretty please.
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.
This is awesome. Thank you again for adding another really helpful tool to the kit.
Glad you’re enjoying it!
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.
Yea, I forgot to mention left-handers.
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
Pini – make sure to read the blog post. It includes information on where to go for support. Thanks!
Great new addition to your amazing toolkit. Thank you! It works great.
Awesome! Glad you like it!
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!
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.
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
Bouke – great, thanks! For support, please read the last paragraph.
[…] https://www.brentozar.com/archive/2017/12/introducing-sp_blitzlock-troubleshooting-sql-server-deadlo… […]
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?
Hi Tim,
Sometimes asking for a really big top, like 2147483647 helps things. But generally, querying a file full of XML is pretty painful. You may want to try spinning up an XE session specifically for deadlocks, and using BlitzLock against that instead.
Thanks!
Thank you, I’ll give it a shot.
Tim,
After your comment, I did some work on the proc. If you wanna try a different version with some changes, give this a shot:
https://raw.githubusercontent.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/0ef9f41a26b5fa6c05c623ceee01ce1f5bfcc692/sp_BlitzLock.sql
If it’s still slow, check the messages tab to see which step it’s on, and try to capture an execution plan for me.
Thanks,
Erik
Hi Brent! Thanks for this post!
Why is the event_date column data so very different from the last_tran_starteed column data?
Does the event_date column indicate the time of the deadlock?
Mehdi – go ahead and read the last section of the blog post about support. Thanks!