SQL Server Consulting, Education, and Training
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.
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.
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.
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.
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.
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.
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.
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';
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!
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.
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:
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.
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!
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.