Sqlstats Review

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 ≈ (compileelapsed + executionelapsed) ± 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 | loblogical / logical ≥ 50% | | Read-ahead scan indicator | readahead / 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) 150% of elapsed | | High compile overhead | compilecpu > 20% of executioncpu AND compileelapsed ≥ 200 ms | | Zero-return high-read | rowsaffected = 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 after restart or DBCC DROPCLEANBUFFERS). Concerning on a warm system: indicates the working set is larger than available RAM, or this table is infrequently accessed. Solutions: add RAM, reduce logical reads via index, or pre-warm the buffer pool.

I4 — Read-Ahead Dominant Pattern (Full Scan Signal)

• Trigger: read-ahead reads / logical reads ≥ 80% AND logical reads ≥ 10,000 for any table
• Severity: Info
• Fix: The storage engine prefetched most pages sequentially — strong indicator of a full index scan. Verify whether an index seek is possible for this table's predicate (/tsql-review T4, T6; /sqlplan-review N4).

I5 — Single Table Dominates Logical Reads

• Trigger: One table accounts for ≥ 80% (warning) or ≥ 95% (critical) of statement total logical reads
• Severity: Warning (≥ 80%); Critical (≥ 95%)
• Fix: Focus all index tuning effort on this table. A covering index eliminating a scan or key lookup here has the highest marginal impact. Run /sqlplan-review and /sqlindex-advisor targeted at this table.

I6 — Worktable or Workfile Detected

• Trigger: Table name is exactly Worktable or Workfile
• Severity: Warning
• Fix: SQL Server created a temporary work structure in tempdb because a sort or hash operation exceeded its memory grant: a Worktable backs a spilled sort (or a hash aggregate spool), a Workfile backs a spilled hash join/aggregate build input. This corresponds to a spill to tempdb — see checks N41–N43 in sqlplan-review. Reduce spills by: updating statistics, adding indexes to avoid large sorts, or increasing the memory grant via OPTION (MIN_GRANT_PERCENT).

I7 — Temporary Table in IO Output

• Trigger: Table name starts with # (explicit local temp table) or ## (global temp table)
• Severity: Info
• Fix: An explicit temp table is being used. Verify it has adequate statistics (created after INSERT, not before) and appropriate indexes for subsequent joins. See /tsql-review T45, T46 for table variable vs temp table guidance.

I8 — LOB Reads Present

• Trigger: lob logical reads > 0 for any table
• Severity: Info
• Fix: The query is accessing Large Object columns (text, ntext, image, varchar(max), nvarchar(max), xml, or json). LOB pages are stored separately from the main row and require additional I/O. Consider: reading only the needed LOB columns (replace SELECT *), or restructuring JSON/XML access.

I9 — LOB Reads Dominant

• Trigger: lob logical reads / logical reads ≥ 50% for any table
• Severity: Warning
• Fix: Most I/O for this table is LOB data. The LOB columns are likely large or numerous. Investigate: reading fewer LOB columns, compressing LOB data, or moving infrequently-accessed LOB data to a separate table.

I10 — Columnstore Segment Skip Rate Low

• Trigger: segment skipped / (segment reads + segment skipped) 0
• Severity: Info
• Fix: Physical reads indicate pages read from disk rather than the buffer pool cache. On a system that has been running normally (warm cache), persistent physical reads may signal buffer pool pressure (insufficient RAM for working set). Benign on first execution or after cache flush.

I15 — Azure SQL Page Server Reads Detected

• Trigger: page server reads > 0 for any table
• Severity: Info
• Fix: Running on Azure SQL Hyperscale — page server reads are reads from remote storage (page server), not the local buffer pool. Similar in impact to physical reads. Optimize by reducing total logical reads to improve local cache hit rate.

I16 — Columnstore Batch Mode I/O Absent Despite CS Index

• Trigger: Columnstore segment reads > 0 AND no Worktable/Workfile in the statement AND SET STATISTICS IO shows only row-store style output (no segment reads line) — indicates row-mode execution against a columnstore index — SQL 2012+
• Severity: Warning — row-mode execution on a columnstore index loses batch-mode vectorization; I/O and CPU throughput may be 5–10× lower than expected
• Fix: Diagnose with /sqlplan-review — check N7 (Row Mode Columnstore Scan). Common causes: scalar UDF in SELECT list (blocking batch mode until SQL 2019 UDF inlining), OUTER JOIN patterns, or compat level > Elapsed)
• Trigger: execution_cpu > 150% of execution_elapsed
• Severity: Info
• Fix: CPU time exceeds elapsed time — the query ran on multiple threads (parallel plan). CPU time is the sum across all threads. This is expected and often desirable. Use /sqlplan-review to confirm DOP and check for thread imbalance (N30 Parallel Thread Skew).

W3 — High Compile Time Relative to Execution

• Trigger: compile_cpu > 20% of execution_cpu AND compile_elapsed ≥ 200 ms
• Severity: Warning
• Fix: Query compilation consumed a significant fraction of the total time. Causes: complex query structure (deep CTEs T25, many joins), missing statistics, or ad-hoc queries that aren't cached. Fix: simplify the query, ensure statistics are up to date, or use a stored procedure to enable plan reuse.

W4 — Long Elapsed Time

