Narcis Miclaus

5 min read

SQL Server, from the ground up Lesson 38 / 40

sp_BlitzIndex and sp_BlitzCache: the full-body scan

Deep index audit with sp_BlitzIndex. Query plan review with sp_BlitzCache. Reading the output, triaging the 50 things they find.

sp_Blitz is the server-wide overview. sp_BlitzIndex and sp_BlitzCache are the specialist tools: the first audits indexes, the second audits query performance. Together they tell you about 80% of what’s wrong with a given database’s performance.

Today we dig into both.

sp_BlitzIndex: what your indexes look like

USE Runehold;
EXEC sp_BlitzIndex;

Four modes, via @Mode parameter:

  • @Mode = 0 (default) — Diagnose — find problems.
  • @Mode = 1 — Summarize — per-index statistics.
  • @Mode = 2 — Detail — every index, every column, every stat.
  • @Mode = 3 — Missing indexes — the “you should add these” report.
  • @Mode = 4 — All — just dump everything.

Day-to-day you use @Mode = 0. Occasional deep-dives use @Mode = 4.

The diagnose output

One row per finding. Columns:

  • Priority — 1 (urgent) to 250 (informational).
  • Finding — the problem.
  • URL — explanation (same as sp_Blitz).
  • Database Name / Schema Name / Table Name.
  • Details: schema.table.index(keys) / includes.
  • Definition: [Property1], [Property2] — any relevant context.
  • Secret columns — hidden columns the optimizer knows about but you might not (clustered key tacked onto non-clustered indexes, for example).
  • Usage: Reads vs Writes; Size.
  • More Info — how to dig deeper (usually “run sp_BlitzIndex @Mode=2 @TableName='...'” for the specific table).

The findings are prioritized for a reason. Fix P1s first.

Common sp_BlitzIndex findings

P1 — Multiple indexes with duplicate keys. You have two indexes on (CustomerId, OrderDate). Drop one. Easy win, immediate space + write cost savings.

P10 — Unused indexes. An index with thousands of writes and zero reads. Candidate for dropping. Verify first: user_seeks + user_scans + user_lookups = 0 for a meaningful period (not just since last restart).

P20 — Heaps (no clustered index). Almost always wrong. Add a clustered index. Lesson 21 for the theory.

P50 — Missing indexes suggested. From the missing-index DMV. Evaluate, don’t blindly create.

P100 — Indexes not updated recently. Haven’t been reorganized/rebuilt. Probably fine; Ola’s scripts handle it (lesson 35).

P150 — Statistics missing or stale. UPDATE STATISTICS ... WITH FULLSCAN and re-run sp_BlitzIndex to see it disappear.

Detail mode for a specific table

EXEC sp_BlitzIndex @Mode = 2, @DatabaseName = 'Runehold', @SchemaName = 'Sales', @TableName = 'Orders';

Shows every index, every column, every stat. Plus suggested non-clustered indexes based on missing-index DMVs. Plus which queries in the plan cache would benefit from each suggested index.

This is what you look at when someone says “why is this table slow?” — you get a complete picture in one result set.

sp_BlitzCache: what your queries look like

USE DBA;
EXEC sp_BlitzCache;

Parses the plan cache (like the DMV queries from lesson 36, but much smarter). Shows the top queries by a specified metric, annotated with every known anti-pattern detected in each plan.

@SortOrder parameter — choose the metric:

  • 'CPU' (default) — most CPU burned.
  • 'Reads' — most logical reads.
  • 'Writes' — most writes.
  • 'Duration' — wall-clock time.
  • 'Executions' — times run.
  • 'Recent Compilations' — recently compiled queries (useful for spotting parameter sniffing).
  • 'Memory Grant' — biggest memory hog queries.

The output

One row per top query. Columns:

  • Query — the SQL text (truncated).
  • Query Plan — click to see it in SSMS.
  • Executions / Total CPU / Avg CPU / Total Reads / Avg Reads etc.
  • Warnings — comma-separated list of problems detected in the plan.

The warnings column is the gold. Each warning tells you something specific:

  • Implicit Conversion Affecting Cardinality — your WHERE has an implicit type conversion that’s disabling index seeks.
  • Key Lookups: N% — the query does N% of its cost in key lookups. Time for a covering index.
  • Missing Index: high value — plan suggests a missing index that would help a lot.
  • Parameter Sniffing — this query has dramatically different runtimes across executions.
  • Spills to tempdb — sorts/hashes didn’t fit in memory.
  • Unparameterized Query — a literal value is being compiled into plan text; each distinct value gets its own plan. Usually wastes plan cache.

Fixing a couple of top offenders often gives you 30-50% CPU reduction on the server.

An example session

On a new server you’re asked to tune:

USE DBA;

-- Top 10 CPU-consumers
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'CPU';

-- Top 10 by reads (IO hogs)
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Reads';

-- Top 10 with memory grants
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Memory Grant';

Four minutes of work, the top 30 expensive queries in the cache, with every warning annotated. Read the top one. Fix it (usually: add a covering index, or rewrite to remove an implicit conversion). Move to the next.

Runehold tuned their production database from 65% average CPU to 22% over a month by fixing the top 15 sp_BlitzCache findings, one per week. That’s it. No magic, just systematic.

Running both regularly

Schedule sp_Blitz, sp_BlitzIndex, and sp_BlitzCache weekly via Agent, dump results to logging tables, and review the changes. New findings get attention; old ones stay resolved.

EXEC sp_Blitz
    @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults';

EXEC sp_BlitzCache @Top = 100,
    @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzCacheResults';

EXEC sp_BlitzIndex @OutputDatabaseName = 'DBA', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzIndexResults';

Now you have history. Chart it. You can answer “are things getting better or worse?” with data instead of guess.

Customizing sp_BlitzCache

Useful extra parameters:

  • @MinimumExecutionCount — ignore queries with fewer than N executions. Helps filter out one-off ad-hoc junk.
  • @StoredProcName — analyze only one proc.
  • @ExportToExcel = 1 — format output for Excel paste.
  • @IgnoreSqlHandles — comma-separated list of sql_handles to exclude (noisy queries you’ve already fixed or intentionally slow ones).

Run this on your own machine

USE Runehold;

-- 1. Index audit
EXEC sp_BlitzIndex;

-- 2. Detail on one specific table
EXEC sp_BlitzIndex @Mode = 2, @SchemaName = 'Sales', @TableName = 'Orders';

-- 3. Missing index suggestions
EXEC sp_BlitzIndex @Mode = 3;

USE DBA;

-- 4. Top 10 CPU queries
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'CPU';

-- 5. Top queries with parameter sniffing issues
EXEC sp_BlitzCache @Top = 10, @SortOrder = 'Recent Compilations';

-- 6. Queries that spill to tempdb
EXEC sp_BlitzCache @Top = 20, @SortOrder = 'Memory Grant';

Pick one finding. Click the URL. Read the explanation. Fix or document. Move on.

Next: sp_BlitzCache’s sibling for “what’s happening RIGHT NOW” — sp_WhoIsActive and sp_BlitzFirst. The tools you run when the phone rings.

Search