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. 828 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

1. 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                                ║
║         │     108 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                              ║
║  16 patterns — lock order, RCSI bypass, MERGE, heap RID,    ║
║  TempDB, lock escalation, DTC, ledger/temporal               ║
║                                                              ║
║  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)
- Observed: DECLARE order_cursor CURSOR FOR SELECT OrderId FROM dbo.Orders WHERE Status = 'Pending'
- Impact: Row-by-row processing is 100x–1000x slower than a set-based UPDATE
- Fix: Rewrite as a set-based UPDATE ... FROM ... JOIN

### Passed Checks
T16 ✓, T29 ✗ (flagged), T33 ✓, T36 ✓, T39 ✓

What Gets Checked

Structural Anti-Patterns (T1–T15):

• SELECT * without explicit column list
• UPDATE/DELETE without WHERE clause
• Non-sargable predicates (functions on indexed columns, implicit type coercion, leading wildcard LIKE)
• Cursor usage, scalar UDFs in SELECT/WHERE, correlated subqueries in SELECT list
• CROSS JOIN without comment, OR in JOIN predicate, DISTINCT masking a bad join

Correctness and Logic (T16–T28):

• = NULL instead of IS NULL
• LEFT JOIN nullified by WHERE filter on right-side column
• Missing ORDER BY, missing TRY/CATCH, missing transaction scope
• UNION instead of UNION ALL, CASE branch type mismatch, missing ELSE in CASE
• CTE reuse and chain depth, deprecated SET ROWCOUNT, catch-all parameter patterns

Security and Dynamic SQL (T29–T38):

• SQL injection via string concatenation (EXEC(@string), user input in dynamic SQL)
• Hardcoded credentials, xp_cmdshell, EXECUTE AS without REVERT
• OPENROWSET with hardcoded connection string, linked server queries, missing schema prefix

Deprecated and Non-Idiomatic Syntax (T39–T45):

• Old-style outer join syntax (*=, =*), GROUP BY ALL, RAISERROR vs THROW
• GETDATE() where UTC precision preferred, INSERT without column list
• SET ANSI_NULLS OFF / SET QUOTED_IDENTIFIER OFF, temp table without explicit columns

Performance Smells (T46–T50):

• Table variable for large data (no statistics), string functions on large rowsets
• Deeply nested scalar subqueries, pagination without deterministic sort key
• Implicit collation or type conversion in comparisons

sqlplan-review

Analyze a single execution plan for performance anti-patterns. Applies 108 checks (S1–S36 statement-level, N1–N72 node-level) and produces a prioritized Critical → Warning → Info report with actionable fixes.

Triggers

/sqlplan-review
/plan-review

Usage

From a file:

/sqlplan-review path/to/query.sqlplan

Paste XML inline:

/sqlplan-review

<ShowPlanXML ...>
  ...
</ShowPlanXML>

Describe the plan (no XML):

/sqlplan-review
The plan has a Hash Match join with 5M estimated rows but 50 actual rows,
followed by a Key Lookup on dbo.Orders with 1.2M executions.

Output

## Execution Plan Analysis

### Summary
- 2 Critical, 5 Warnings, 3 Info items
- Primary bottleneck: Key Lookup (dbo.Orders) — 78% of plan cost

### Critical Issues
[C1] Implicit Conversion Affects Seek (S12) — ...
[C2] Confirmed Spill to TempDb Level 2 (N41) — ...

### Warnings
...

### Missing Indexes
CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId] ON [dbo].[Orders] (...)

### Passed Checks
S5, S6, N10, N20 — verified clean

What Gets Checked

Statement-level (S1–S36):

• Memory grant sizing and wait time
• Parallelism (serial plan, ineffective DOP, thread skew)
• Compile timeout and high compile CPU
• Downlevel cardinality estimator
• Table variables, plan hints, RECOMPILE cost
• Query Store forced plans, recursive CTEs, parameter count

Node-level (N1–N72):

• Scans vs seeks, Key Lookups, implicit conversions
• Sort/Hash spill risk and confirmed spills
• Bad row estimates (100× Info, 1000× Warning)
• Scalar UDFs, Nested Loops at scale, Eager Index Spools
• Columnstore operators (segment elimination, delta store, mixed batch/row mode)
• Window functions (no partition, RANGE frame spool)
• In-Memory OLTP cross-container joins
• JSON predicates, STRING_SPLIT at scale, large IN lists

sqlplan-compare

