When you’ve got the symptoms of a database issue you can run a series of diagnostic queries to try and drill down on the problem and then start figuring out solutions. There are a number of free tools out there though that can speed up that process immensely. In this post we’re going to look at my favourites.

In no particular order…

sp_WhoIsActive

sp_WhoIsActive by Adam Machanic is great for the situation where you have a problem right now. It’s versatile and supports a number of parameters for execution but most of the time I run it with just the defaults and it tells me what I need to know. The resultset returned shows you what is executing on your SQL Server without any noise from idle connections etc.

From the output you can quickly identify long running, or CPU consuming queries. You can also identify queries whose execution is blocked and analyse the blocking chain to find the root blocker.

sp_WhoIsActive is a stored procedure that you install on your database instance. Either in the master database, or another database of your choosing.

You can download it and find the documentation here.

Query Store

Query Store is built in functionality that has existed in SQL Server since 2016. Once enabled it captures query execution information over time so you can see what was running in a particular time window, look at what are your most resource intensive queries, as well as identify regressed queries (queries that were good, but have gone bad). You can also use it to “fix” some issues by forcing a known good execution plan – and in SQL Server 2022 you can use it to force query hints too.

I’ve blogged a fair bit about Query Store and my Introduction to SQL Server Query Store post is one of my most popular posts. Give it a read if Query Store is unfamiliar to you.

Query store isn’t going to help you if you haven’t turned it on, and it’s a setting you should consider as default for each database. From SQL Server 2022 it is enabled automatically.

The only downside with Query Store is that it is a per-database view and if you have a lot of databases it won’t easily help you determine which one a problem is coming from. That’s why I wrote the script Identify the (Top 20) most expensive queries across your SQL Server using Query Store.

Paul Randall’s Wait Stats script

This is a great script for identifying what exactly is troubling your SQL Server. Is it CPU, is it Parallelism, is it blocking or memory etc. It analyses the aggregated waits (what SQL Server has been waiting on) since the last server restart. This sort of analysis is often the first thing you should be looking at when you have a problem. Paul used to work for Microsoft in the SQL Server team and is one of the foremost experts on all things SQL Server. You can get the script here as well as find comprehensive documentation on most of the wait types you are likely to find.

Brent Ozar’s First Responder Kit

Originally written by Brent Ozar (he has since open-sourced it) the First Responder Kit covers a lot of ground. It’s a set of stored procedures you can use in different scenarios. Like sp_WhoIsActive, you can install them in the master database or another database of your choosing. The most useful for problems that you are in the middle of is sp_BlitzFirst which encapsulates some of the other items above. It includes:

• Identifying currently long running, high CPU or blocked queries.
• Analysing wait stats.
• Checking for currently executing SQL Server agent jobs.
• Looking at perfmon counters.

As it covers a number of areas it is (as the name suggests) what you should consider running first when you have a problem.

Another very useful stored procedure in the toolkit is spBlitz which gives an overall healthcheck of your server and is a useful starting point for identifying all sorts of things that are going wrong or aren’t correctly configured.

You can get the First Responder kit here.

Need Help?

Got a SQL Server performance problem and need some assistance? Get in touch today to discuss how I can help you.