sp_HumanEvents: Usage and Guidelines

Yessaduh

---

I talked a little about why I wrote this proc. In a nutshell, I think it’s absurd to expect people to adopt something that’s hard to use, and then wonder why no one’s using it. In general, I think SQL Server users deserve far better tools than we get. I understand that it’s difficult to balance “ease of use” with “depth of use”, but after 12+ years Extended Events is still a stink bomb for most people to untangle.

One thing I want to make very clear: If you’re reading this in March-ish of 2020, this is V1-ish of the public code. I expect people to find bugs and incompatibilities. I expect people to want additional features. If you fall into one of those categories, please open an issue on GitHub. Even if you have code you want to submit, open the issue first so I can track it easier.

The thing is, you probably have to use this in order to test it. So let’s make that easy for you.

First step: get it and install it.

I Know You’ve Got Problems

---

If you’re here, you’ve likely used some of the Blitz scripts over the years. They may have diagnosed some issues that were tough to really track down, too.

sp_BlitzIndex may have told you about Aggressive Locking, sp_BlitzCache may have told you about queries with high duration and low CPU, sp_BlitzFirst may have warned about compiles or recompiles, and so on. Now you’ve got a way to try to find those. You still need to be looking at the server, but it does make the process a little bit easier.

Here are some example calls to get you started.

To capture all types of “completed” queries that have run for at least one second, for 20 seconds, from a specific database

EXEC dbo.sp_HumanEvents @event_type = 'query', @query_duration_ms = 1000, @seconds_sample = 20, @database_name = 'YourMom';

Maybe you want to filter out queries that have asked for a bit of memory:

EXEC dbo.sp_HumanEvents @event_type = 'query', @query_duration_ms = 1000, @seconds_sample = 20, @requested_memory_mb = 1024;

Or maybe you want to find unparameterized queries from a poorly written app that constructs strings in ugly ways, but it generates a lot of queries so you only want data on about a third of them.

EXEC dbo.sp_HumanEvents @event_type = 'compilations', @client_app_name = N'GL00SNIFЯ', @session_id = 'sample', @sample_divisor = 3;

Perhaps you think queries recompiling are the cause of your problems! Heck, they might be. Have you tried removing recompile hints? ?

EXEC dbo.sp_HumanEvents @event_type = 'recompilations', @seconds_sample = 30;

Look, blocking is annoying. Just turn on RCSI, you goblin. Unless you’re not allowed to.

EXEC dbo.sp_HumanEvents @event_type = 'blocking', @seconds_sample = 60, @blocking_duration_ms = 5000;

If you want to track wait stats, this’ll work pretty well. Keep in mind “all” is a focused list of “interesting” waits to queries, not every wait stat.

EXEC dbo.sp_HumanEvents @event_type = 'waits', @wait_duration_ms = 10, @seconds_sample = 100, @wait_type = N'all';

Note that THREADPOOL is SOS_WORKER in xe-land. why? I dunno.

EXEC dbo.sp_HumanEvents @event_type = 'waits', @wait_duration_ms = 10, @seconds_sample = 100, @wait_type = N'SOS_WORKER,RESOURCE_SEMAPHORE';

C’mon Turn Me On

---

There’s a lot of possibilities here! If you think I’ve missed something, or if you run into an issue, let me know on GitHub. My goal is to make this nice and easy for people to use to find and fix problems with their SQL Servers.

If you need help with that, drop me a line. I do happen to enjoy my job.

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.