Recent Improvements to sp_HumanEventsBlockViewer
Video Summary
In this video, I delve into the recent enhancements to SP Human Events Block Viewer, a powerful tool designed to parse and analyze block process report XML data in SQL Server. I explain how you can now read from a target table or log the fully parsed results to another table, offering greater flexibility and utility for managing blocked processes. Additionally, I demonstrate the new parameters added to the stored procedure, such as specifying a timestamp column for filtering and logging results to a designated table with retention settings. This update not only makes SP Human Events Block Viewer more versatile but also highlights its importance in diagnosing and resolving blocking issues efficiently.
Full Transcript
Erik Darling here with Darling Data, and in today’s video we are going to talk about recent improvements to SP Human Events Block Viewer. I guess you could say everything is improved except the name. So if you are unaware, this started off as a companion script to SP Human Events. That’s why it’s called SP Human Events Block Viewer. I originally wrote it to look at the block process report XML in a more refined way than just creating a view to look at it. And then it just took off and became a world of its own and it is, I think, one of the handiest tools in my set of procedures because if you have a block process report extended event, this will parse the heck out of it and give you all sorts of great information about what went on during your blocked processes. What we added in, what I added in this one was a couple things. One is the ability to read from a target table. So if you store the block process report XML in a table somewhere, like just like the raw XML, you can point this to that table and parse it out. And you can also log my fully parsed thing to a table and you can even do both. It’s kind of cool. So let’s talk a little bit about this stuff before I get started.
Before we get into the store procedure. If you appreciate my SQL Server efforts and you want to say thank you with money, you can sign up for a membership down in the video description. If you have no money, you can like, you can comment, you can subscribe and you can even ask me questions for free for my office hours episodes. So all sorts of good things afoot there. If you are looking for a SQL Server consultant, again, Beer Gut Magazine has rated me the absolute tip top SQL Server consultant number one. One in the world outside of New Zealand and only one specific part of New Zealand. It’s not all of New Zealand. There’s like one address in New Zealand. If you would like some high quality SQL Server training, I’ve got that as well. You can get it for 75% off. That’s about 150 US dollars after you apply that discount code. Again, all the links for this stuff are down in the video description. SQL Saturday, New York City, May the 10th, 2025. It’ll be fantastic. I will be there. We can hang out. We can high five. We can talk about all my awesome SQL Server store procedures or whatever. The weather will be nice by then. So we can we can chat about that. I don’t like chatting about bad weather. It’s not fun.
With that out of the way, though, let’s talk about SP human events block viewer, the store procedure whose name always precedes itself. So there are two sets of new parameters that got added in here. There are these right here. These are if you want to read the block process report XML from a table. So target database schema table, the target column that has the XML data in it. And then there’s an optional one called timestamp column. If you if you if you’re if you’re the column you put the XML into has like an insert date.
And you want to filter on that rather than filtering on what’s in the XML. You can do that. And then the other set of columns that I have in here, zoom and cooperate are these ones to log to a table. So if you set log to table equals one, and I don’t know why this turned a different color on me, let’s just overlay that. Let’s see. Let’s see how this goes. That looks kind of weird, kind of funky, like 80s computer vibe to it. If you set this to one, you will log the results of this thing to a table.
You have to provide it with a database, a schema, and this is a default value for this. I believe the default if you leave the schema blank, it’ll default to DBO. And then you can also set a retention in number of days for how long you want to do that.
And this will delete data older than that date in whatever table you’re logging to. So two things to show you there that will be lots of fun. So with all my store procedures, there is a help parameter that will give you all of the information you need about what the script does, some examples of how to run it, and what all of the parameters do.
So all good stuff for you to learn about in there. So let’s say that I terminate my SQL Server queries correctly. And I’ve got this table called block process report.
And I’ve got the block process report XML stored in this column. And I’ve got a insert date column called insert date right there. I can run.
I’m going to run this with debug just so we can all see what happens. So you can see the magic behind the things. And we can run this. And what we’ll get back for like the main result set, the non debug result set, will be this thing in here, which will be the fully parsed out block process report that I promised you. So this is the main result set.
But if we just do this without debug, it’ll be just these things. So the main results for this are the block process report fully parsed out, any available query plans from the block process report XML. If you scroll down a little bit in any of these sections, you get these SQL handles.
And I’ll go and look in the plan cache for those SQL handles. And then down here, there is a roll up with all the stuff that you might care about that happened during the block process. During the block process, as you can see, for the one block process that’s in here, there’s the query that was blocking it was sleeping.
So that query took it decided to take a little nap. Didn’t want to didn’t want to finish running, didn’t want to complete itself and blocked another query. So you can if you’re storing the block process report XML off in a table somewhere, this is a great way to read it.
Now, if you want to log stuff to a table, there’s really only one result set worth logging to a table. And I do that. So if we run this, again, this is with debug mode on this.
So this returns all like the debug output. But if you look over in the messages tab, this will give you the dynamic SQL results as well. And so just like with if you watch the SP health parser video, one thing that I do here is make sure that we’re not putting the same data in over and over and over again.
So I find the max event time or like the oldest date or the newest date in the in this table. And then I filter based on that. So you will only see the freshest, newest things in here.
So that’s a good time. And I suppose what’s we should go look and make sure that table got created and has stuff in it. And if we select the top thousand rows out, we will see just about what we saw in the results before that XML gets fully parsed.
And then what’s really nice, actually, this is probably overkill, is that if you use SP human events block viewer to log stuff to a table, you can even point SP human events block viewer at that table and reparse the XML because it’s not already done for you or something. Anyway, I suppose that the upside of this is that you will get the findings back for everything because I don’t log the findings to a table. Well, that would be incredibly annoying and repetitive.
So anyway, that’s fun. So, yeah, SP human events block viewer name still a lot to type, but it’s doing a lot more stuff, cool stuff now. So I hope that you will actually I hope you turn on RCS and you have very little use for it.
But if you don’t if you don’t have recommitted snapshot isolation turned on, if you are still suffering under the yoke of SQL Server’s default recommitted isolation level, which is awful and terrible and innumerable ways, then this will probably be very handy for you because those those no lock hints don’t always save you. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. I hope you will use SP human events block viewer and maybe even start logging it to a table and maybe use it as ammunition to convince someone to turn on recommitted snapshot isolation to reduce all of these blocking problems that you currently have. So that’ll be a good time.
Won’t it? Won’t it then? 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. 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.