I figured I’d write some notes after watching Brent’s How I Use the First Responder Kit: sp BlitzFirst

This query from Brent’s SQL-Server-First-Responder-Kit essentially gives you a view of what is happening on the database over a 5 second period.

EXEC sp_blitzFirst @ExpertModel = 1

There are several sets of data returned. The first set shows which queries are running right now, at the start of the 5 second snapshot, and also includes their execution plans! You can open these just by clicking the plan link, and if you have the free SentryOne Plan Explorer installed, you can open the plan straight from SSMS.

The second set of data shows a prioritised list of potential issues with your database, for mine it showed pretty high CPU usage. For Brent’s, it showed a database backup was in progress.

The third shows WAIT STATS; like how long SQL Server had to wait for any given reason. My top entry was SOS_SCHEDULER_YIELD, which is in the CPU category and it waited for 70 seconds. We should probably look into this!

The fifth set shows which files were interacted with during the snapshot, and how much data was written or read. I’m starting to wonder how I’ve gone so long without having used these sprocs!

The sixth set is a dump of the available perf-mon counters. For example, our SQL Compilations/sec counter was showing around 20 recompilations per second. That doesn’t sound great.

The last set is a re-run of the first, and again shows us what is running at the end of the 5 second snapshot. This allows us to review which queries were running across the full duration of the sp_blitzFirst snapshots.

Brent also recommends trying out the SinceStartup parameter:

EXEC sp_blitzFirst @SinceStartup = 1

This gives a view of what SQL Server has been up to since it first started up, rather than just over 5 seconds.

This script seems fairly powerful at giving an overview of the health of your database server, and helps you see why it might be running slowly. For us it most certainly is CPU constrained.

Popular posts from this blog

Taking a memory dump of a w3wp process


GitLab Badges