Diff two execution plans for the same query — one known-good (baseline) and one regressed — to identify exactly what the optimizer changed.

Triggers

/sqlplan-compare
/plan-compare
/plan-diff

Usage

/sqlplan-compare baseline.sqlplan new.sqlplan

Or paste both XML blocks labeled "Baseline" and "New".

What It Detects

• Seek degraded to Scan (Critical)
• Hash Join changed to Nested Loops on large table (Critical)
• New spill to TempDb introduced (Critical)
• Memory grant inflated or deflated > 2×
• Parallelism lost (DOP dropped to 1)
• New Key Lookup introduced
• New high-impact missing index suggestions
• Sort operator added
• Cardinality model downgraded

Output

Side-by-side metric table (cost, DOP, memory, join counts) followed by ranked regression findings with before/after values and fixes.

sqlindex-advisor

Produces a ranked CREATE INDEX script from two independent sources: operator-derived recommendations inferred from plan patterns, and the optimizer's explicit <MissingIndexGroup> suggestions. Both are merged, deduplicated, and ranked together.

Triggers

/sqlindex-advisor
/index-advisor
/missing-indexes

Usage

/sqlindex-advisor query.sqlplan
/sqlindex-advisor plans/report1.sqlplan plans/report2.sqlplan
/sqlindex-advisor plans/

What It Does

Source A — Operator-derived (independent analysis):

Source B — Optimizer suggestions: Extracts all <MissingIndexGroup> elements, merges overlapping key columns and INCLUDE lists per table.

Unified merge: Both sources are combined per table, deduplicated, and ranked by Impact × ln(1 + SourceCount).

Output

-- [I1] dbo.Orders — Score: 94.2 [both: D1 + optimizer]
-- Eliminates Key Lookup + matches optimizer suggestion (Impact 87.3)
CREATE NONCLUSTERED INDEX [IX_Orders_CustomerId_CreatedDate]
ON [dbo].[Orders] ([CustomerId], [CreatedDate])
INCLUDE ([Status], [TotalAmount])
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

-- [I2] dbo.LineItems — Score: 71.0 [derived: D6]
-- Inner-side scan in Nested Loops, 12,400 executions
CREATE NONCLUSTERED INDEX [IX_LineItems_OrderId]
ON [dbo].[LineItems] ([OrderId])
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);

sqldeadlock-review

Analyze a SQL Server deadlock XML graph to identify root cause (lock order conflict, missing index, isolation level) and produce a prioritized remediation plan.

Triggers

/sqldeadlock-review
/deadlock
/deadlock-analyze

Where to Get the Deadlock XML

• SSMS: Activity Monitor → Processes → right-click deadlock → Save Deadlock File As...
• system_health XE session:

  SELECT xdr.value('@timestamp', 'datetime2') AS [timestamp],
         xdr.query('.') AS deadlock_xml
  FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
  ) AS data
  CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS xdr(xdr);

• Trace flag 1222: Enable with DBCC TRACEON(1222, -1), output goes to SQL error log

Usage

/sqldeadlock-review deadlock.xdl

Or paste the <deadlock> XML block directly.

Patterns Detected

sqlplan-batch

Batch-analyze a folder of .sqlplan files. Applies all 108 checks to each plan and produces a single aggregated batch-analysis.md dashboard.

Triggers

/sqlplan-batch
/plan-batch
/batch-review

Usage

/sqlplan-batch C:\captures\slow-plans\

Or list specific files:

/sqlplan-batch plan1.sqlplan plan2.sqlplan plan3.sqlplan

Output (batch-analysis.md)

• Executive Summary — total Criticals, Warnings, spills, plans with > 1 GB grant
• Top 10 Most Expensive Plans — ranked by statement cost
• Top 10 Plans by Critical Issues — worst offenders
• Check Violation Frequency — which checks fire most often across all plans
• Spill Report — all plans with confirmed TempDb spills
• Consolidated Missing Index Script — merged and ranked across all plans
• Per-Plan Summary Table — one row per file with all key metrics

Workflow

Capture workload → save .sqlplan files → /sqlplan-batch folder/
                                              ↓
                                    batch-analysis.md
                                              ↓
                          Pick worst plans → /sqlplan-review plan.sqlplan
                           Fix indexes      → /sqlindex-advisor folder/

sqlquerystore-review

Analyze SQL Server Query Store data to identify regressed queries, plan instability, top resource consumers, query-level wait patterns, configuration issues, and SQL 2019/2022 IQP/PSP/DOP/CE feedback signals. Applies 32 checks (Q1–Q32) and maps every finding to companion skills for deep-dive analysis. Requires SQL Server 2016+.

