Our new Triage Specialist will be using tools, forms, and methodologies that we build for our SQL Critical Care® clients, but we also make a lot of those tools available to the public for free in our First Responder Kit.

Here’s how to do your own quick health assessment for free:

First, start your health check with sp_Blitz®. It’s a stored procedure that gives you a prioritized list of problems on your SQL Server. If the databases are all third party apps that you can’t control, use the @CheckUserDatabaseObjects = 0 parameter to focus the results on the things you can change. sp_Blitz® works on SQL Server 2005 and newer, but it doesn’t work on databases in SQL Server 2000 compatibility mode.

If you don’t think one of the resulting rows is important, or if you don’t understand the warning, copy the contents of the URL column into your browser and spend a few minutes reading the details. For example, at least once a month, we run across a server having performance problems, and it turns out not all of the CPU and memory is actually available to SQL Server. Sure enough, when we look at sp_Blitz®, it warns about CPU schedulers being offline, and the admin says something to the effect of, “Oh, I saw that in sp_Blitz®’s output, but I wasn’t really sure what it meant because I didn’t read the URL.”

Next, check your server’s bottlenecks with sp_BlitzFirst®. This stored proc was designed for live real-time performance troubleshooting, but it has another cool use. Use the @Seconds = 0, @ExpertMode = 1 parameters, and the second result set will show you your server’s primary wait stats since SQL Server was restarted (or since someone cleared the wait stats.) This helps guide you toward the type of performance bottleneck you’re troubleshooting – is it a CPU bottleneck, storage issue, or locking?

Armed with that knowledge, check your top resource-intensive queries with sp_BlitzCache®. Once you know the kind of bottleneck you’re looking for, use the @sort_order parameters to get to the right queries:

• Looking for high CPU consumers? @sort_order = ‘cpu’
• Tracking down the source of PAGEIOLATCH waits due to queries dragging a lot of data back from disk? @sort_order = ‘reads’
• Wondering who’s getting blocked for long periods of time? Try @sort_order = ‘duration’

Then make the queries more efficient with sp_BlitzIndex®. Now that you know the worst queries and the database they’re in, run sp_BlitzIndex® in that database and get a psychological profile of your indexes.

• Dastardly deadlocks? Look for the Aggressive Indexes warnings about high lock waits on an index.
• Slow selects? Check out the high-value missing index warnings.
• Intolerable inserts? Read the Hoarder warnings about tables that have a high number of nonclustered indexes that aren’t getting used.

We slather trademarks all over these tools because we’re really proud of ’em. We use them every single day in our consulting work, and we want you to use them in your triage work too. You might even wanna just practice using ’em in case we ever ask you to use ’em during our job interviews. (That’s a hint.)