How I Use sp_BlitzLock To Investigate SQL Server Deadlocks
Video Summary
In this video, I delve into the intricacies of SPBlitzLock, a powerful tool designed to help Database Administrators and developers navigate the often frustrating world of SQL Server deadlocks. Drawing parallels between my espresso machine’s quirky behavior and deadlock issues in SQL Server, I explain how SPBlitzLock can be a valuable asset for identifying and resolving these problematic situations. By walking through the output of system health extended events and detailing the various filters and parameters available within the procedure, I aim to equip viewers with the knowledge needed to effectively use SPBlitzLock in their own environments. Whether you’re dealing with query-on-query deadlocks or parallel deadlocks, this tool offers a comprehensive breakdown of deadlock information, making it easier to pinpoint the root causes and implement solutions.
Full Transcript
Erik Darling here with Darling Data. And in this video, we’re going to discuss probably the stored procedure that I’ve spent some of the most time working on of all the procedures in the first responder kit, probably only SPBlitzCache, maybe SPBlitzQueryStore, but I haven’t touched that one in a while. So, um, um, Blitz who at first, but, you know, uh, only so much you can do in there. But, uh, SPBlitzLock, uh, will help you with deadlocks, not locking deadlocks. And, uh, I’m going to start this off by talking about how, uh, my stupid espresso machine reminds me a lot of deadlocks in SQL Server. You see, my stupid espresso machine is a Philips 3200. And, uh, it’s a fully automated espresso machine, which is great for someone like me who is not terribly invested in the process of making espresso, but desperately needs the output of an espresso machine, which is, uh, a whole lot of caffeine packed into a small, small container. Now, when you, if you, if you have an espresso machine, you can get a little bit of caffeine.
If you have an espresso machine or use an espresso machine, uh, you might know that when you turn it on, there’s often a bunch of steps that you like the warmup stuff that it has to do before it’s ready to make you that perfect cup of espresso. My, my espresso machine, um, if I started up and, um, it, it realizes that there’s enough, not enough water in the basin or that the, the puck receptacle is full, it will actually stop the whole startup process and wait for you to, uh, come fix it. Either empty the, the puck receptacle or fill the water basin. Uh, when it does that, there’s no audible beep.
Right? There’s no, like, thing that, like, extra warns you about the fact that it stopped doing anything, which, if you’re sitting there staring at it, isn’t a big deal. But, you know, for someone like me, when I know something’s going to take a minute or two to warm up, I hit the button and then I go do something and figure I’ll come back and hit the, the double shot button and hit start. And then my espresso will come out.
So that’s frustrating because that’s sort of like what DBAs and developers deal with, with deadlocks. There’s often no really loud beep when there are, uh, when, when deadlocks are happening. A lot of applications sort of swallow, uh, error messages, right?
You like any user might not get an error, like a message that there was a, their, their transaction was involved in a deadlock. They just might get some error message like, um, try your, try your request again later. Do it again some other time.
Maybe you’ll get lucky. The other problem with the espresso machine is that, uh, the, the, the grinding area, which now that I say it sounds a little dirty, but the, uh, the grinding dish, let’s call it, let’s keep it a family friendly SQL Server video.
The grinding dish is this weird shallow thing. And like the, the, like the grind implement. God, it just keeps getting worse.
It’s in like the front left-hand corner, but like the angle of the dish, when like you put beans in, they don’t always like, like you rely on the shake of the, the, the espresso machine to like drive the beans towards it. But, uh, they don’t always, they can’t always make it there.
And there’s like a couple of weird things in there that beans can get stuck on so that like, you have to like go fish them out. It’s, it’s, it’s weird. And, uh, that reminds me a lot of deadlocks because getting deadlock information is a terribly frustrating thing.
Uh, it involves, uh, for most, I think for most folks these days, it’d be using extended events, which is, which leads to a whole lot of XML parsing and shredding. If you want to get your, get information about your deadlocks into sort of a readable format, that’s just not an XML document that you have to look all over the place for stuff for. Um, so there’s that.
So because of all that, back in 2017, while I was, I was drunk on an airplane with, uh, El Capitan himself, uh, flying out to, to, to, to pass summit for a pre-con from Chicago. Uh, uh, uh, I, I started writing SP Blitz lock. Uh, Brent was terribly uninterested because of the, the amount of X query involved.
Uh, it’s also probably why he doesn’t like Blitzcast very much either, but, um, yeah, it’s, uh, it, there were some weird design choices in it then mostly due to plain drunkenness. But, uh, I recently gave her a facelift and, uh, fixed a lot of the, the issues that I had with it over the years. So, um, hopefully now everyone can, well, I mean, I was drunk, but I wasn’t drunk on a plane when I fixed the, I was actually just drunk in a hotel room in Las Vegas.
So, uh, for most of it anyway. Uh, but yeah, um, now SP Blitzlock is in a pretty decent shape, I think. So, uh, by default, everyone, every, just about every SQL Server I look at these days has a system health extended event on it, which has, captures deadlock information.
So, uh, by default, if Blitzlock will go and look at that. If you have an event set up specifically to look at deadlocks, you can look at that by feeding it the name of the extended event session that you use to capture deadlocks. So, let’s just take a quick look at the output when we go to the default system health extended event session.
I put a pretty far back start date on this. Uh, by default, it’ll go back one week. Um, the system health extended event is sort of a best effort thing.
Data will be there if it can hold it. If not, it kind of rolls over, goes away, says bye-bye. Um, so, there’s a, you know, it’s good if you don’t have anything else, but if you, if you are okay with setting up an extended event session just to capture deadlocks, you’re, you’re probably better off doing that to have. So, but, uh, the, the output that it gives you, pretty, pretty simple.
Uh, you know, you get the server name, the type of deadlock. So, there’s two types of deadlocks that, uh, SP Blitzlock will, uh, help you analyze. There’s regular query-on-query deadlocks.
And then there’s also parallel deadlocks, which occur when, uh, you have a parallel execution plan. And the threads in that plan have some sort of order-preserving dependency in them that, uh, causes certain threads to not be able to make progress. And then eventually you may end up with a parallel deadlock.
You may end up with a bunch of exchange buffers spilling out to tempdb. It’s a, it’s a pretty nasty situation. I’m definitely worth looking into and trying to fix. Um, sometimes a max-dot one hint is your friend.
But you get, uh, the event date, the database it happened in, uh, the SPID, which I don’t usually find terribly useful. But, you know, I like to express as much of the data, uh, as, as gets stored in the deadlock report as I can. Uh, you get the deadlock group.
So, you know, which queries were involved in which, uh, set of deadlocks. Uh, you get the query that, uh, as much of it as possible. Sometimes the query text is truncated in here. Um, you get the, the, when possible, you get the object names, the, the table that were involved in the deadlock.
Uh, you get the isolation level, which is, uh, generally correct. Um, there are some circumstances when it may not be. Uh, you get the owner mode and the waiter mode of the deadlocks, which is good to have.
So you can figure out if you, um, have, uh, reader on writer deadlocks, which are most easily solvable by an optimistic isolation level. Read committed snapshot isolation or snapshot isolation. Uh, I picked up the client options because sometimes these get weird and you can, uh, see odd things in there.
Uh, some, uh, some basic information about where the queries came from, login, host name, client app, how long they waited before the deadlock detector woke up and gave someone the old kapow. Uh, the wait resource, which is not easy to decode.
Uh, so I don’t do that. Um, if you feel like figuring that out on your own, go for it. Um, if anyone’s setting deadlock priority, how much of the transaction log was used, some information about, uh, when things were happening in there, uh, the transaction name.
Uh, there’s some weird stuff to look out for in here. If you’re using the JDBC driver, you definitely want to look out for implicit transaction, the transaction name column that can be, uh, useful to have.
Uh, the status column is also pretty useful. Um, if you catch a lot of stuff in the status column that says sleeping, you, like, you might have an application bug where, like, queries aren’t closing commands out or something like that.
And, um, you, uh, you know, you need to fix that. You can’t really, can’t really do anything there. Uh, if you kill it, then they’ll roll back and you may not want that to happen.
Uh, if it’s a parallel deadlock, then this will get populated with details about what happened within the parallel deadlock. These are, of course, regular deadlocks, so there’s nothing too interesting in there. And then, of course, I also give you the full deadlock graph back, which SQL Server 2022 added a whole bunch of nonsense to, uh, just in case you want to copy and paste that into, uh, something that will visualize deadlock graphs for you.
Management Studio does it. Plan Explorer does it. So there’s stuff in there.
Uh, the bottom section will enumerate or roll up some of the information we have about deadlocks. Um, you know, give you some friendly advice, right? Like you might need RCSI, uh, which objects have been involved in the most deadlocks, which indexes, uh, it’ll also, you know, figure out if store procedures or ad hoc queries were involved in them.
Uh, it’ll give you a total, you know, deadlock wait time for stuff, uh, by database and object and, you know, in total. So there’s all sorts of useful information in there. Now, I’m not going to go over all that stuff again, but I am going to tell you that if you have a specific extended event session set up to, uh, look at deadlocks, then all you need to do is fill in the event session name parameter.
Um, again, I’m just going to use the start date here and accidentally hit the space bar. I’m just going to use the far back start date there. And this is one deadlock shy of the other one only because I didn’t have this set up when I was doing other deadlock stuff.
So, um, it looks a little different, but you can still probably, um, figure out enough from that there. Uh, there are a bunch of other filters in Blitzlock. You can filter down to database, object, um, user login, pretty much any, there are a lot of things you can filter to in there.
Um, you can set an end date. So if you want to look for a specific day or something, you can, you can filter to start date and end date. It’s pretty good.
Uh, there are a few others. Um, there’s, as always the, the help parameter is where you want to go to keep abreast of all of the latest developments in any of these store procedures. So, uh, I would, I would highly advise you do that.
So anyway, uh, that’s SP Blitzlock. You don’t have to do much there. Uh, pretty much just run it.
It spits out all the information and then it’s up to you to figure out, uh, what goes where and when. You know, so one thing that I do want to actually sort of tie into another video is, uh, we talked in the SP Blitzcache video about using only SQL handles. So if we go into the deadlock graph here and we scroll past all of whatever the heck this is, um, you’ll see that there are SQL handles for various, uh, queries in the XML deadlock report.
And this is what you would want to paste into the only SQL handles parameter in SP Blitzcache or SP Quickie Store to try to find execution plans for the queries that were involved in the deadlocks. A lot of the time, um, a lot of the time, uh, the query plan will tell you a whole lot about why the deadlock happened. That, uh, that might not be totally obvious from just, just the text of the query.
So anyway, that’s SP Blitzlock. It’s pretty awesome. I’m very proud of it.
Uh, if you like this sort of SQL Server content, don’t forget to do the old like and subscribe for me. I don’t know why I’m getting such interference over here. Everything was normal a second ago.
Green screens are temperamental. I guess, I guess like espresso machines. Uh, uh, what did I, I forget what I said last. Like and subscribe.
I’m forcing you to. Um, I have your family in a basement. If you don’t like and subscribe, um, I’m going to take them out to a nice dinner. They’re nicer than you could ever, nicer than you could ever bring them to. And then they’ll lose all respect for you.
Something like that. Anyway, uh, I hope you enjoyed yourselves. I hope you learned something. Thank you for watching. And I will see you in another video somewhat shortly. I’m going to have to run through these demos a little bit.
And then we’ll see, we’ll see what, see what we come up with. All right. Thanks for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.