sp_HumanEventsBlockViewer: Update Roundup!

Busy Bee

---

I’ve been spending a lot of time lately working on my free scripts. And, I know, I’ve been recording videos about them, but some folks out there like to read things.

In this post, I’m going to talk about a couple cool changes to sp_HumanEventsBlockViewer, a procedure I wrote to analyze the blocked process report via Extended Events, and wish I had given a snazzier name to.

You see, when I wrote it, I pictured it as a utility script for sp_HumanEvents, which will set up the blocked process report and an extended event. But it turns out I use it a lot more on its own.

Go figure.

Well, you live and your learn. Sometimes.

Plans!

---

It used to be that there was a column in the output with SQL handles pulled from the blocked process report, and you could take those SQL handles and run Your Favorite Plan Cache Script to (maybe) find the plans for those queries.

That’s clunky, as a wise man once said. Now, there’s an additional result set with all of the available cached plans related to the blocked/blocking queries.

It will look something like this, and there are many related query execution metrics also returned. It just doesn’t make a good screenshot to capture them all.

Priorities!

---

The findings results section used to only be sorted by the check ID. Through the magic of window functions and aggregates, I’m now also sorting the results by which database/objects/whatever had the highest amount of blocking.

It should look something like this:

The code that handles this is pretty cool, and it’s not something I’ve seen many people do. It’s an aggregate inside of a windowing function, that does something like this:

SELECT TOP (10)
    p.OwnerUserId,
    n = ROW_NUMBER() OVER
        (
            ORDER BY 
                COUNT_BIG(*) DESC
        )
FROM dbo.Posts AS p
GROUP BY
    p.OwnerUserId;

In a normal query, you could just sort by COUNT_BIG(*) DESC to order results, but when you’re putting results into a table to return later, the sorting won’t be preserved.

Assigning a row number to the aggregates means I can sort by check number, and then the row number within each check, to put the worst offenders up at the top.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% 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.