sp_HumanEvents: Humanizing Extended Events

LONDON

---

The next couple days I’m going to publish some written stuff about my new stored procedure, and next week will be blog posts of YouTube videos for those who prefer that format.

Anyway, I wrote this stored procedure to cover some gaps in the First Responder Kit. Namely, that it’s absolutely fantastically great at mining and analyzing data as it exists, but it does not currently have a way to catch things as they’re happening. Yes, sp_BlitzWho can show you queries that are running, sorta like sp_WhoIsActive does. But if you don’t hit F5 at just the right time, you can miss a lot.

sp_HumanEvents is an attempt to cover that gap by allowing you to easily set up short Extended Events sessions to capture specific query problems. Down the line, I’ll invest more time in creating long-running sessions that push data off to permanent tables. For now, I wanted to get something out there for people to use (and find bugs in, ha ha ha) and to solicit other opinions on.

Right now, it allows you to spin up Extended Events sessions dynamically that can track:

• Blocking
• Queries and Plans
• Compiles
• Recompiles
• Wait Stats

A current limitation is that you can only track one thing at a time. That may change in the future; it may not. I don’t want people hauling off and creating monstrous Extended Event sessions and then complaining that performance got worse.

Observer overhead is real.

You’re probably wondering why it doesn’t do anything for deadlocks. Well, I already wrote that one. I didn’t see a good reason to duplicate that work, or introduce a dependency in here. If that’s what you’re after, you already have a tool for it.

Speedway

---

My goal here was to take a lot of the mystery, misery, and guesswork out of how to set up, filter, and get actionable data from extended events. I’m not claiming to be an expert, but I have used them a bit over the years. And I did learn a lot while writing this, too!

I’d been using a similar assortment of “one off” scripts to troubleshoot things, but the last thing I want to do is inundate your “DBA Scripts” folder with another one-off that you’ll forget about. Only a monster would do that to you.

Anyway, the main driver of this proc is the event_type and seconds_sample parameters, which will set up a session to capture what you’re most interested in.

From there, depending on which session you choose, you can filter on query duration, wait types and durations, app or host name, database name, SPIDs, usernames, object names, and memory grants.

Not all parameters are compatible with all session types, but I handle that with dynamic SQL in the background. You don’t have to use all that stuff, but I wanted to add as much configurability as I could without making this proc too hard to use (or write!).

There’s also a pretty extensive “help” section, which details a lot of particulars, and I’ll keep up to date as changes are made. If you’re reading this post way down the line, it may not all be exactly the same. Or who knows? Maybe it’ll be perfect the first time ?

Tomorrow, I’ll go over some example calls and the results.

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 performance problems quickly.