sp_BlitzFirst 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_BlitzFirst to do all that in ten seconds or less. Here’s a quick video explaining how it works:
Download it in our free First Responder Kit.
Common sp_BlitzFirst Parameters
@ExpertMode = 1 – doesn’t do more in-depth checks, but DOES return a whole lot more data, including sections on wait stats, file stats, and Perfmon counters. Calls sp_BlitzWho at the start & end too so you can see which queries were running at those times. @SinceStartup = 1 – displays wait stats, file stats, and Perfmon counters since the SQL Server started up. @Seconds = 5 – by default, we take a 5-second sample of activity, but you can take a longer sample if you want to run a load test or demo.
Storing sp_BlitzFirst Results in a Table
sp_BlitzFirst® can log results to a table if you use the @OutputDatabase* parameters like this:
|
1 2 3 4 5 6 7 8 9 |
EXEC sp_BlitzFirst @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirst', @OutputTableNameFileStats = 'BlitzFirst_FileStats', @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats', @OutputTableNameWaitStats = 'BlitzFirst_WaitStats', @OutputTableNameBlitzCache = 'BlitzCache', @OutputType = 'none' |
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:
|
1 |
EXEC sp_BlitzFirst @AsOf = '2015-02-23 18:45', @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults' |
sp_BlitzFirst 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. 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.
To query the past data, use the delta views that sp_BlitzFirst automatically creates for you. Whatever your table name inputs were, just add _Deltas to the end of them, and you’ll get data with differences from each pass. Be aware that depending on your logging frequency, the history tables might have a ton of data, and you may want to add indexes if you frequently query the logging history. However, the more indexes you add, the slower your logging goes – so easy on the indexes, Tex.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE master; GO CREATE CERTIFICATE sp_BlitzFirst_cert ENCRYPTION BY PASSWORD = '5OClockSomewhere' WITH SUBJECT = 'Certificate for sp_BlitzFirst', START_DATE = '20130711', EXPIRY_DATE = '21000101'; GO CREATE LOGIN sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert; GO CREATE USER sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert; GO GRANT EXECUTE ON dbo.sp_BlitzFirst TO sp_BlitzFirst_login; GO GRANT CONTROL SERVER TO sp_BlitzFirst_login; GO ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert WITH PASSWORD = '5OClockSomewhere'; GO GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public]; GO |
Now anybody can run sp_BlitzFirst even if they don’t have SA-level permissions. Note: whenever you update sp_BlitzFirst, you’ll need to reassign the permissions by adding the signature again:
|
1 2 3 4 5 |
ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert WITH PASSWORD = 'Get lucky'; GO GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public]; GO |
How to get support for sp_BlitzFirst
The sp_BlitzFirst documentation covers additional parameters that will return more results, store the results to a table, and more.
For free interactive support, you’ve got a few good options:
- Talk with us in the #FirstResponderKit Slack channel (if you need a free invite, hit SQLslack.com)
- Post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include the version of sp_Blitz, your SQL Server version number (including the build #), and tag it with sp_Blitz.
- To make code improvements or report a bug, visit the Github issues list and read the Contributing Guide
