sp_HumanEventsBlockViewer: Walk The Block Chain (Video Edition)
Video Summary
In this video, I showcase a fantastic code contribution from one of the brightest minds in the database world to my stored procedure, SP_human_events_block_viewer. Michael’s contribution adds an incredibly useful feature that allows users to walk through blocking chains and see the full results of blocked queries—essentially providing a visual representation of which query is at the head of the blocking issue and how it affects downstream processes. This enhancement makes troubleshooting SQL Server blocking issues much more intuitive and user-friendly, especially for those who frequently deal with complex blocking scenarios. I also discuss some technical details about the implementation, including the addition of computed columns and indexes to optimize performance, as well as a design choice that ensures the feature remains efficient even when dealing with long blocking chains.
Full Transcript
Erik Darling here with Darling Data. And in this episode, I’m going to show you a very, very cool code contribution that the, I don’t know, probably one of the smartest folks I know in the database world made to my store procedure, SP underscore human events block viewer. I thank all of the powerful and mighty forces in the universe that Michael was patient enough to type out that entire store procedure name. And of course, the code that does the cool thing in the store procedure. Now I’m going to walk you through a little bit of it because I think it’s really interesting. If you don’t think it’s interesting, you can skip ahead a little bit, whatever you want to do. But what the code does is allows a human events block viewer in the output to walk the blocking chain. So you can see the full results of like this query block that query. So you can see the full results of like this query and all down until we run out of, run out of room. So to do, to implement that, and Michael has experienced implementing this because he also has a stored procedure that can be used to look at the block process report.
That, that, that was a bit of an inspiration to me when, when I decided to write this to, you know, work with the, the tooling that I, that I care about more specifically. So both of the temp tables that get, they get added to deal with the blocked and blocking processes will get, have a computed column added that adds some information about blocking and blockade spids. So we, because we, because we care very much about your, your experience with these stored procedures. We, we added some indexes to speed up the sort of the stuff that needs to happen in order to walk the blocking chain. There’s an update to the table. So this is the sort of the hierarchy walking, the recursive CTEto do the walking that Michael wrote.
Does, does, does the walking of things here and then updates the table with blocking details there. And then we go back and we make sure that the blocking table is also updated with the block level in, in sort order so that we know what should be at the top and who and how things sort of enumerate down the path. There’s also a little bit of code that got added to this query, some case expressions in the, some case expressions in this part of the query that tell this output, which column to use and when.
And then, and then, and then I think that’s just about it for what we needed in there. But then the results are very cool. Bring this up.
And, uh, I ran this with debug just to get some additional information while this was running to make sure that everything was cool and calm and collected. But, uh, here in this block process report table, we now have this column called blocking tree. And this is where you will see the materialized path that gets ginned up by Michael’s super cool code contribution to this door procedure.
You still get all the other great results back that you’d expect from this, uh, information about the blocking process all laid out for you, uh, along with a full block process report in case you want to use that to ask a question on your favorite Q and A site. You want to send it to a young, good looking, handsome SQL Server consultant to, to fix your problems. Uh, and I don’t know any one of those things I guess would be a pretty sane, valid use for that.
Um, and I, so while I was working on this, uh, there, there was some question in my mind about whether this would be useful for, um, the, uh, store procedure that I work on a lot over in the first responder kit, SP Blitzlock. Uh, but, uh, it just, it didn’t seem as, uh, as easy to retrofit this code over there for various reasons. It’s just set up a lot differently than the block process report stuff is.
So, uh, I don’t think I’m going to be able to port this over there in a simple way. So you’re just going to have to kind of deal with that. Um, those results are already pretty, pretty self-explanatory there.
So I’m not terribly concerned about, about that. So, you know, it’s probably not that big a deal that it’s not going to make it over there, but I’m really, really psyched, really, really excited to have this, uh, over in SP underscore human events block viewer. So that, uh, when you are troubleshooting the blocked process report data in SQL Server, uh, you can more easily see which query was at the head of the blocking issue, which queries were blockaded by that, which queries, those block, those queries that got blocked were blocking downstream should be really good.
Um, there was a design choice made in this, uh, if we go back up to the computed column that got added to the table, um, right here. Uh, I’m sorry, where the, uh, it’s up a little bit further. Uh, so like the columns get added to the table, uh, are added.
Some of them have a default value of like VARCAR 400. If you run into any truncation issues because your blocking chains are so long that they end up being more than 400 characters long, uh, that’s where you’d want to look. Um, just, or, you know, file an issue over on GitHub.
Let me know, uh, that, that that happened and we can look at maybe expanding that column a bit. Uh, I want to, I wanted to avoid having that column be a VARCAR max because then we wouldn’t be able to index it. And I found that when it was a VARCAR max and I was testing things out, you know, something that I’ve talked about both on my blogs and my videos, when, uh, you have a parameter, a variable, or a column that is a max data type and you need to do any filtering on that column, whether it’s a join or a where clause, then, uh, SQL Server needs a filter operator later in the query plan.
It can’t push that down to when it touches the table or index. And so that, that, that gave me the, the opposite of the warm and fuzzies, uh, when I was sort of, uh, going through the initial code for this. But Michael, uh, being as smart and Canadian as he is, has assured me that 400 characters is a, is a lot of blocking chain information.
So we’re going to stick with that for now. Uh, if we run into any issues with that, we can, we can blow it up a little bit, but, you know, there is a limit to, um, you know, how, what, what the, how, how big the key of an index can be. So I’m going to try to keep it, try to keep it slim.
Anyway, uh, brand new in the code. Uh, if you have it, if you haven’t updated SP human events, block viewer in a while, I would encourage you to do so. There’s been a lot of, uh, changes to it.
A lot been doing a lot of work on it lately to make it better, uh, both for myself and the consulting that I do. And for people out there who, uh, trust, trust these free store procedures that I make to, uh, to do their jobs as well. Um, you know, it’s, it’s, it’s funny.
I was thinking about like, uh, I was, I was walking down the street, uh, in New York, uh, a common thing that you’ll see on sidewalks is a, is a pile of someone’s junk outside an apartment. And it’s just like free stuff. Take me.
And I was like, man, who would ever want to bring free stuff into their house? Like, cause like, it’s always crap. Like, it’s like, like, like books about windows XP and stuff. And like, you know, like, like, like someone died and left out like all their Danielle Steele novels and the yellow and smell weird.
And then I was like, wait, that’s sort of like open source software. And then I said, I should probably not say that about free stuff anymore because I made free stuff and I hope that, uh, uh, I’m lucky enough to have you bring your, bring my free stuff into, into your SQL servers. Which are, uh, probably decidedly more expensive than that stack of Danielle Steele novels.
I’m a little too scared to open them to see if any of them are autographed. Again, Danielle Steele is, uh, quite the, quite the romance novelist. So, you never know where those books might end up.
Drop, dropped in the bath or something. Anyway, uh, thank you for watching. Uh, if you enjoy my store procedures or, uh, my, uh, YouTube video content. I would encourage you to subscribe to the channel.
Um, I’m ambivalent about whether you like this video or not. Uh, this is, this is a video to show off something very cool that someone contributed to one of my store procedures. So, if you don’t like it, you’re a real grinch.
You’re a scrooge. It says more about you than it does about me. So, I hope you like it and I hope you choose to like the video via the thumbs up sign. But if you don’t, you should keep it to yourself because you should not insult Canadian.
They will have nefarious ways of getting revenge on you, like setting wildfires and turning everything to the south of them into an orange haze for weeks on end. Careful with that. Don’t mess with Canadians.
They’ll assassinate you with, with, with, with wildfire dust. Anyway, thank you for watching. And, uh, I’m gonna record another video today. Uh, because the wise man once said, The blogs must flow.
Anyway, thank you 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.