When someone tells you the SQL Server is slow, what do you do?
- Run sp_who looking for queries that might be blocking someone
- Check the SQL Agent jobs to see if there’s a backup job running
- Fire up Activity Monitor looking for problems
- Remote desktop into the server to look at CPU use
- Open Perfmon to check your favorite metrics
- Run a wait stats sampling query looking for the biggest bottleneck
That’s all a lot of work, and I’m lazy. So right now, as we speak, I’m onstage at the PASS Summit unveiling a single stored procedure that does all that in ten seconds, plus more.
You already know how our free sp_Blitz™ gives you a prioritized list of configuration and health problems on your SQL Server. Now when your SQL Server is slow, you can find out what’s going on just by asking Brent – with sp_AskBrent™. Here’s how it looks:
In this example, I’ve got three problems, and I can click on links to get more details about the specific finding, how to stop it, and the query text.
I can also turn on Expert Mode and see some of the raw data that sp_AskBrent™ checked for diagnostics. Here’s Expert Mode turned on with a server having a whole mess of problems:
It returns results including:
- Which wait stats were the biggest during the last five seconds
- Which data and log files are getting the most reads & writes, and how fast they’re responding
- Which queries consumed the most resources during the sample
Using the @OutputDatabase parameters, you can also log the results to table. This means you can set up a SQL Agent job to run it every few minutes, and then when someone wants to know why the server was slow last night, you can run it with the @AsOf parameter to get the results as of a past date and time.