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:

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:

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:

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:

Getting support for sp_BlitzFirst

sp_BlitzFirst works on SQL Server 2008 and newer. 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. For free interactive support, you’ve got a few good options:

• Post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include the version of sp_BlitzFirst, your SQL Server version number (including the build #), and tag it with sp_BlitzFirst.
• Talk with us in the #FirstResponderKit Slack channel (if you need a free invite, hit SQLslack.com)
• To make code improvements or report a bug, visit the Github issues list and read the Contributing Guide

Scared by the results? I can help.

Don’t have time to learn the hard way? I’m here to help with a quick, easy process that gets to the root cause of your database health and performance pains. My SQL Critical Care® gets to the root of your SQL Server pains and teaches you how to make your databases faster and more reliable. Learn more, see sample reports, and book a free call with me, Brent.

sp_BlitzFirst Blog Posts