sp_HumanEventsBlockViewer: Now With 100% More Query Plans And Prioritized Results!
Video Summary
In this video, I discuss some recent updates to SP Human Events Block Viewer, a utility script that helps analyze blocking issues in SQL Server by examining the blocked process report XML data from extended events. I’ve made two significant changes: one involves adding a second result window that queries the plan cache for execution plans related to the blocking processes, providing more context and insights into the queries involved. The other change prioritizes the output based on which objects or indexes have experienced the most blocking activity, helping DBAs focus their efforts where they are needed most. These updates enhance the usability of SP Human Events Block Viewer and its sister procedure, SP Blitz Lock, making it easier to troubleshoot and resolve blocking issues in SQL Server environments.
Full Transcript
Erik Darling here with Darling Data, rated by several prominent publications, Winesniffer Magazine, Beargut Magazine, and well there are some others, but they all got together and voted and they rated me the most capable SQL Server consultancy in the entire world. Even Atlantis is part of that. I don’t know how computers work underwater, but there’s a strong possibility that if humans had stayed underwater, we would not have nearly as many DNS problems as we do today. This is just a short video on this kind of grotesque Sunday to talk about some changes that I made to SP Human Events Block Viewer.
Now, this started off as a utility script that I could use to look at the blocked process report XML data from the extended event that SP Human Events would set up to get information about blocks, but I extended the usability of this procedure a while back to look at any extended event session that has blocked process report XML in it, because, you know, because, you know, because, you know, human events is great and it catches blocking, but, you know, it just uses the ring buffer and oftentimes, you know, not a lot of data stays in the ring buffer at once.
So it worked really well in the context of the human events procedure, which would log stuff from the ring buffer off to a table. But, you know, in the in the longer term sort of, you know, gathering blocking data with clients, you know, you kind of you don’t often want to set up things of that complexity. You just want to have an extended event that captures the block process report and you want to read data from and get information from it.
Now, the there are two changes here. And the cool thing is that since SP since a while back when I did a rewrite of SP Blitz Lock, SP Human Events and SP Blitz Lock share nearly the same code. So SP Blitz Lock is over in the first responder kit. And that looks at the deadlock extended event stuff. And thankfully, the deadlock extended event stuff in the block process report extended event stuff is pretty darn close XML wise.
So they share nearly the same code. Just, you know, one looks at deadlocks, the other one looks at blocking. But of course, blocking begets deadlocks. So our blocking begets deadlocking, I think would probably be the better way of saying that. So we’ve got two for the price of one almost anyway, the two changes that are in SP Human Events block viewer and that I also have in a pull request out for SP Blitz Lock are, I think, good changes.
Because of the way that the procedures used to work, where they’re like, you know, so the first change is around SQL handles. So identifying plans that were involved in the blocking. It used to be that both SP Human Events and SP Blitz Lock would show you the SQL handles. But then it was up to you to go use whatever plan cache mining script you care about to go out to the plan cache and look for the execution plans of the queries that were involved in blocking or deadlocking.
What I did was I added a second result window to both of those store procedures that will go out to the plan cache and look for SQL handles that were involved in the blocking and bring back some metrics about them. Now, it doesn’t use query store because I don’t know if query store is on. I didn’t want to add a bunch of complicated checks and, you know, all that other stuff. It just seemed like a lot of work. So I’m not doing that. But I am looking at the plan cache.
So what I did was I added a section of code that goes out to the plan cache and looks for execution plans for any of the queries that were involved in and well for for block viewer for blocking for SP Blitz Lock, it’ll look for anything involved in deadlocks. Now, if you’ve been watching my videos for any period of time, you’ll know that I have slowly fallen out of love with the plan cache over time. And I think the reason for that is at least somewhat obvious here, because not every plan is going to be in the plan cache when you go and look.
If your plan cache is unstable, which a lot of servers that I look at, the plan cache is somewhat unstable. If you go and look, you might not find a very a lot of historical data in there. Often the plan cache is less than 24 hours old.
But I figured it would be better to do this and make a good effort at finding execution plans for people than it would be to have them try to make the next logical step in the in the in the result output and go look for query plans of things that were involved. And for this procedure blocking for SP Blitz Lock deadlocking. So that’s the first change and you can see the separate results here and available plans.
And I think this illustrates pretty well why I am not totally in love with the plan cache. We only have blocking for a couple of things in here and we have a whole bunch more blocking granted some of these blocks were like many months and days ago. So I don’t expect that to be in there, but I just want to show you this to sort of level set, like make sure that expectations are right, that not everything that is in the blocking the block process report is going to have a plan available for it.
But I want to make a good effort to go and find that stuff for you and show it to you. So, you know, for this for this for the available plans that we have here, we can see this most recent blocking thing up here. And this is the query that was involved in the blocking.
And this is the query plan for it. You know, granted, you could probably do something with this knowledge where, you know, you have the predicate on a, you have a seat predicate. And then we have, well, it’s kind of funny how modification queries show up in here with this type of execution plan, because there’s a predicate on the column that we’re updating, which is not technically a predicate, because we’re not actually searching on it.
We’re just looking for where where ID equals something, but because we’re updating the age column, some reason it shows up as a predicate here. So we get the execution plan back. And then I also grab as many execution metrics as I can from the plan cache. So, you know, you’ll get when the plan was created, the last execution time, the count, you know, worker time, elapsed time, stuff like this, you know, so like all the sort of standard stuff that you would expect to get from a query going out and looking at looking for execution plans and query metrics.
So it’s a pretty good amount of stuff, pretty good amount of information in there. That’s the first change. The second change is that I started ranking or I started prioritizing the output by which things had the most blocking associated with them. So before the only thing that I was ordering by was check ID. I added another column to the findings table, which is a sort order, which when I do the inserts into the findings table, I order them by which thing had the most stuff going on.
And then on the way out, like I have a sorting column specifically for that. And on the way out, I sort also by I check check ID and then that column. So when you go look at the results now, you’ll see in the findings column, which should probably expand a little bit so that comes a little bit more obvious what I’m doing here. We come over here, we can see that Stack Overflow has been involved in 37 blocking sessions, which ranks above temp DB, which had two blocking sessions.
And then for more specific checks like which objects, indexes, stuff like that have had more blocking associated with them. You can see 14, 8, 5, 3, 3, 2, 1, 1. And then throughout all the results, you know, like 35, 2, 26, 1, 2, 1, you know, 38. So like all this stuff is ranked, all this stuff is sorted in here by which, you know, things had the most blocking associated with them.
I, like I said, since Blitzlock shares nearly the same code base as this, I also added that change in a pull request out to SP Blitzlock. You should see that in the next first responder kit update whenever that is, who knows? Brent’s busy playing the slots, you might not get around to pushing that pull request through for a while, who knows?
But everything in here is ranked. So you see like even like the timing in here is ranked by which things had the most time associated with it. So you can kind of get a sense of where you should concentrate your efforts by like, you know, which database tables, indexes, stuff like that had the most blocking events and blocking time associated with them.
So hopefully two good changes that will make your life easier when dealing with blocking and deadlocking issues. This is all available in the main branch of my GitHub repo for human events block viewer. Again, for SP Blitzlock, there’s a pull request out for it.
You can go hunt down the that branch over there and look for it if you want to sort of, you know, beta test a little bit that version. But other than that, that’s about it. But anyway, two changes that I’m really happy with.
This kind of, this work was kind of a part of a weird like code frenzy that I went on with like this and SP Quickie store with the time zone stuff. I don’t think, I don’t think anything changed in SP pressure detector along with these two. But, you know, again, stuff that I’m really happy with, stuff that makes my consulting job easier and that I want, I want, you know, to pass along to make your life easier troubleshooting SQL Server.
Because who knows, someday you’ll say, you know what, that Erik Darling, he sure does a lot of good work. We should, we should hire him just to say thank you. We shouldn’t actually like have him do anything.
We’ll just pay him money, say thank you for all the hard work, and then go about your job with, with great aplomb or something. Don’t worry, I’m not shaking you down. If I were shaking you down, I’d show up at your job, or your house, wherever, or I guess that’s both for a lot of people.
Anyway, it’s Sunday, I got stuff to do. I just wanted to put this out there so that you would be more aware of changes to this store procedure. Happy hunting for blocking, happy, have a lot of fun resolving blocking.
Getting, getting your server in tip-top shape. Remember, read committed snapshot isolation level is the key to resolving most blocking issues. So you should, when possible, use that for your SQL Server databases because it takes care of the most idiotic blocking scenarios that exist in databases when read queries and write queries can block and deadlock with each other.
So really, I can’t, I can’t recommend making that change more than I already do. If you’re on SQL Server 2019 and up, accelerated database recovery is a wonderfully complimentary settings change to use alongside read committed snapshot isolation because you take the, you take tempdb out of the equation.
You use the persistent version store per database to read row versioning information from, which is great. It’s really, really smart move on Microsoft’s part. So you can take all the, you know, the, the worries about tempdb contention.
If you have a lot of databases that you would want to use RCSI, you can take that out of the picture and have them use that information instead. Anyway, I’m going to get going now. I’m going to get off my soapbox chit chatting about this stuff, but enjoy the rest of your hopefully long weekend.
Uh, it’s July 4th coming up for my American friends. We’re going to celebrate our independence from our British friends. I guess it wasn’t always that friendly, but, you know, we’ve got, got a special relationship these days.
Anyway, uh, thank you for watching. I hope you learned something. I hope you enjoyed the free scripts.
I hope you use them. I hope they are useful to you. Uh, and, uh, I think that’s about it. All right. We’re at lucky number 13 minutes now. So I’m going to, I’m going to cut this here.
Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.