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

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) < 50% when segment data is present
• Severity: Warning
• Fix: Less than half of columnstore segments were skipped — the query predicate is not eliminating segments effectively. Segment elimination relies on min/max metadata per segment. Solutions: reorganize the columnstore index to improve segment clustering (rebuild, or add a clustered rowstore index to order data before loading), or add a more selective predicate.

I11 — Columnstore Segment Skip Rate High (Good Pattern)

• Trigger: segment skipped / (segment reads + segment skipped) ≥ 90% when segment data is present
• Severity: Info
• Fix: 90%+ of columnstore segments were skipped — excellent predicate selectivity at the storage layer. No action required. Document this as a well-tuned columnstore query.

I12 — Same Table Appears Multiple Times in Statement

• Trigger: The same table name appears more than once in a single statement's IO group
• Severity: Info
• Fix: The table was scanned or accessed multiple times in the same query. Common causes: a CTE referenced more than once (T24), multiple joins to the same table, or a subquery materializing separately. Use /sqlplan-review to confirm the operator topology.

I13 — Zero Rows Affected With High Reads

• Trigger: Statement has rows affected = 0 AND total logical reads ≥ 10,000
• Severity: Info
• Fix: The query performed substantial I/O but modified or returned no rows. Possible causes: filter predicate too restrictive (all data read then filtered out), or a WHERE clause that prevents index seeks (T4, T6). Verify predicate selectivity and index coverage.

I14 — Physical Reads Non-Zero on Warm System

• Trigger: Any table has physical reads > 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 < 130. On SQL 2019+, ensure compat level 150 and Batch Mode on Rowstore is enabled.

I17 — Azure SQL Hyperscale: Remote Page Server Reads Dominant

• Trigger: page server reads / (logical reads + page server reads) ≥ 30% for any table — applies to Azure SQL Hyperscale only; skip if page server reads column is absent
• Severity: Warning — a high fraction of remote page server reads indicates the local buffer pool is not caching the working set; repeated execution will be slower than on-premises equivalents
• Fix: Reduce total logical reads (index tuning, predicate pushdown). Verify the Hyperscale replica tier has sufficient memory for the working set. For read-only workloads, route to a Hyperscale named replica with its own independent buffer pool.

I18 — High Temp Object Write Amplification

• Trigger: A #temp or Worktable entry has logical reads ≥ 5× the total logical reads of the underlying base tables feeding it — signals repeated re-reads of a temp table rather than one-time write/read
• Severity: Warning — repeated re-reads of a temp object indicate it is used as a loop-join inner side; add a covering index to the temp table or consider a table variable with a primary key
• Fix: Add an index to the temp table after population: CREATE INDEX ix_tmp ON #temp(join_col). Alternatively, evaluate whether the temp table can be replaced by a CTE or subquery that the optimizer can inline. Check /sqlplan-review for Nested Loops with the temp table on the inner side.

Time Checks (W1–W9)

Evaluate CPU and elapsed time metrics per statement.

W1 — I/O or Lock Wait Dominant (CPU << Elapsed)

• Trigger: execution_cpu < 10% of execution_elapsed AND elapsed ≥ 1,000 ms
• Severity: Warning
• Fix: The query spent most of its elapsed time waiting, not computing. Common causes: physical I/O (check I3, I14), lock/latch waits (blocking from concurrent queries), or network transfer time. If physical reads are low, investigate lock waits using sys.dm_exec_requests or Extended Events.

