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.
- The URLs for more information about each check aren’t really complete yet.
- If Microsoft supports it, so do we – unfortunately, that means SQL 2000 and 2005 are out of luck. We use a lot of cool DMVs and techniques that only work in current versions of SQL Server.
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_BlitzFirst_cert ENCRYPTION BY PASSWORD = 'FiveOClockSomewhere' WITH SUBJECT = 'Certificate for sp_BlitzFirst', START_DATE = '20130711', EXPIRY_DATE = '21000101'; 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 = 'FiveOClockSomewhere'; 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:
ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert WITH PASSWORD = 'Get lucky'; GO GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public]; GO
Going Back in Time to See Past Bottlenecks
sp_BlitzFirst® can log results to a table if you use the @OutputDatabase* parameters like this:
EXEC sp_BlitzFirst @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults'
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_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.
Note that sp_BlitzFirst® 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.
Querying for a Specific Time Range
Wanna see all three of the history tables (waits, Perfmon, and file stats) for a specific window of time, grouped together? This query will give you the totals for a 10-minute time window:
DECLARE @CheckDateStart VARCHAR(50) = '2016-08-09 13:40 -07:00'; DECLARE @CheckDateEnd VARCHAR(50) = DATEADD(MI, 10, CAST(@CheckDateStart AS DATETIMEOFFSET)); SELECT wait_type, SUM(wait_time_ms_delta / 60 / 1000) AS wait_time_minutes, SUM(waiting_tasks_count_delta) AS waiting_tasks FROM DBAmetrics.dbo.BlitzFirstResults_WaitStats_Deltas d WHERE d.CheckDate BETWEEN @CheckDateStart AND @CheckDateEnd AND ServerName = 'WIN-BJJP8BNV8QA\SQL2016' GROUP BY wait_type HAVING SUM(waiting_tasks_count_delta) > 0 ORDER BY 2 DESC; SELECT object_name, counter_name, MIN(CheckDate) AS CheckDateMin, MAX(CheckDate) AS CheckDateMax, MIN(cntr_value) AS cntr_value_min, MAX(cntr_value) AS cntr_value_max, (1.0 * MAX(cntr_value) - MIN(cntr_value)) / (DATEDIFF(ss,MIN(CheckDate), MAX(CheckDate))) AS BatchRequestsPerSecond FROM DBAmetrics.dbo.BlitzFirstResults_PerfmonStats d WHERE d.CheckDate BETWEEN @CheckDateStart AND @CheckDateEnd AND ServerName = 'WIN-BJJP8BNV8QA\SQL2016' GROUP BY object_name, counter_name ORDER BY 1, 2; SELECT DatabaseName, TypeDesc, FileLogicalName, DatabaseID, FileID, MIN(CheckDate) AS CheckDateMin, MAX(CheckDate) AS CheckDateMax, MAX(num_of_reads) - MIN(num_of_reads) AS Reads, (MAX(bytes_read) - MIN(bytes_read)) / 1024.0 / 1024 AS ReadsMB, ISNULL((MAX(bytes_read * 1.0) - MIN(bytes_read)) / NULLIF((MAX(num_of_reads) - MIN(num_of_reads)),0) / 1024, 0) AS ReadSizeAvgKB, ISNULL((MAX(io_stall_read_ms) - MIN(io_stall_read_ms)) / NULLIF((MAX(num_of_reads * 1.0) - MIN(num_of_reads)), 0), 0) AS ReadAvgStallMS, MAX(num_of_writes) - MIN(num_of_writes) AS Writes, (MAX(bytes_written) - MIN(bytes_written)) / 1024.0 / 1024 AS WritesMB, ISNULL((MAX(bytes_written * 1.0) - MIN(bytes_written)) / NULLIF((MAX(num_of_writes) - MIN(num_of_writes)),0) / 1024, 0) AS WriteSizeAvgKB, ISNULL((MAX(io_stall_write_ms) - MIN(io_stall_write_ms)) / NULLIF((MAX(num_of_writes * 1.0) - MIN(num_of_writes)), 0), 0) AS WriteAvgStallMS FROM DBAmetrics.dbo.BlitzFirstResults_FileStats d WHERE d.CheckDate BETWEEN @CheckDateStart AND @CheckDateEnd AND ServerName = 'WIN-BJJP8BNV8QA\SQL2016' GROUP BY DatabaseName, TypeDesc, FileLogicalName, DatabaseID, FileID HAVING MAX(num_of_reads) > MIN(num_of_reads) OR MAX(num_of_writes) > MIN(num_of_writes) ORDER BY DatabaseName, TypeDesc, FileLogicalName, DatabaseID, FileID;
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.
If Bad Things Happen to Good Stored Procedures
If sp_BlitzFirst doesn’t compile or if it throws an error when it runs, head on over to DBA.StackExchange.com and post a question. Include the version of sp_BlitzFirst, your SQL Server version number (including the build #), and tag it with sp_BlitzFirst.
If you’d like to suggest changes or contribute code, visit the Github repository for the First Responder Kit. You can get more instructions, file issues, and read how to contribute to the community.
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.