Triggers

/sqlquerystore-review
/qs-review
/query-store

How to capture Query Store data

Run Query A (top resource consumers) — the primary capture query from the skill. Optionally add Query C (configuration) to audit Query Store health:

-- Query A: Top Resource Consumers (adjust @top_n and date range as needed)
DECLARE @top_n integer = 20;
DECLARE @start_date datetimeoffset = DATEADD(DAY, -7, GETUTCDATE());
DECLARE @end_date   datetimeoffset = GETUTCDATE();

SELECT TOP (@top_n)
    database_name   = DB_NAME(),
    query_sql_text  = TRY_CAST(qt.query_sql_text AS nvarchar(200)),
    object_name     = OBJECT_NAME(q.object_id),
    query_hash      = q.query_hash,
    plan_count      = COUNT(DISTINCT p.plan_id),
    total_executions    = SUM(rs.count_executions),
    avg_duration_ms     = SUM(rs.avg_duration) / NULLIF(SUM(rs.count_executions), 0) / 1000.0,
    avg_cpu_ms          = SUM(rs.avg_cpu_time) / NULLIF(SUM(rs.count_executions), 0) / 1000.0,
    avg_logical_reads   = SUM(rs.avg_logical_io_reads) / NULLIF(SUM(rs.count_executions), 0),
    avg_memory_grant_mb = SUM(rs.avg_query_max_used_memory) / NULLIF(SUM(rs.count_executions), 0) * 8.0 / 1024.0,
    max_duration_ms     = MAX(rs.max_duration) / 1000.0,
    is_forced_plan      = MAX(CASE WHEN p.is_forced_plan = 1 THEN 1 ELSE 0 END),
    force_failure_count = MAX(p.force_failure_count),
    aborted_count       = SUM(CASE WHEN rs.execution_type = 1 THEN rs.count_executions ELSE 0 END),
    exception_count     = SUM(CASE WHEN rs.execution_type = 2 THEN rs.count_executions ELSE 0 END)
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time >= @start_date AND rs.last_execution_time < @end_date
GROUP BY qt.query_sql_text, q.query_id, q.query_hash, q.object_id
HAVING SUM(rs.count_executions) > 0
ORDER BY SUM(rs.avg_cpu_time * rs.count_executions) DESC;

Paste the result grid into Claude alongside the configuration query output if available.

Usage

/sqlquerystore-review

[paste Query A output here]
[paste Query C output here — optional, enables health checks Q23–Q25]

Or provide a file path:

/sqlquerystore-review path/to/qs-output.txt

Output

• Query Store Health — current mode, storage %, capture mode, wait stats status
• Top Resource Consumers table — ranked by total CPU with key metrics per query
• Performance Findings — Critical → Warning → Info with check IDs (Q1–Q25)
• Query Wait Summary — per-query wait category breakdown (SQL 2017+ when enabled)
• Passed Checks — all checks verified clean for confidence

What Gets Checked

Regressed Queries (Q1–Q6): Duration regressed ≥ 2× (Q1), CPU regressed (Q2), logical reads regressed (Q3), new plan for stable query (Q4), variant plan with performance gap (Q5), forced plan failure on regressed query (Q6).

Plan Stability (Q7–Q12): ≥ 3 plans per query hash (Q7), forced plan failure (Q8), aborted rate > 10% (Q9), exception executions (Q10), RECOMPILE on infrequent query (Q11), plan feedback active — SQL 2022+ (Q12).

Resource Hotspots (Q13–Q18): Single query > 30% CPU (Q13), > 30% duration (Q14), > 30% reads (Q15), > 30% executions — N+1 signal (Q16), > 30% memory grant (Q17), top 3 > 80% of any metric (Q18).

Query-Level Waits (Q19–Q22, SQL 2017+): > 50% duration waiting (Q19), lock waits dominant (Q20), memory grant waits (Q21), network IO waits dominant (Q22).

Operational Health (Q23–Q25): Storage > 80% max (Q23), capture disabled or READ_ONLY (Q24), wait stats not enabled (Q25).

sqlspn-review

Analyze SQL Server SPN (Service Principal Name) configuration and Active Directory Kerberos delegation settings to diagnose authentication failures, NTLM fallback, and double-hop connectivity problems. Applies 40 checks (K1–K40) across seven categories: MSSQLSvc SPN presence (K1–K6), service account binding (K7–K11), AG listener and alias (K12–K16), configuration and permissions (K17–K20), Kerberos delegation (K21–K25), AD account sensitivity (K26–K30), and Azure AD / hybrid and advanced scenarios (K31–K40).

