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:

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:

Want training on this?

In my How I Use the First Responder Kit class, I teach you the most important parameters and how I use them in my day-to-day work.

Once you’ve conquered that, my Fundamentals classes cover index tuning, query tuning, TempDB, running SQL Server in the cloud, and much more.

Brent Ozar

Want consulting 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. Learn more, see sample reports, and book a free call with me.