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:
Brief change log:
- v14 – Mar 1, 2015 – added CPU usage checks, bug fixes and improvements.
- v13 – Feb 22, 2015 – added @OutputTable* parameters to persist wait stats, Perfmon counters, and file stats to tables. Great for trending over time.
- v12 – Feb 16, 2015 – added batch requests per second and wait time per core per second to tell you how busy the server is, and how hard it’s working.
- v11 – Nov 20, 2014 – Jefferson Elias added more Perfmon counters. We added @FileLatencyThresholdMS and @SkipChecksQueries parameters, plus bug fixes.
- The URLs for more information about each check aren’t really complete yet.
- No support for SQL Server 2000 or compatibility mode 80.
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 = '2015-02-23 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.
You can also use these parameters to persist additional performance diagnostics over time:
- @OutputTableNameFileStats – contents of sys.dm_io_virtual_file_stats
- @OutputTableNamePerfmonStats – contents of sys.dm_os_performance_counters
- @OutputTableNameWaitStats – contents of sys.dm_os_wait_stats, with common harmless waits filtered out
All three of these DMVs have cumulative data – meaning, if you query them, you’ll see total numbers since the SQL Server was restarted or the counters were cleared out manually. As of v13, doing differentials to trend this data over time is an exercise left for the reader.
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 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.