Monday, October 21, 2013

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 Ozarsp_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...  :/ )

No comments: