mssql-performance-skills — SQL Server Performance Tuning Skills for LLMs

https://github.com/vanterx/mssql-performance-skills

Claude.ai (Remote MCP)

Go to claude.ai → Settings → Connectors → Add custom connector, enter the URL and click Add:

https://mssql-mcp.tsx113.workers.dev

A collection of Claude Code skills covering the full SQL Server performance tuning workflow — from static T-SQL source review to execution plan analysis, I/O statistics, workload traces, wait statistics, Query Store analysis, procedure runtime stats, index recommendations, deadlock diagnosis, batch workload assessment, WSFC cluster log analysis, Always On AG health, AG configuration correctness, ERRORLOG operational review, SPN/Kerberos delegation diagnosis, full encryption infrastructure review (TDE, Always Encrypted, CLE, backup encryption, TLS, certificates, key hierarchy including sp_control_dbmasterkey_password/SSISDB, dynamic data masking, PCI-DSS/HIPAA/GDPR/FedRAMP compliance), instance/database configuration drift, SQL Server Setup Bootstrap log analysis, SQL Server Reporting Services (SSRS) report server trace log analysis, and SQL Server migration readiness (version/edition/platform compatibility, security-object portability, operational-object portability) — plus an agentic orchestrator that routes mixed artifacts to the right specialised skill. 833 checks across 26 skills.

AI-Generated Skills — These skills were planned and generated by Claude with open source community resource guidance. The check catalog, thresholds, and output formats are intended to be improved and validated by human DBAs against real workloads. PRs welcome for new checks, threshold adjustments, or output format refinements.

Skills

Quick Start

5 minutes to your first analysis:

1. Install the skills (see Installation below)
2. In SSMS, open skills/sqlplan-review/examples/horrible.sqlplan
3. In Claude Code, type:

   /sqlplan-review skills/sqlplan-review/examples/horrible.sqlplan
   

4. Compare the output to skills/sqlplan-review/examples/horrible-analysis.md — that's the reference gold-standard output showing the expected Critical / Warning / Info findings and Passed Checks table.

Don't have a plan yet? Use the symptom-based routing in PERFORMANCE_TUNING_GUIDE.md to pick the right skill for what you have.

Installation

The skills are plain Markdown files. Copy them into your agent's skills directory — no build, no runtime, no dependencies.

Claude Code

Option 1: Plugin Marketplace (recommended)

# Add the marketplace (once per machine or project)
/plugin marketplace add vanterx/mssql-performance-skills

# Install all 26 skills
/plugin install mssql-performance-skills@mssql-performance-skills

For project-scoped install, append --scope project to the marketplace add command.

Option 2: npx one-liner

npx skills add vanterx/mssql-performance-skills   # requires Node.js >= 18

Uses the third-party skills npm package — not maintained by Anthropic or this repo.

Option 3: Manual

git clone https://github.com/vanterx/mssql-performance-skills.git
cp -r mssql-performance-skills/skills/* ~/.claude/skills/   # global
# or
cp -r mssql-performance-skills/skills/* .claude/skills/     # project-scoped

Contributing / Local Development

After cloning, install the git hooks to keep skills-data.ts in sync automatically:

bash scripts/install-hooks.sh

This installs a pre-commit hook that regenerates mcp-server/src/skills-data.ts whenever a skills/*/SKILL.md file is staged, so you never have to run npm run bundle manually before committing.

MCP Server (Remote — Cloudflare Workers)

The skills are hosted as a remote MCP server on Cloudflare Workers — no Node.js, no clone, no build required.

claude.ai web — Settings → Connectors → Add custom connector:

https://mssql-mcp.tsx113.workers.dev

OpenCode — add to ~/.config/opencode/opencode.jsonc:

{
  "mcp": {
    "mssql": {
      "type": "remote",
      "url": "https://mssql-mcp.tsx113.workers.dev"
    }
  }
}

"type": "remote" is required (OpenCode v1.15.10+). Without it OpenCode treats the entry as a local stdio server and crashes on startup.

Claude Desktop does not support remote MCP servers via URL — use claude.ai web or the local Claude Code install method above.

Once connected, the following are available in any conversation:

The server auto-deploys via GitHub Actions on every push. Source: mcp-server/

Other LLMs and IDEs

SKILL.md files are LLM-agnostic. Paste one into ChatGPT/Gemini as a system instruction, or drop it into:

For non-Claude LLMs, use SKILL.md alone (not references/check-explanations.md) — it contains every trigger and threshold in a compact form. For large .sqlplan XML, ask the LLM to extract key fields first.

Not sure which skill to use? See PERFORMANCE_TUNING_GUIDE.md for symptom-based routing. Costs? See LLM_COST_ESTIMATION.md. Which checks apply to your SQL Server version? See VERSION_COMPATIBILITY.md — skill-level support matrix from SQL 2008 R2 through SQL 2022 and Azure SQL.

Recommended Workflow

Use the full pipeline for a slow query you're actively tuning, or jump to the relevant step if you already have an artifact.

Don't know which step to start at? Run the orchestrator — it classifies your artifacts (or a symptom description), dispatches the right specialised skills in the right order, and runs an adversarial root-cause check across the merged findings.

╔══════════════════════════════════════════════════════════════╗
║  ENTRY POINT  — when you have mixed artifacts or only a      ║
║                  symptom, let the orchestrator route          ║
╠══════════════════════════════════════════════════════════════╣
║                                                              ║
║  Mixed inputs (.sqlplan + .sql + stats + waits + ...)        ║
║         OR                                                    ║
║  Symptom description ("CPU is high since 09:00")             ║
║         │                                                    ║
║         ▼                                                    ║
║  /mssql-performance-review   or   /sql-triage                ║
║  Agentic dispatcher — routes to specialised skills below,    ║
║  runs adversarial check, emits unified report with evidence  ║
║  chain, risk-rated fixes, rollback, and verification steps.  ║
║  Strictly offline — never contacts SQL Server.                ║
║                                                              ║
╠══════════════════════════════════════════════════════════════╣
║  SHIFT-LEFT  — catch problems before execution               ║
╠══════════════════════════════════════════════════════════════╣
║                                                              ║
║  T-SQL source code (.sql file, stored procedure body)        ║
║         │                                                    ║
║         ▼                                                    ║
║  /tsql-review                                                ║
║  85 checks — SQL injection, non-sargable predicates,         ║
║  cursor patterns, NULL bugs, deprecated syntax               ║
║                                                              ║
╠══════════════════════════════════════════════════════════════╣
║  RUNTIME MEASUREMENT — what actually happened                ║
╠══════════════════════════════════════════════════════════════╣
║                                                              ║
║  SET STATISTICS IO, TIME ON  (SSMS Messages tab)             ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqlstats-review                                            ║
║  27 checks — per-table read counts, scan counts,             ║
║  worktable spills, CPU vs elapsed wait ratio                 ║
║                                                              ║
║  Profiler .trc / Extended Events .xel output                 ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqltrace-review                                            ║
║  25 checks — N+1 patterns, parameter sniffing variance,      ║
║  recompiles, sort/hash warnings, top resource consumers      ║
║                                                              ║
║  sys.dm_os_wait_stats — single snapshot or 3+ snapshots      ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqlwait-review                                             ║
║  44 checks — I/O, locks, parallelism, memory grants,         ║
║  latch contention, log space, poison/throttle waits,          ║
║  backup I/O, insert hotspots, cumulative skew;                ║
║  trend analysis (V19–V26); modern features (V30–V36)         ║
║                                                              ║
║  Query Store DMVs (SQL Server 2016+)                         ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqlquerystore-review                                         ║
║  32 checks — regressed queries, plan instability,            ║
║  top consumers, query-level waits, IQP/PSP/DOP/CE feedback  ║
║                                                              ║
║  Collected sys.dm_exec_procedure_stats snapshots             ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqlprocstats-review                                           ║
║  25 checks — top CPU/IO procedures, per-execution            ║
║  efficiency, N+1 callers, trend analysis, advanced patterns  ║
║                                                              ║
║  sys.dm_os_memory_clerks / PLE / memory grant DMVs           ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqlmemory-review                                           ║
║  20 checks — PLE decline, plan cache bloat, memory           ║
║  grant queue/timeouts, ColumnStore/XTP, LPIM, OS pressure    ║
║                                                              ║
║  sys.dm_io_virtual_file_stats — snapshot pair preferred      ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqldiskio-review                                           ║
║  15 checks — data/log latency, hot files, stall ratio,       ║
║  storage placement, auto-growth sizing and timing            ║
║                                                              ║
╠══════════════════════════════════════════════════════════════╣
║  DEEP DIVE — execution plan analysis                         ║
╠══════════════════════════════════════════════════════════════╣
║                                                              ║
║  Execution plan (.sqlplan)  — capture with Ctrl+M in SSMS    ║
║         │                                                    ║
║         ├──▶  /sqlplan-review                                ║
║         │     111 checks — join strategy, row estimates,     ║
║         │     memory grants, parallelism, operator spills    ║
║         │                                                    ║
║         └──▶  /sqlindex-advisor                         ║
║               Ranked CREATE INDEX script from operator       ║
║               patterns + optimizer suggestions               ║
║                                                              ║
╠══════════════════════════════════════════════════════════════╣
║  SPECIFIC PROBLEMS                                           ║
╠══════════════════════════════════════════════════════════════╣
║                                                              ║
║  Two plans (before/after a regression)                       ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqlplan-compare baseline.sqlplan regression.sqlplan        ║
║  20 checks — seek→scan, DOP drop, batch mode lost,           ║
║  implicit conversion, partition elimination, PSP             ║
║                                                              ║
║  Deadlock XML (.xdl / system_health XE session)              ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqldeadlock-review deadlock.xdl                              ║
║  17 patterns — lock order, RCSI bypass, MERGE, heap RID,    ║
║  TempDB, lock escalation, DTC, ledger/temporal, optimized locking/TID ║
║                                                              ║
║  Folder of .sqlplan files (workload capture)                 ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqlplan-batch plans/                                       ║
║  Aggregate dashboard — top offenders, check frequency,       ║
║  consolidated index script across all plans                  ║
║                                                              ║
╠══════════════════════════════════════════════════════════════╣
║  ENTERPRISE AVAILABILITY — failover / connectivity path      ║
╠══════════════════════════════════════════════════════════════╣
║                                                              ║
║  SQL Server ERRORLOG (paste text or file path)               ║
║         │                                                    ║
║         ▼                                                    ║
║  /sqlerrorlog-review                                            ║
║  33 checks — AG failover events, lease expiry, memory        ║
║  pressure, I/O slow, login burst, ADR/IQP/CE feature signals ║
║         │ AG lease / hadr event seen?                        ║
║         ▼                                                    ║
║  /sqlclusterlog-review                                          ║
║  30 checks — WSFC lease timeouts, quorum loss, node          ║
║  eviction, network partition, Cloud Witness, Arc agent       ║
║         │ cluster stable, AG unhealthy?                      ║
║         ▼                                                    ║
║  /sqlhadr-review                                         ║
║  27 checks — replica connectivity, redo queue, data loss     ║
║  risk, log send rate, Contained AG, parallel redo            ║
║         │ AG config drift suspected?                         ║
║         ▼                                                    ║
║  /sqlag-review                                               ║
║  37 checks — prerequisites, replica design, listener,        ║
║  backup strategy, endpoint security, distributed AG          ║
║         │ Kerberos / auth failure in ERRORLOG?               ║
║         ▼                                                    ║
║  /sqlspn-review                                                 ║
║  40 checks — MSSQLSvc SPN, AG listener, delegation,          ║
║  Azure AD hybrid, gMSA rollover, CNAME alias, FAST           ║
║                                                              ║
╚══════════════════════════════════════════════════════════════╝

Not sure which skill to use? See PERFORMANCE_TUNING_GUIDE.md for scenario-based routing and symptom lookup.

sqlwait-review

Analyze sys.dm_os_wait_stats or sys.dm_exec_requests output to identify the dominant server bottleneck. Applies 44 checks (V1–V44) based on the Waits and Queues methodology. Single-snapshot mode (V1–V18, V27–V29) covers physical I/O, lock waits, parallelism (CXPACKET/HT*), memory grant queuing, log I/O, CPU pressure, thread exhaustion, TempDB contention, non-page latch contention, log space exhaustion, client/network waits, HA synchronization, OS-level calls, poison/throttle waits (IO_RETRY, LOG_RATE_GOVERNOR, SE_REPL_), PAGELATCH on user databases (insert hotspots), backup I/O, and cumulative skew detection. Trend mode (V19–V26) activates automatically when 3+ time-stamped snapshots are provided — detecting worsening trends, spikes, peak periods, emerging bottlenecks, and correlated wait types. Modern feature checks (V30–V36) cover In-Memory OLTP (XTP), Columnstore, Query Store (QDS*), Transaction/DTC, Service Broker, Full Text Search, and Parallel Redo (Always On secondary). Memory and I/O detail checks (V37–V40) cover forced memory grants, grant timeouts, stolen memory percentage, and file-level I/O latency — requires optional DMV capture queries. SQL 2019/2022 feature waits (V41–V44) cover PSP selector wait, IQP DOP Feedback adjustment wait, ADR PVS cleanup worker wait, and TempDB memory-optimized metadata contention.

Triggers

/sqlwait-review
/wait-review
/waits

How to capture wait statistics

SELECT TOP 20
    wait_type, waiting_tasks_count, wait_time_ms,
    max_wait_time_ms, signal_wait_time_ms,
    CAST(100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(5,2)) AS pct_total
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Full benign list in PERFORMANCE_TUNING_GUIDE.md / skills/sqlwait-review/SKILL.md
    'BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR','BROKER_TASK_STOP','BROKER_TO_FLUSH',
    'CHECKPOINT_QUEUE','CLR_AUTO_EVENT','CLR_MANUAL_EVENT','DIRTY_PAGE_POLL',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_CLUSAPI_CALL','HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE',
    'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE',
    'PARALLEL_REDO_DRAIN_WORKER','PARALLEL_REDO_LOG_CACHE','PARALLEL_REDO_TRAN_LIST',
    'PARALLEL_REDO_WORKER_SYNC','PARALLEL_REDO_WORKER_WAIT_WORK',
    'QDS_ASYNC_QUEUE','QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
    'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_SHUTDOWN_QUEUE',
    'REDO_THREAD_PENDING_WORK','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE',
    'SERVER_IDLE_CHECK','SLEEP_DBSTARTUP','SLEEP_DBTASK','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
    'SLEEP_SYSTEMTASK','SLEEP_TASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',
    'SOS_WORK_DISPATCHER','SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH',
    'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','UCS_SESSION_REGISTRATION',
    'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','WAITFOR',
    'XE_DISPATCHER_WAIT','XE_LIVE_TARGET_TVF','XE_TIMER_EVENT'
)
ORDER BY wait_time_ms DESC;

Paste the result into Claude:

/sqlwait-review

[paste query results here]

Output

## Wait Statistics Analysis

### Input Summary
- Source: sys.dm_os_wait_stats — 30-minute differential
- Total actionable wait time: 16,069,270 ms
- In context: ≈ 8.9 sessions blocked on average throughout the period
- Signal wait ratio: 8.6% (< 15% — CPU not yet saturated)

### Server Configuration Context
| Setting | Value | Affects | Interpretation |
|---------|-------|---------|---------------|
| RCSI enabled | No | V2 | Enabling RCSI eliminates reader/writer LCK_M_S conflicts |
| TempDB data files | 2 of 8 recommended | V9 | Add 6 files to reduce PFS/GAM contention |
| Cost Threshold for Parallelism | 5 (default) | V3 | Raise to 50 before reducing MAXDOP |

### Top Wait Types (V17)
| Rank | Wait Type | Category | Total Wait ms | % of Total | Max Wait ms |
|------|-----------|----------|--------------|------------|------------|
| 1 | PAGEIOLATCH_SH | I/O | 8,420,500 | 52.4% | 4,200 |
| 2 | LCK_M_S | Locks | 2,568,900 | 16.0% | 142,800 |
| 3 | CXPACKET | Parallelism | 1,840,200 | 11.5% | 8,100 |

### Dominant Bottleneck
This server is I/O bound — 54.2% of wait time is PAGEIOLATCH (physical page reads).

### Performance Findings

**[C1] Physical I/O Dominant** (V1) — PAGEIOLATCH 54.2%, max 4,200 ms
- User impact: Queries taking 1–5 seconds longer due to disk waits
- Fix: Add covering indexes on highest-read tables; run /sqlindex-advisor

**[C2] Lock Waits Present** (V2) — LCK_M_S 16.0%, max 142,800 ms (2m 22s)
- User impact: Some users waited up to 2.4 minutes for a lock to release
- Configuration note (RCSI = OFF): ALTER DATABASE ProdDB SET READ_COMMITTED_SNAPSHOT ON

What Gets Checked

The output always includes a Server Configuration Context table (interprets MAXDOP, CTPfP, RCSI, TempDB files, recovery model, delayed durability, Always On commit mode against the wait types present), a Category column in the wait type table (I/O, Locks, Memory, Parallelism, Log, HA, etc.), an In context metric (concurrent blocked sessions avg), and a User impact line per finding. Pass the config capture query output alongside wait stats for config-aware analysis.

V1–V5 (Resource waits): Physical I/O (PAGEIOLATCH), lock waits (LCK_M_* incl. serializable range locks), parallelism (CXPACKET/CXCONSUMER), memory grants (RESOURCE_SEMAPHORE/RESOURCE_SEMAPHORE_QUERY_COMPILE), log I/O (WRITELOG/LOGBUFFER)

V6–V10 (Capacity waits): Client consumption (ASYNC_NETWORK_IO — almost always app-side), scheduler yield (SOS_SCHEDULER_YIELD — not always CPU pressure), thread exhaustion (THREADPOOL), TempDB contention (PAGELATCH), signal wait ratio (CPU saturation indicator)

V11–V13 (External waits): OLE DB / linked servers (OLEDB — millisecond waits are often benign monitoring tools), HA synchronization (HADR_SYNC_COMMIT), OS/CLR preemptive calls (PREEMPTIVE_*)

V14–V16 (Internal structures): Single bottleneck dominance, non-page latch contention (LATCH_EX/SH — requires sys.dm_os_latch_stats to diagnose), log space exhaustion (LOGMGR_RESERVE_APPEND — always Critical)

V17 (Diagnostic): Top-5 summary table — always fires

V18 (Poison/Throttle): Emergency waits — IO_RETRY (hardware failure), IO_QUEUE_LIMIT (storage full), LOG_RATE_GOVERNOR (Azure SQL tier cap), SE_REPL_* (SQL 2019+ Always On secondary lag throttling primary)

V19–V26 (Trend Analysis — activates with 3+ time-stamped snapshots): V19 trend direction (monotonically worsening/improving); V20 spike detection (single period ≥ 200% of own avg); V21 peak period — always fires (most stressed window); V22 velocity ranking — always fires (top 3 fastest-growing); V23 emerging wait types (absent at start, significant later); V24 correlated spikes (2+ waits spike together — shared root cause); V25 transient event detection (spike that resolved); V26 pattern classification — always fires (one-sentence workload pattern: degrading / stable / spike / improving)

sqltrace-review

Analyze SQL Server Profiler trace output (.trc), Extended Events session data (.xel), or sys.fn_trace_gettable() results. Applies 25 checks (X1–X12 event-level, X13–X25 workload aggregate) and produces a top-consumer ranking table plus a prioritized findings report.

Triggers

/sqltrace-review
/trace-review

How to capture trace data

Option A — sys.fn_trace_gettable() (existing .trc file)

SELECT EventClass, TextData, CPU, Reads, Writes, Duration,
       StartTime, SPID, ApplicationName, DatabaseName
