Giveaway
I’ve been doing a lot of work on all of my free SQL Server troubleshooting stored procedures lately.
If you haven’t used them, or haven’t even heard of them, now’s a good time to talk about what they are, what they do, and some of the newer features and functionality.
I hate to be one of those “bug fixes and performance improvements” people, but I am really happy with the way a lot of these are working.
It’s not that I’m not constantly looking for ways to improve, it’s just that for a lot of them there aren’t really “new” things I can go find and show you, or new ways to look at old things that I think would be helpful.
New things tend to come with new versions of SQL Server. If you don’t like it, tell Microsoft to ship more often. Ha ha ha.
For a full overview of what all of them do and all that, head to the README file.
For everyone else, feel free to follow along.
Fresh Install
I used to get really annoyed with myself, because it took a lot of clicking to install or update all the scripts.
Now, thanks to GitHub magic (actions), and a world-renowned super genius (Drew Furgiuele b|t, who also has an awesome podcast that you should listen to), you can get all updates in a single script.
Because I’m forgetful, I wanted this to happen every time I merged into the main branch without me having to remember to rebuild the main file.
That’s what this whole thing does.
What a gem, that Drew.
sp_HealthParser
I’m going to start by reintroducing a couple of the newer procedures that I released leading up to my PASS Precon.
The first one is sp_HealthParser, which digs deep into the system health extended event for all sorts of gruesome details about what’s going on in your SQL Server.
I’d been meaning to do this for a long time, and finally had the time and patience to write all the necessary XQuery to do it. Once I started writing it, I started kicking myself for not doing it sooner.
There’s so much great information in there:
- Queries with significant waits
- Waits by count
- Waits by duration
- Potential I/O issues
- CPU issues
- Memory issues
- System health (duh) issues
- Blocking
- Deadlocking
The really cool thing about the wait stats is that they get logged in a way that you can see them over time, and see when your system was busy.
The most common way I execute it is like so:
EXEC dbo.sp_HealthParser
@warnings_only = 1,
@skip_locks 1;
There are a number of sections in there that have a column which will flip to WARNING when something happens that your SQL Server doesn’t like, and a lot of the time I wanna focus in on just that.
I also usually skip the blocking and deadlocking sections, in favor of setting up specific extended events to monitor them.
Also because the blocked process report that it captures is missing a lot of details that the real blocked process report has in it.
That being said, if you’re looking at a server for the first time, you may want to see what’s in there.
sp_LogHunter
Another newer procedure is sp_LogHunter. The idea here is that SQL Server’s error log is a horrible window into your server’s soul, but searching through it is a nightmare.
- You can only search for a single thing at a time
- The results usually exclude a lot of useful information around any hits for your search
- Often using the GUI is incredibly slow and involves a lot of clicking
What I wanted was a stored procedure that looked for a whole bunch of the awful stuff I’ve seen in error logs over the years, and the results to get returned in chronological order, all together.
That’s what this does. There are a bunch of configuration options, but I usually just fire it off to look through the last 7 or so days of log data. But you can choose specific time windows, look for custom message entries, and more.
EXEC dbo.sp_LogHunter;
sp_HumanEvents
Extended Events are great, but for all the XQuery you have to write to get data back in something that isn’t the cursed-by-old-and-new-gods GUI.
One of the hardest stored procedures I’ve ever written, sp_HumanEvents, does all that for you. Not for every extended event, but for the ones I found myself using most often with clients.
- Query performance
- Wait stats
- Compiles
- Recompiles
- Blocking
This thing can do a ton of different stuff, but the most common thing I do with it is to troubleshoot slow stored procedures that clients have me working on.
In SSMS, there’s no way to only return some query plans, and a lot of stored procedures I end up working on have a lot of queries in them (some very fast, some very slow), and I want to focus on the slower ones.
To get set up to do that, this is what I run:
EXEC sp_HumanEvents
@event_type = 'query',
@query_duration_ms = 1000,
@session_id = N'58',
@keep_alive = 1;
That will create an extended event that monitors a single session for any queries that run over 1 second.
From there, all you have to do is watch live data, and then run the procedure to get a ton of detail about them.
sp_HumanEventsBlockViewer
For years, I’ve been working on sp_BlitzLock to troubleshoot deadlocks in SQL Server. One day, after going through a lot of yuck to do the same thing with the blocked process report, I decided to write sp_HumanEventsBlockViewer.
It was originally intended as a companion script to look at the blocked process report extended event that sp_HumanEvents sets up, but it quickly became its very own island unto itself.
Now, you do need to enable the blocked process report, and you do need to set up an extended event target to capture it, but once you do the amount of detail you can get about blocking problems on your server is incredible.
To see how to do that, check out the README file. Once that’s done, just point this baby at it and let’er rip. Not RIP 🪦
EXEC dbo.sp_HumanEventsBlockViewer
@session_name = N'blocked_process_report';
sp_PressureDetector
sp_PressureDetector, at least according to Google’s search reports, is my most popular script.
This one got a lot of work before my PASS Precon. Not to add stuff to it really, but to make the results as clear as possible.
The idea behind this one is to show you resource pressure currently happening on your SQL Server, and when possible, queries that are causing it.
It will also give you a lot of good information about the server itself, like how much data is stored on it, and configuration details that most often matter for hardware.
You don’t have to do much else with it aside from hit F5.
EXEC dbo.sp_PressureDetector;
sp_QuickieStore
An ode to the dismal shortcomings of the Query Store GUI, sp_QuickieStore comes to the rescue.
By default, it gives you the 10 worst queries from the last 7 days by average CPU. There are about 100 ways to search for, include, and exclude certain queries from the results.
Seriously. Just look at all the parameters for that. You can find what hurts, research a stored procedure, search across all databases with Query Store enabled, and way more.
The thing that I kept running into, though, is that the default results would often include a lot of results that weren’t pertinent. Overnight problems and whatnot.
For years I’ve been saying “anyone can find a slow query”, because that’s really easy. But you need to find the queries that matter to you.
A little before Christmas, I decided I should live by my own rules, and added a few new parameters.
Here’s how they work:
EXEC dbo.sp_QuickieStore
@workdays = 1,
@work_start = '9am',
@work_end = '5pm';
When you use these, it will only show you queries that happen on weekdays (respecting local DATEFIRST settings), with configurable time frames.
By default, it look from 9am to 5pm, and those hours will automagickally convert from your local time zone to UTC (because that’s how Query Store stores dates). But you can adjust that to your needs.
If you want to find everything that happens every weekday morning, afternoon, or all day, you can do that now.
Time Flies
The first commit I made to sp_PressureDetector was back in December of 2019. Looking at the code now is a touch sentimental, and more than a touch embarrassing.
My formatting game, along with the small amount of details returned, aren’t really anything I’m psyched about. But I’m very proud of what these have all turned into.
I get decent traffic to my repo. There’s a dbatools cmdlet to install my scripts. I get nice emails from folks out there who have found and solved problems with them, and I’ve solved hundreds of client problems with them.
It may all sound a little braggy, and I’m totally okay with that. I’m super proud of them these days.
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.