• Trigger: execution_elapsed ≥ 30,000 ms (warning) or ≥ 300,000 ms (critical)
• Severity: Warning (≥ 30 s); Critical (≥ 5 min)
• Fix: Query ran for > 30 seconds. Prioritize for tuning. Identify the highest-read table (I1, I5) and run /sqlplan-review on the captured execution plan to find the dominant operator.

W5 — High CPU Time

• Trigger: execution_cpu ≥ 60,000 ms
• Severity: Warning
• Fix: Query consumed > 60 seconds of CPU time. High CPU usually correlates with large scans, sorts, hash joins, or implicit conversions. Use /sqlplan-review to identify the high-CPU operators (N4 Scan, N18 Hash Match, N20 Sort).

W6 — Multi-Batch: Highly Variable Elapsed Times

• Trigger: When multiple statements are present, max(execution_elapsed) > 10× min(execution_elapsed) for statements with elapsed ≥ 100 ms
• Severity: Info
• Fix: One or more statements take dramatically longer than others. Focus tuning effort on the slowest batch. Report which statement number is the outlier.

W7 — High Rows Affected With Low Elapsed

• Trigger: rows_affected > 1,000,000 AND execution_elapsed 1 million rows very quickly. Verify this was intentional. Large-volume DML may: fill the transaction log, cause long-running lock hold, or affect downstream replication. Consider batching (e.g., TOP 10000` in a loop).

W8 — Compile Time Dominates Total Elapsed Time

• Trigger: compile_elapsed > 30% of execution_elapsed AND execution_elapsed ≥ 500 ms
• Severity: Warning — compilation overhead is consuming a disproportionate share of each execution's elapsed time; each execution is paying a fresh compile cost rather than reusing a cached plan
• Fix: Common causes: ad-hoc SQL not parameterized (use sp_executesql with parameters), optimizer timeout on very complex queries (simplify or use Query Store hints), statistics out of date forcing recompile. Add OPTION (RECOMPILE) only when the compile cost is intentional (e.g., parameter-sensitive queries where a fresh plan is always better than a stale cached one).

W9 — Negative Elapsed Time (Clock Skew Artifact)

• Trigger: Any statement shows `execution_elapsed or W)
• Observed: [specific table name, metric value, statement number]
• Impact: [why this matters]
• Fix: [concrete action]

Warnings

[same format]

Info

[same format]

Passed Checks

I1 ✓ (brief reason), I3 ✓ (brief reason) [list every check verified clean with a reason in parens — e.g., I3 ✓ (no intentional full-table scan without comment)]

---

Analyzed by: [state the AI model and version you are running as, e.g. "Claude Sonnet 4.6", "DeepSeek R1", "GPT-4o"] · [current date and time in the user's local timezone, or UTC if timezone is unknown, e.g. "2026-05-16 20:15 NZST"]

**Formatting rules:**
- Time values: display as both raw ms and `hh:mm:ss.mmm` format
- All numeric IO values: comma-formatted thousands separators
- % Logical Reads: 3 decimal places for per-statement tables; 1 decimal place for grand totals
- Omit LOB, segment, and page server columns from tables if all values are zero
- Bold the totals row in every table
- If no execution time line is found for a statement, note "No execution time recorded"
- If compile time appears without execution time (compile-only run), note it

---

### Section: Output Filters (--brief / --critical-only)

**`--brief`** — Omit the Passed Checks table and attribution footer. Output the Summary, Findings, and Prioritized Fix Sequence sections only. Use when a quick scan of what fired is all that's needed.

**`--critical-only`** — Suppress Warning and Info findings. Show only Critical findings. The Passed Checks table is also omitted. Use when triaging an incident and only actionable blockers matter.

Both flags can be combined: `--brief --critical-only` produces the Summary section plus Critical findings only.

When neither flag is present, produce the full report as documented above.

---

### Section: Verbose Output (--verbose)

When the user's request includes `--verbose`, `--trace`, or the word `verbose`:

**1. Append a `## Check Evaluation Log` section** after the Passed Checks table.

Include one row for every check in this skill's ruleset, in check-ID order:

| Check | Evidence | Threshold | Result |
|-------|----------|-----------|--------|
| [ID — Name] | [key attribute(s) and value found, or "absent"] | [threshold or condition] | PASS / **FIRE → [severity]** / NOT ASSESSED |

Result conventions:
- `PASS` — attribute present, threshold not met
- `**FIRE → Critical/Warning/Info**` — threshold met; bold to distinguish from passes
- `NOT ASSESSED` — required attribute absent from input

**2. Save both files** to the current working directory using the Write tool:

  output//-/analysis.md  ← full report
  output//-/trace.md     ← Check Evaluation Log

Derive ``:
1. Filename stem if a file path was provided (e.g. `horrible.sqlplan` → `horrible`)
2. First meaningful identifier from the artifact (top wait type, first table name, procedure name, etc.)
3. Fallback: `run`
Sanitize: alphanumeric + hyphens/underscores only, max 32 chars.

File headers:
  analysis.md → `# Analysis —  / # Input:  / # Generated: `
  trace.md    → `# Check Evaluation Log —  / # Input:  / # Generated: `

Create directories as needed. When `--verbose` is not present, write nothing to disk.

---

## Notes

- Finding headers include the statement number and table name as the source reference (e.g., `[C1 — Stmt 2, Table 'Orders']`). For single-statement inputs, use the table name

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [vanterx](https://github.com/vanterx)
- **Source:** [vanterx/mssql-performance-skills](https://github.com/vanterx/mssql-performance-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.