Triggers

/sqlspn-review

How to capture SPN and delegation data

setspn -Q MSSQLSvc/*
setspn -L DOMAIN\sqlsvc
setspn -X
Get-ADUser DOMAIN\sqlsvc -Properties TrustedForDelegation, TrustedToAuthForDelegation, msDS-AllowedToDelegateTo, ServicePrincipalNames, MemberOf
Get-ADComputer SQLNODE1 -Properties TrustedForDelegation, msDS-AllowedToActOnBehalfOfOtherIdentity, ServicePrincipalNames

Usage

/sqlspn-review

[paste setspn and Get-ADUser/Get-ADComputer output here]

Or describe the authentication failure:

/sqlspn-review
Linked server queries from SQLNODE1 to SQLBACK01 fail with
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".
Service account is CONTOSO\sqlsvc. SQL Server is the default instance.

Output

## SPN Review Analysis

### Summary
- 4 Critical, 1 Warning, 1 Info
- Service account: CONTOSO\sqlsvc
- SQL instances / listeners found: SQLNODE1 (default instance, port 1433)
- Highest-risk finding: Duplicate SPN (K8) + Unconstrained Delegation (K19)

### Critical Issues

### [C1 — K8] Duplicate SPN — MSSQLSvc/SQLNODE1:1433
- **Observed:** SPN registered on both CONTOSO\sqlsvc and decommissioned CONTOSO\sqlsvc_2024
- **Impact:** KDC cannot issue service tickets for SQLNODE1; all Kerberos logins fail
- **Fix:** setspn -D MSSQLSvc/SQLNODE1:1433 CONTOSO\sqlsvc_2024

### Passed Checks
| Check | Result |
|-------|--------|
| K1 — Missing Default-Instance SPN | PASS — MSSQLSvc/SQLNODE1:1433 and FQDN variant present |

What Gets Checked

SPN Presence (K1–K6): Default instance SPN (K1), named instance SPN (K2), FQDN variant (K3), short-hostname variant (K4), port mismatch (K5), FCI VNN SPN (K6).

Service Account Binding (K7–K11): SPN on wrong account (K7), duplicate SPNs across accounts (K8), SPN on computer account instead of service account (K9), stale SPNs from old accounts (K10), MSA/gMSA auto-registration gaps (K11).

AG Listener and Alias (K12–K16): Missing listener SPN (K12), named instance using port 1433 (K13), SQL Browser not running (K14), alias without SPN (K15), multi-subnet AG with incomplete SPN coverage (K16).

Configuration and Permissions (K17–K20): HTTP SPN missing for SSRS/linked server (K17), SPN registration permission gap (K18), unconstrained delegation enabled (K19), NTLM fallback signal (K20).

Kerberos Delegation — Service Account (K21–K25): Constrained delegation not configured for double-hop (K21), delegation target missing SPN (K22), protocol transition not enabled (K23), RBCD misconfigured (K24), delegation scope too broad (K25).

AD Account Sensitivity (K26–K30): Connecting user has AccountNotDelegated flag (K26), end-user in Protected Users group (K27), computer account SPN conflict (K28), computer account unconstrained delegation (K29), service account in Protected Users (K30).

sqlclusterlog-review

Analyzes Windows Server Failover Cluster (WSFC) CLUSTER.LOG files for Always On Availability Group root-cause diagnosis. Applies 30 checks (L1–L30) across five categories: file-wide patterns, AG resource transitions, network/node health, configuration signals, and modern cluster features (Cloud Witness, Azure Arc, Contained AG).

Triggers: /sqlclusterlog-review

Input: Paste content from CLUSTER.LOG (live: C:\Windows\Cluster\cluster.log; generated via Get-ClusterLog: C:\Windows\Cluster\Reports\CLUSTER.LOG) — covering at least 10 minutes before the incident across all nodes — or describe the symptoms in plain language.

Checks: 30 total —

• L1–L8 File-wide patterns: lease timeout, health check failure, RHS crash, error burst density, failover cycling, quorum loss, node eviction, log time gap
• L9–L17 AG resource: offline transition, SQL connectivity loss, forced failover, long pending state, hadrres.dll init failure, resource DLL API timeout, cascade across AGs, primary role loss, replica disconnection
• L18–L22 Network/node: network partition, cluster network interface failure, heartbeat timeout, witness access failure, node isolation
• L23–L25 Configuration signals: VerboseLogging=0, SeparateMonitor not set, missing node coverage
• L26–L30 Modern features: Cloud Witness repeated timeout, Azure Arc agent disconnect, Contained AG system database offline, cross-subnet probe failure, sp_server_diagnostics component warning

Usage:

/sqlclusterlog-review
[paste CLUSTER.LOG content]

Sample output: See skills/sqlclusterlog-review/examples/cluster-analysis.md.

sqlhadr-review

Analyzes sys.dm_hadr_* DMV output to assess Always On Availability Group replica health: synchronization state, secondary lag, redo/log send queue sizes, and configuration gaps. Applies 27 checks (H1–H28, with H21 retired and merged into sqlag-review F15) across six categories.

Triggers: /sqlhadr-review, /hadr-review

Input: Paste output from the sys.dm_hadr_* capture query in SKILL.md (run on the primary replica), a file path to saved query output, or describe the symptom ("secondary is 90 seconds behind", "replica shows NOT_HEALTHY").

Checks: 27 total (H1–H28, H21 retired) —

• H1–H6 Replica connectivity and role: disconnected, resolving state, sync unhealthy, not synchronizing, last connect error, manual failover on sync-commit
• H7–H11 Data loss and recovery time: estimated data loss, estimated recovery time, secondary lag, redo queue buildup, log send queue buildup
• H12–H16 Throughput: zero redo/send rate, redo/send rate mismatch, multiple databases lagging, commit latency signal
• H17–H20, H22 Configuration: async replica in sync-expected position, no automatic failover replica, single replica AG, listener not configured, automatic seeding active (H21 retired — read-only routing absence is now sqlag-review F15)
• H23–H27 Modern AG features: Contained AG misrouted DML, Cloud Witness inaccessible, parallel redo worker saturation, read-scale secondary missing RCSI, AG without database-level health detection
• H28 Seeding and initialization integrity: database stuck in INITIALIZING synchronization state, particularly after a failover

Usage:

/sqlhadr-review
[paste sys.dm_hadr_* DMV output]

Sample output: See skills/sqlhadr-review/examples/hadr_dmv_output-analysis.md.

sqlag-review

Audits Always On Availability Group configuration correctness across all layers — prerequisites, replica design, listener architecture, backup strategy, endpoint security, distributed AG topology, and Basic/Contained AG constraints. Applies 37 checks (F1–F37) across seven categories. Companion to /sqlhadr-review (runtime health) and /sqlclusterlog-review (WSFC events).

Triggers: /sqlag-review, /ag-review, /ag-config-review, /hadr-config, /distributed-ag-review, /ag-setup-review

Input: Paste output from the catalog-view capture queries in SKILL.md (run on the primary replica), a file path, or a natural language description of the AG topology and any known issues.

Checks: 37 total —

• F1–F6 Prerequisites: AlwaysOn feature disabled, database not in FULL recovery, endpoint missing/not started, endpoint encryption disabled, failure condition level at extremes, version mismatch
• F7–F13 Replica design: excessive sync-commit replicas, WAN session timeout too low, aggressive health check timeout, backup priority ties, incomplete replica join, missing databases after join, no readable secondary
• F14–F18 Listener/network: multi-subnet listener missing IP, read-only routing URL/list absent, non-default port, multi-subnet OFFLINE IP guidance
• F19–F23 Backup strategy: automated backup preference NONE, backup jobs not preferred-replica-guarded, log backups not scheduled, backup compression disabled, PRIMARY preference with 3+ replicas
• F24–F27 Endpoint security: Windows auth cross-domain, certificate expiring within 90 days, RC4 encryption, port documented as blocked
• F28–F33 Distributed AG / advanced: instance name instead of listener URL, Basic AG with multiple databases or readable secondary, Contained AG Windows endpoint auth, distributed AG stuck sync-commit, cross-database dependencies
• F34–F37 Operational monitoring: no Extended Events session for AG diagnostics, listener IP not conformant with Windows cluster, AG database-count scale ceiling exceeded, automatic seeding left active during a manual-restore workflow

Usage:

/sqlag-review
[paste AG catalog view output, or describe the topology]

Sample output: See skills/sqlag-review/examples/ag-config-analysis.md.

sqlerrorlog-review

Analyzes SQL Server ERRORLOG files for operational issues: availability group failures, memory pressure, I/O subsy