sp_BlitzFirst

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. 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.

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. Once you’ve got logging to tables set up, you can analyze those queries using the free First Responder Kit Power BI dashboard.

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:

Scared by the results? We can help.

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. Our 3-day 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

What To Do If sp_BlitzFirst Warns About High Compilations

Compiles Aren’t The Same As Recompiles If you’re seeing high RECOMPILES, this post isn’t for you. We’ll talk about the differences between compiles and recompiles, but not how to troubleshoot recompiles. Recompiles mean one of two obvious things: You have a RECOMPILE hint at the stored procedure or statement level, or SQL found a reason…
Read More

Learn to Use sp_Blitz, sp_BlitzCache, sp_BlitzFirst, and sp_BlitzIndex with These Tutorial Videos

There have been so many improvements lately to our First Responder Kit scripts that it was time to update our tutorial videos. Each of these is about ten minutes long and shows how to use ’em: How to Use sp_Blitz for a Health Check How to Use sp_BlitzFirst in a Performance Emergency How to Use…
Read More

Memory Grants Added to sp_BlitzCache and sp_BlitzFirst

Exciting New Doodads When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}