FROM sys.fn_trace_gettable('C:\Traces\workload.trc', DEFAULT)
WHERE EventClass IN (10,12,16,37,50,54,65,69,79,80,92,93,146)
ORDER BY StartTime;

Paste the result grid into Claude.

Option B — Extended Events .xel file

SELECT event_data.value('(event/@name)[1]','NVARCHAR(100)') AS event_name,
       event_data.value('(event/data[@name="sql_text"]/value)[1]','NVARCHAR(MAX)') AS sql_text,
       event_data.value('(event/data[@name="duration"]/value)[1]','BIGINT') AS duration_us,
       event_data.value('(event/data[@name="cpu_time"]/value)[1]','BIGINT') AS cpu_ms,
       event_data.value('(event/data[@name="logical_reads"]/value)[1]','BIGINT') AS reads
FROM sys.fn_xe_file_target_read_file('C:\XE\workload*.xel',NULL,NULL,NULL)
CROSS APPLY (SELECT CAST(event_data AS XML)) AS ed(event_data);

Usage

/sqltrace-review

EventClass  TextData                                     CPU    Reads    Duration
12          SELECT * FROM dbo.Orders WHERE CustomerId=?  156    48291    142800000
12          EXEC dbo.GetReport @month=?                  84200  2568900  284906000
16          (Attention)                                  0      0        30000000

Or paste a CSV export from SSMS Profiler, or describe what the trace showed.

Output

## Trace Analysis

### Input Summary
- Total events: 48,291  |  Distinct queries (normalized): 142
- Event types: SQL:BatchCompleted, RPC:Completed, Attention, Sort Warnings

### Top Resource Consumers

By Total CPU (top 5)
| # | Query | Executions | Avg CPU ms | Total CPU ms | % |
|---|-------|-----------|-----------|-------------|---|
| 1 | EXEC dbo.GetReport... | 1,284 | 84,200 | 108 M | 41% |

### Performance Findings
[C1] Long-Duration Query (X1) — EXEC dbo.GetReport, 284,906 ms (4m 44s)
[W1] High-Frequency Query (X13) — SELECT * FROM Orders WHERE CustomerId=?, 48,291 executions
[W2] Parameter Sniffing Signal (X14) — GetReport: max 284,906 ms vs min 48 ms (5,935× variance)

What Gets Checked

Event-Level (X1–X12):

• Long-duration queries (≥ 5 s warning, ≥ 30 s critical)
• High CPU (≥ 5,000 ms), high logical reads (≥ 100 K / 1 M), high writes (≥ 10,000 pages)
• Attention events (client timeout/cancel), lock timeout events
• Recompile events (≥ 3 for same object), exception/error events (by severity)
• Sort warnings, hash warnings (spill to tempdb), missing statistics, missing join predicate

Workload-Level (X13–X20):

• High-frequency query pattern (≥ 1,000 executions — N+1 signal)
• Parameter sniffing signal (max > 10× min duration, same query)
• Ad-hoc / unparameterized workload detection
• Excessive global recompilation rate (> 5% of batch events)
• Top resource consumers table (always fires — top 5 by CPU, reads, duration)
• Workload concentration (top 3 queries > 80% of CPU)
• Auto-grow events (data or log file expansion during trace)
• ShowPlan XML events present (extract for /sqlplan-review)

sqlstats-review

Parse raw SET STATISTICS IO, TIME ON output from SSMS and produce a structured report with per-table IO metrics, per-statement timing, grand totals across all statements, and a prioritized findings section. Applies 27 checks (I1–I18 IO checks, W1–W9 time checks).

Triggers

/sqlstats-review
/stats-review
/stats-io

How to capture the input

In SSMS, run before your query:

SET STATISTICS IO, TIME ON;
GO
-- your query here
SET STATISTICS IO, TIME OFF;

Copy everything from the Messages tab and paste it into Claude.

Usage

Paste raw SSMS output:

/sqlstats-review

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

(48291 row(s) affected)
Table 'OrderLines'. Scan count 48291, logical reads 2568900, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 84210, physical reads 0, read-ahead reads 82150, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 18420 ms,  elapsed time = 18912 ms.

