/sqlstats-review

name: sqlstats-review
description: Parse and analyze SQL Server SET STATISTICS IO, TIME ON output. Extracts per-table IO metrics and per-statement CPU/elapsed times, computes % logical read share, detects 27 performance patterns (I1–I18 IO checks, W1–W9 time checks). Use when a user pastes SSMS statistics output or asks why a query does too much I/O.
triggers:
  - /sqlstats-review
  - /stats-review
  - /stats-io

SQL Server Statistics IO/Time Review Skill

Purpose

Parse raw `SET STATISTICS IO, TIME ON` output from SQL Server Management Studio and produce a structured report of I/O activity and timing per statement. Applies 27 checks across IO patterns (I1–I18) and time patterns (W1–W9) to surface performance concerns that the raw output obscures.

This is the IO/time complement to `sqlplan-review`. Run it when you have STATISTICS output but no execution plan, or alongside the plan to cross-reference what actually happened at the I/O layer.

Input

Accept any of:

• Raw SSMS console output pasted inline (everything after `SET STATISTICS IO, TIME ON`)
• A plain-text `.txt` file path containing the console output
• A description of what the output showed ("physical reads on Orders table, 140ms elapsed")

The input may contain mixed content — IO lines, time lines, rows-affected messages, error messages, and unrelated output. Parse only the recognized patterns; preserve unrecognized lines as informational context.

Supported Input Line Formats

STATISTICS IO line

Table 'TableName'. Scan count X, logical reads Y, physical reads Z, read-ahead reads A, lob logical reads B, lob physical reads C, lob read-ahead reads D.

Optional additional fields (appear in Azure SQL or columnstore workloads):

• `page server reads` — Azure SQL Hyperscale: reads from page server (remote storage)
• `page server read-ahead reads` — Azure SQL Hyperscale prefetch
• `lob page server reads`, `lob page server read-ahead reads`
• `segment reads`, `segment skipped` — columnstore index segment elimination

Special table names: `Worktable` (sort spill, e.g. ORDER BY, hash aggregate spool), `Workfile` (hash spill, e.g. hash join/aggregate build input), names starting with `#` (explicit temp tables).

STATISTICS TIME lines

SQL Server parse and compile time:
   CPU time = 108 ms, elapsed time = 108 ms.

SQL Server Execution Times:
   CPU time = 156527 ms,  elapsed time = 284906 ms.

Rows affected

(13431682 row(s) affected)

Error messages

Msg 207, Level 16, State 1, Line 1
Invalid column name 'scores'.

Completion timestamp

Completion time: 2025-05-27T10:32:37.8122685-04:00

---

How to Run

1. **Parse**: Split input on newlines. Classify each line as: IO, ExecutionTime, CompileTime, RowsAffected, Error, CompletionTime, or Info. 2. **Group into statements**: Consecutive IO lines belong to the same statement group. A non-IO line (time, rows-affected, error) separates groups. 3. **Compute per-statement totals**: Sum all IO metrics within each statement group. Compute `% Logical Reads` for each table: `(table_logical / group_total_logical) × 100` to 3 decimal places. If total logical = 0, leave blank. 4. **Detect summary time rows**: If a time row's elapsed ≈ (compile_elapsed + execution_elapsed) ± 5 ms, mark it as a summary row and exclude it from running totals. Note: "Summary row detected — not added to totals." 5. **Compute grand totals**: Accumulate IO metrics across all statement groups. Merge rows for the same table name. Sort the grand total table alphabetically by table name. 6. **Run checks I1–I15 and W1–W7**: Evaluate each check against parsed data. Report triggered checks in the findings section. 7. **Output**: Produce the structured report defined in Output Format.

---

Thresholds Reference

| Metric | Value | |--------|-------| | High logical reads (statement) — warning | ≥ 1,000,000 | | High logical reads (statement) — critical | ≥ 10,000,000 | | High scan count — warning | ≥ 1,000 | | High scan count — critical | ≥ 10,000 | | High physical read ratio | physical / logical ≥ 10% | | LOB reads dominant | lob_logical / logical ≥ 50% | | Read-ahead scan indicator | read_ahead / logical ≥ 80% AND logical ≥ 10,000 | | Single-table dominance — warning | one table ≥ 80% of statement logical reads | | Single-table dominance — critical | one table ≥ 95% of statement logical reads | | Columnstore low skip rate | skipped / (reads + skipped) < 50% | | Elapsed time — warning | execution_elapsed ≥ 30,000 ms | | Elapsed time — critical | execution_elapsed ≥ 300,000 ms | | CPU time — warning | execution_cpu ≥ 60,000 ms | | I/O wait indicator | cpu < 10% of elapsed | | Parallelism indicator | cpu > 150% of elapsed | | High compile overhead | compile_cpu > 20% of execution_cpu AND compile_elapsed ≥ 200 ms | | Zero-return high-read | rows_affected = 0 AND statement logical reads ≥ 10,000 |

---

IO Checks (I1–I18)

Evaluate per-statement and per-table IO metrics.

I1 — High Logical Read Count

• **Trigger:** Statement total `logical reads` ≥ 1,000,000 (warning) or ≥ 10,000,000 (critical)
• **Severity:** Warning (≥ 1 M); Critical (≥ 10 M)
• **Fix:** High logical reads indicate large data volumes scanned. Find the highest-% table and add a covering index to reduce reads. Run `/sqlplan-review` on the execution plan for operator-level detail.

I2 — Excessive Scan Count

• **Trigger:** Any single table has `scan count` ≥ 1,000 (warning) or ≥ 10,000 (critical)
• **Severity:** Warning (1 000–9 999); Critical (≥ 10 000)
• **Fix:** High scan count on the inner side of a Nested Loops join. Add an index on the join/seek column of the scanned table so each iteration can seek instead of scan. Confirm with `/sqlplan-review` (N5 Key Lookup, N4 Expensive Scan).

I3 — High Physical Read Ratio

• **Trigger:** Any table where `physical reads / logical reads ≥ 10%`
• **Severity:** Warning
• **Fix:** Pages not in the buffer pool. Expected on cold cache (first run