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. There’s a short video at right showing how to use it.
Brief change log:
- v19 – October 6, 2015 – if @OutputTable* parameters are populated, we also create a set of views to query the output tables. The views have the same database/schema/name as the output tables, and add _Delta as a suffix.
- v18 – September 11, 2015 – bug fixes. No improvements, just bug fixes.
- v17 – July 19, 2015 – improving Azure SQL Database compatibility. Those doggone Microsofties keep changing the exposed DMVs, so depending on what version of ASD you’re running, it may or may not work with sp_AskBrent®.
- v16 – July 18, 2015 – Azure SQL Database compatibility, and removed check 5 (long-running blocking query) temporarily for performance tuning.
- v15 – June 10, 2015 – check wait time, file stats, and Pefmon counters since your SQL Server was restarted by using @Seconds = 0, plus bug fixes and improvements.
- 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. (Note: The CheckDate column in the table returns the time of the first sample in that period.) 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, visit Support.BrentOzar.com 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 process that gets to the root cause of your database health and performance pains. Contact us for a free 30-minute sales consultation.