SQL Server Community Tools: Capturing Blocking With sp_HumanEvents

Month Of Sundays

---

A while back, Brent blogged about using September to raise awareness about free SQL Server community tools.

I thought this was a great idea because I maintain a number of them: my own scripts, the First Responder Kit scripts, and sp_WhoIsActive.

Unlike most people who left links in the comments, I read the entire post and decided to use the whole darn month to write about scripts I maintain and how I use them in my work.

Lest I be accused of not being able to read a calendar, I know that these are dropping a little earlier than the 1st of September. I do apologize for September not starting on a Monday.

There are other great tools and utilities out there, like Andy Mallon’s DBA Utility Database, but I don’t use them enough personally to be able to write about them fluently.

My goal here is to help you use each script with more confidence and understanding. Or even any confidence and understanding, if none existed beforehand.

Oral Board

---

First up is (well, I think) my most ambitious script: sp_HumanEvents. If you’re wondering why I think it’s so ambitious, it’s because the goal is to make Extended Events usable by Humans.

At present, that’s around 4000 lines of T-SQL. Now, sp_HumanEvents can do a lot of stuff, including logging event data to tables for a bunch of different potential performance issues.

When I was first writing this thing, I wanted it to be able to capture data in a few different ways to fit different monitoring scenarios. In this post, I’m going to show you how I most often use it on systems that have are currently experiencing blocking.

First, you need to have the Blocked Process Report enabled, which is under advanced options:

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'blocked process threshold', 5;
RECONFIGURE;

If you want to flip the advanced options setting back, you can. I usually leave it set to 1.

The second command turns on the blocked process report, and tells SQL Server to log any instances of blocking going on for 5 or more seconds. You can adjust that to meet your concerns with blocking duration, but I wouldn’t set it too low because there will be overhead, like with any type of monitoring.

Blockeroos

---

The way I usually set up to look at blocking that’s currently happening on a system — which is what I most often have to do — is to set up a semi-permanent session and watch what data comes in.

When I want to parse that data, I use sp_HumanEventsBlockViewer to do that. At first, I just want to see what kind of stuff starts coming in.

To set that session up, here’s what I do:

EXEC sp_HumanEvents
    @event_type = 'blocking',                   
    @keep_alive = 1;

What this will do is set up an Extended Event session to capture blocking from the Blocked Process Report. That’s it.

From there, you can either use my script (linked above), or watch data coming in via the GUI. Usually I watch the GUI until there’s some stuff in there to gawk at:

EXEC dbo.sp_HumanEventsBlockViewer
    @session_name = 'keeper_HumanEvents_blocking'

Blockerinos

---

Once you have the blocking collected, troubleshooting it is a whole… Fun… thing.

• Misguided use of transactions
• Misguided use of isolation levels
• Misguided use of locking hints

Are common reasons behind blocking, but not always. Common solutions are things like:

• Making sure foreign keys have supporting indexes, especially ones with cascading actions
• Getting angry at Triggers and throwing your computer out the window
• Making sure modification queries have adequate supporting indexes
• Batching modifications on large chunks of data
• Enabling an optimistic isolation level because Read Committed Is A Garbage Isolation Level

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.