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