sp_AskBrent - Your new, "OMG, my SQL Server is sooo slow" free uber SP from Brent Ozar

Brent Ozar - Announcing sp_AskBrent™ for Troubleshooting Slow SQL Servers

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:

...

Brent Ozar -  sp_AskBrent™ Helps You Troubleshoot Slow SQL Servers.

...

Remember that it’s alpha-quality. It may explode at any time. Keep small children away from sp_AskBrent™ at this time. Do not taunt sp_AskBrent™.

Brief change log:

• v7 – Oct 21 – performance tuning check 8 (sleeping queries with open transactions), updated links to more info for some checks.
• v6 – Oct 11 – launched at PASS Summit, added @AsOf parameter to go back in time.

Known issues:

• The URLs for more information about each check aren’t really complete yet.

How to Grant Permissions to Non-DBAs

To grant permissions to non-SA users, check out Erland Sommarskog’s post on Giving Permissions through Stored Procedures - specifically, the section on certificates. The below example follows his examples to create a certificate, create a login based on that certificate, grant SA permissions to the user, and then sign the stored procedure and let the public run it:

...

Going Back in Time to See Past Bottlenecks

sp_AskBrent™ can log results to a table if you use the @OutputDatabase* parameters like this

...

If Bad Things Happen to Good Stored Procedures

If sp_AskBrent™ doesn’t compile or if it throws an error when it runs, please contact us and include:

...

This free SP is pretty much a monster SP. How monster? Here's it at 10% zoom

Yeah, monster. How much? Free! (Friendly Reg-ware). Will have to share this at work tomorrow (Because I know not many of you there subscribe to my feed, no matter how often I whine about that... hum... I wonder if my whining is turning them off from subscribing? NA! Never! They love my whines! I'm sure it doesn't bug anyone!... um... right...  :/ )