sp_AskBrent® Helps You
Troubleshoot Slow SQL Servers.
I kept getting emails and phone calls that said, “The SQL Server is running slow right now, and they told me to ask Brent.” Each time, I’d have to:
- Look at sp_who or sp_who2 or sp_WhoIsActive for blocking or long-running queries
- Review the SQL Server Agent jobs to see if a backup, DBCC, or index maintenance job was running
- Query wait statistics to figure out SQL Server’s current bottleneck
- Look at Perfmon counters for CPU use, slow drive response times, or low Page Life Expectancy
That’s too much manual work – so I wrote sp_AskBrent® to do all that in ten seconds or less. Here’s how it works:
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:
- v11 – Nov 20, 2014 – Jefferson Elias added more Perfmon counters. We added @FileLatencyThresholdMS and @SkipChecksQueries parameters, plus bug fixes.
- v10 – May 22, 2014 – added Opserver output mode, thresholds for resource-intensive queries, filters for specific database ids, and more.
- v9 – Nov 3, 2013 – fixed bug in the date format for British users.
- v8 – Oct 21, 2013 – fixed bug in check 8 on SQL 2005 servers.
- v7 – Oct 21, 2013 – performance tuning check 8 (sleeping queries with open transactions), updated links to more info for some checks.
- v6 – Oct 11, 2013 – 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 user based on that certificate, grant SA permissions to the user, and then sign the stored procedure and let the public run it:
USE master; GO CREATE CERTIFICATE sp_AskBrent_cert ENCRYPTION BY PASSWORD = 'Get lucky' WITH SUBJECT = 'Certificate for sp_AskBrent', START_DATE = '20130711', EXPIRY_DATE = '21000101'; GO CREATE USER sp_AskBrent_login FROM CERTIFICATE sp_AskBrent_cert; GO GRANT EXECUTE ON dbo.sp_AskBrent TO sp_AskBrent_login; GO GRANT CONTROL SERVER TO sp_AskBrent_login; GO ADD SIGNATURE TO sp_AskBrent BY CERTIFICATE sp_AskBrent_cert WITH PASSWORD = 'Get lucky'; GO GRANT EXECUTE ON dbo.sp_AskBrent TO [public]; GO
Now anybody can run sp_AskBrent even if they don’t have SA-level permissions.
Note: whenever you update sp_AskBrent, you’ll need to reassign the permissions by adding the signature again:
ADD SIGNATURE TO sp_AskBrent BY CERTIFICATE sp_AskBrent_cert WITH PASSWORD = 'Get lucky'; GO GRANT EXECUTE ON dbo.sp_AskBrent TO [public]; GO
Going Back in Time to See Past Bottlenecks
sp_AskBrent® can log results to a table if you use the @OutputDatabase* parameters like this:
EXEC sp_AskBrent @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'AskBrentResults'
If the table doesn’t exist, it will get created – otherwise the new rows just get added to the end of the table. Set up a SQL Server Agent job to run that every X minutes, and then you can do this:
EXEC sp_AskBrent @AsOf = '2013-10-15 18:45', @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'AskBrentResults'
sp_AskBrent® will look in the output table for all results within 15 minutes of that time, and return them in chronological order. That way, you can see why the server was slow last night – or if it was operating normally.
Note that sp_AskBrent® does not clean out past records for you, so I’d suggest doing a truncate-table job also against that same table once a week. Since it can contain execution plans and queries, the data can get pretty big quickly.
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:
- Your exact SQL Server version number (you can see it next to the server name in SSMS Object Explorer, like 10.50.4000)
- The exact error copy/pasted from SQL Server when you try to run it or compile it
- Anything about your server that might be unusual (case-sensitive, unusual collation, strange database names, etc) and might be related to the error
More Tools for Slow SQL Servers
sp_Blitz®: Free SQL Server Health Check – You’ve inherited a SQL Server from somebody, and you have no idea why it’s slow. sp_Blitz® gives you a prioritized list of health and performance issues, plus gives you URLs for more details about each issue.
Our Free 6-Month DBA Training Plan – Every Wednesday, you get an email with our favorite free SQL Server training resources. We start at backups and work our way up to performance tuning.
SQL Critical Care® – Don’t have time to learn the hard way? We’re here to help with our quick, easy 3-4 day process that gets to the root cause of your database health and performance pains. Contact us for a free 30-minute sales consultation.