From a file:

/sqlstats-review path/to/stats-output.txt

Describe the output:

/sqlstats-review
OrderLines table: scan count 48,291, logical reads 2.5M. Elapsed 18 seconds.

Output

## Statistics IO/Time Analysis

### Input Summary
- 1 statement parsed
- Total logical reads: 2,653,110
- Total execution elapsed: 00:00:18.912

### Statement 1
**Compile Time:** CPU 0 ms | Elapsed 1 ms
**Rows Affected:** 48,291

| Table | Scan Count | Logical Reads | Physical Reads | Read-Ahead | % of Reads |
|-------|-----------|---------------|----------------|------------|------------|
| OrderLines | 48,291 | 2,568,900 | 0 | 0 | 96.826% |
| Orders | 1 | 84,210 | 0 | 82,150 | 3.174% |
| **Total** | **48,292** | **2,653,110** | | | |

**Execution Time:** CPU 18,420 ms (00:00:18.420) | Elapsed 18,912 ms (00:00:18.912)

### Performance Findings

[C1] Excessive Scan Count (I2) — OrderLines: scan count 48,291
[W1] High Logical Reads (I1) — 2,653,110 total reads (≥ 1M threshold)
[W2] Read-Ahead Pattern on Orders (I4) — 97.5% read-ahead ratio (scan indicator)

What Gets Checked

IO Checks (I1–I18):

• High logical reads per statement (warning ≥ 1 M, critical ≥ 10 M)
• Excessive scan count (inner-side Nested Loops indicator)
• High physical read ratio (cold cache or buffer pool pressure)
• Read-ahead dominant pattern (full scan signal)
• Single table dominating reads (≥ 80% of statement)
• Worktable/Workfile detected (spill to tempdb)
• Temp table, LOB reads, LOB dominant, columnstore segment skip rate
• Same table appearing multiple times, zero rows with high reads
• Physical reads non-zero, Azure page server reads

Time Checks (W1–W7):

• CPU << elapsed (I/O or lock wait dominant)
• CPU >> elapsed (parallel execution detected)
• High compile time relative to execution
• Long elapsed time (warning ≥ 30s, critical ≥ 5 min)
• High CPU time (≥ 60s)
• Multi-batch elapsed time variance
• High rows affected with low elapsed (large-volume DML risk)

tsql-review

Analyze raw T-SQL source code for anti-patterns, security vulnerabilities, and logic bugs. Applies 85 checks (T1–T85) across six categories and produces a prioritized Critical → Warning → Info report. No execution plan required — runs on source code before the query is ever executed.

Triggers

/tsql-review
/sql-review

Usage

From a .sql file:

/tsql-review path/to/procedure.sql

Paste inline T-SQL:

/tsql-review

SELECT * FROM dbo.Orders WHERE YEAR(OrderDate) = 2024

Describe the query:

/tsql-review
A stored procedure that builds a dynamic WHERE clause by concatenating parameters,
then calls EXEC(@sql). It also uses a cursor to update each row.

Output

## T-SQL Review

### Summary
- 2 Critical issues, 4 Warnings, 3 Info items
- Highest-risk: [C1] Dynamic SQL String Concatenation (T29)

### Critical Issues
**[C1] Dynamic SQL String Concatenation** (T29)
- Observed: SET @sql = N'SELECT * FROM dbo.Orders WHERE Status = ''' + @status + N''''
- Impact: SQL injection — an attacker controlling @status can execute arbitrary SQL
- Fix: Parameterize via sp_executesql: EXEC sp_executesql @sql, N'@p_status NVARCHAR(50)', @p_status = @status

**[C2] Missing WHERE on UPDATE** (T2)
- Observed: UPDATE dbo.Products SET Price = Price * 1.10 — no WHERE clause
- Impact: Updates every row in dbo.Products — likely unintentional data corruption
- Fix: Add WHERE clause; if full-table update is intentional, document with a comment

### Warnings
**[W1] Explicit Cursor Usage** (T7)
- Obse