W2 — Parallel Execution Detected (CPU >> 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 < 10,000 ms
• Severity: Info
• Fix: Query modified > 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 < 0 or compile_elapsed < 0
• Severity: Info — negative elapsed time is a measurement artifact from STATISTICS TIME using the QueryProcessingTime counter which can wrap or be skewed by NUMA clock sources; the data for this statement should be treated as unreliable
• Fix: Rerun the query in isolation to get a clean measurement. If the issue persists, check for NUMA clock skew: SELECT node_id, online_scheduler_count FROM sys.dm_os_nodes. On multi-NUMA systems, ensure the Windows High Performance power plan is active and that processor clock synchronization is functioning. This is a data quality issue only — no query tuning action required.

Version-Aware Check Suppression

If the SQL Server version is stated by the user, read VERSION_COMPATIBILITY.md (~/.claude/skills/VERSION_COMPATIBILITY.md if installed, or skills/VERSION_COMPATIBILITY.md from the repo). If unavailable, skip silently. For checks whose minimum version exceeds the instance version: verbose mode → log as SKIP (version: requires SQL 20XX+, instance is SQL 20YY); standard report → omit entirely. Do not suppress NOT ASSESSED rows from missing input — only suppress version-inapplicable checks.

Output Format

Structure your report as follows:

## Statistics IO/Time Analysis

### Input Summary
- X statement(s) parsed
- Total logical reads: N (across all statements)
- Total execution elapsed: hh:mm:ss.mmm

---

### Statement 1

**Compile Time:** CPU N ms | Elapsed N ms

**Rows Affected:** N rows affected

**IO Statistics**

| Table | Scan Count | Logical Reads | Physical Reads | Read-Ahead | [LOB cols if present] | % of Statement Reads |
|-------|-----------|---------------|----------------|------------|----------------------|----------------------|
| TableName | 1 | 42,015 | 0 | 1,306 | | 87.234% |
| **Total** | **1** | **48,200** | **0** | **1,306** | | |

**Execution Time:** CPU N ms (hh:mm:ss.mmm) | Elapsed N ms (hh:mm:ss.mmm)

[Repeat per statement]

---

### Grand Totals (All Statements)

**IO Totals** (sorted A–Z by table name)

| Table | Scan Count | Logical Reads | Physical Reads | Read-Ahead | [LOB cols if present] | % of All Reads |
|-------|-----------|---------------|----------------|------------|----------------------|----------------|
| Orders | 3 | 125,415 | 2 | 84,201 | | 42.1% |
| Products | 7 | 98,200 | 0 | 0 | | 33.0% |
| **Grand Total** | **10** | **297,800** | **2** | **84,201** | | |

**Time Totals**

| Phase | CPU | Elapsed |
|-------|-----|---------|
| Compile | N ms | hh:mm:ss.mmm |
| Execution | N ms | hh:mm:ss.mmm |
| **Grand Total** | **N ms** | **hh:mm:ss.mmm** |

---

### Performance Findings

#### Critical Issues
**[C1 — Stmt 2, Table 'Orders'] Issue Name** (I<N> or W<N>)
- 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:

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/<skill-name>/<YYYY-MM-DD-HHmmss>-<input-prefix>/analysis.md ← full report output/<skill-name>/<YYYY-MM-DD-HHmmss>-<input-prefix>/trace.md ← Check Evaluation Log

Derive <input-prefix>:

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 — <skill-name> / # Input: <first 80 chars> / # Generated: <UTC timestamp> trace.md → # Check Evaluation Log — <skill-name> / # Input: <first 80 chars> / # Generated: <UTC timestamp>

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 alone (e.g., [C1 — Table 'Orders']).
• Do not invent findings not triggered by the rules above.
• Worktable and Workfile are system-generated names; do not treat them as user tables in grand totals sorting, but do include their reads in totals.
• A compile time row that appears between two batches is a batch separator — assign it to the preceding batch's compile phase.
• When rows affected is singular "1 row affected" vs plural, handle both forms.
• If the same statement produces both compile time and execution time lines, the compile line precedes the execution line — maintain this ordering in the report.
• For very long inputs (> 50 statements), summarize the per-statement section and focus on grand totals and the top 5 statements by logical reads.
• Summary time rows (where elapsed ≈ compile + execution totals) must not be double-counted. Note their detection explicitly.

Companion Skills

• tsql-review — Review the T-SQL source code of the query for static anti-patterns (non-sargable predicates, cursor loops, dynamic SQL) before the query runs.
• sqlplan-review — Analyze the execution plan for the same query to understand operator choices, join strategies, and row estimate quality that drove the I/O seen in STATISTICS output.
• sqlindex-advisor — Derive CREATE INDEX recommendations from the execution plan to reduce the logical reads identified here.
• sqldeadlock-review — If high I/O correlates with long elapsed times and low CPU (W1), investigate deadlock or blocking as a root cause.
• sqlplan-batch — If you have .sqlplan files for the same workload, batch-analyze them alongside this STATISTICS review.
• sqlquerystore-review — Analyze Query Store data to find regressed queries, plan instability, and the top resource consumers across the whole workload. Use after running a workload capture to prioritize which queries to tune with /sqlplan-review.

• mssql-performance-review — Orchestrator that routes mixed artifacts to multiple specialised skills (this one included), runs an adversarial root-cause check, and produces a single consolidated report with evidence chain, risk-rated fixes, and rollback. Use when you have several artifact types together or describe a symptom without knowing which skill to run.