Narcis Miclaus

5 min read

SQL Server, from the ground up Lesson 37 / 40

The First Responder Kit: Brent Ozar's gift to humanity

What the kit is, why it's MIT-licensed and free forever, how to install it on every SQL Server you own, and the shape of the tools inside.

Eighteen years ago, a consultant named Brent Ozar started writing stored procedures that summarized everything wrong with a SQL Server in a single result set. sp_Blitz was the first. It checks backups, security, configuration, index design, disk layout, and a hundred other things, and prints a prioritized list of findings with links to blog posts explaining each one.

Then he wrote sp_BlitzIndex, which audits your indexes. Then sp_BlitzCache for query performance. Then sp_BlitzFirst for “what’s happening right now.” Over the years the collection grew. Brent’s company open-sourced it all as the First Responder Kit (FRK), MIT-licensed.

It’s the single most useful set of scripts you can install on a SQL Server. Every competent DBA has them deployed on every production instance they touch. Today’s lesson is the introduction; lessons 38-40 go deep on specific tools.

What’s in the kit

The main stored procedures:

  • sp_Blitz — full-body server scan. Configuration, backups, security, major issues.
  • sp_BlitzFirst — “what’s happening right now?” Live wait stats, top queries, active sessions.
  • sp_BlitzIndex — index audit. Unused indexes, missing indexes, heap tables, fragmentation.
  • sp_BlitzCache — analyzes the plan cache. Bad queries, missing indexes, implicit conversions.
  • sp_BlitzLock — reads the default Extended Events to summarize deadlocks.
  • sp_BlitzBackups — backup history and RPO/RTO estimates.
  • sp_BlitzWho — live sessions, cleaner than the DMV query from lesson 36.
  • sp_BlitzQueryStore — Query Store-based diagnostics.

Plus a few helper procedures. All live in one database (often master, sometimes DBA or a custom name).

Installing

From the GitHub repo — github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit — download or clone. Run Install-All-Scripts.sql against the database you want to install them in.

-- Make a DBA database to hold the tools
CREATE DATABASE DBA;
USE DBA;
GO

-- Run Install-All-Scripts.sql from the kit

Install order: a helper view called FirstResponderKitVersion, then all the stored procs, then a few tables for logging.

Each proc is self-contained — you can run sp_Blitz without sp_BlitzIndex. Installing all of them costs nothing and keeps you future-proof.

Upgrade quarterly. Brent’s team updates the kit every month or so with new checks and bug fixes. Install-All-Scripts.sql handles updates cleanly — just re-run it.

License and usage

MIT license. You can use it on commercial servers, modify it, ship it embedded in your own tools, run it on 10,000 servers. It’s free. Nobody’s asking you to pay. Brent’s company makes money through consulting and training; the scripts are a goodwill investment.

If you benefit from the kit (you will), consider donating to the Vintage Computing Federation, which Brent’s wife’s foundation supports. It’s a small ask for tools that have saved me hundreds of hours.

Running sp_Blitz: the full-body scan

EXEC sp_Blitz;

That’s it. The result set has columns:

  • Priority — 1 is “the building is on fire,” 200 is “minor warning.” Sort ascending.
  • FindingsGroup — category (Backup, Performance, Security, Reliability, etc.).
  • Finding — the specific issue.
  • DatabaseName — if applicable.
  • Details — the specifics.
  • URL — link to Brent’s blog explaining the finding, why it matters, and how to fix it.

A fresh install of SQL Server might produce 20 findings. A neglected 10-year-old production instance might produce 200. The point is to know which.

Typical “priority 1” findings:

  • No recent backup.
  • Corruption detected.
  • Database in suspect state.

Typical “priority 10” findings:

  • SA account enabled with a guessable password.
  • No DBCC CHECKDB run in 30 days.
  • Instance running with default trace only, no Extended Events.

Typical “priority 50” findings:

  • Auto-close enabled on a database (slows access).
  • Lots of non-standard configuration options.
  • Stats haven’t been updated in a month.

Go through the findings one by one. Not all need fixing immediately — some are intentional design choices — but you should know what’s there.

The URL column is the magic

Every finding links to a Brent Ozar blog post that explains:

  • What the finding means.
  • Why it’s a problem (with real-world examples).
  • How to fix it.
  • When it’s OK to ignore.

You don’t need to memorize every SQL Server anti-pattern. You need to recognize the finding in sp_Blitz’s output and click the URL.

Over time, you develop muscle memory: “I’ve seen this one a dozen times, I know what to do.” Until then: read the URL, fix or document, move on.

Customizing checks

Some findings you want to suppress because they’re intentional in your environment. sp_Blitz has a skip table:

-- Create the skip table (first time)
EXEC sp_Blitz @CheckServerInfo = 1, @OutputType = 'NONE';

-- Skip a specific check by check ID
INSERT INTO dbo.BlitzChecksToSkip (CheckID, ServerName, DatabaseName, Comment)
VALUES (24, @@SERVERNAME, 'ReportingDB', 'Intentional; part of the DW design.');

-- Now sp_Blitz won't flag CheckID 24 on that server + database
EXEC sp_Blitz;

CheckIDs are documented in the source. Build a skip list over time that matches your shop’s conventions.

-- Send output to a logging table
EXEC sp_Blitz @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults';

Schedule that in Agent, weekly. Then you have a history of every finding. New findings appear; old ones hopefully disappear. Chart the count-over-time; production hygiene trend in one graph.

What happens on Azure SQL DB

Azure SQL Database has some restrictions. Some checks don’t apply (tempdb config, Agent jobs), some are replaced by Azure-specific equivalents. The kit auto-detects and runs what makes sense. You still get a useful report, just shorter.

Run this on your own machine

-- 1. Create a DBA database if you don't have one
CREATE DATABASE DBA;
USE DBA;

-- 2. Go to github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit
--    Download Install-All-Scripts.sql
--    Open in SSMS, make sure you're in the DBA database
--    Run it.

-- 3. Full-body scan
EXEC sp_Blitz;

-- 4. See the summary
EXEC sp_Blitz @CheckServerInfo = 1;

-- 5. Look at one specific finding in detail by CheckID
SELECT * FROM dbo.BlitzResults   -- if you logged output
WHERE CheckID = 68               -- "Security - SA account name"
ORDER BY CheckDate DESC;

Read every URL for findings on your own server at least once. Ask Claude about anything you don’t understand. The goal is “I’ve seen this finding, I know what it means, I know whether to fix it.”

Next lesson: sp_BlitzIndex and sp_BlitzCache — the full-body scan for your indexes and your query plans, in that order.

Search