# 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, live blocking chain analysis, 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, SQL Server migration readiness (version/edition/platform compatibility, security-object portability, operational-object portability), and two-period workload comparison (what regressed between a baseline and a current capture, and which configuration, version or wait change explains it) — plus an agentic orchestrator that routes mixed artifacts to the right specialised skill. **950 checks across 29 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 | Skill | Purpose | |-------|---------| | [mssql-performance-review](#mssql-performance-review) | Agentic offline orchestrator — routes mixed artifacts (or a symptom description) to the right specialised skills, runs an adversarial root-cause check, and emits a single consolidated report with evidence chain, risk-rated fixes, and rollback. Use when you have several artifact types together or are not sure which skill to run. Also invocable as `/sql-triage` (equal-primary alias). | | [tsql-review](#tsql-review) | Analyze raw T-SQL source code — 85 checks (T1–T85) — structural, security, correctness, performance, SQL 2017–2022 modern syntax | | [sqlstats-review](#sqlstats-review) | Parse and analyze `SET STATISTICS IO, TIME ON` output — 27 checks (I1–I18 IO, W1–W9 time) | | [sqltrace-review](#sqltrace-review) | Analyze Profiler trace / Extended Events output — 27 checks (X1–X12 event-level, X13–X27 workload aggregate) | | [sqlwait-review](#sqlwait-review) | Analyze `sys.dm_os_wait_stats` — 45 checks (V1–V45) across I/O, lock, parallelism, memory, CPU, latch contention, log space, poison/throttle waits, backup I/O, insert hotspots, trend analysis, modern feature waits (In-Memory OLTP, Columnstore, Query Store, Transaction/DTC, Service Broker, Full Text, Parallel Redo), forced memory grants, grant timeouts, stolen memory, file I/O latency, IQP/PSP/ADR feature waits, TempDB metadata contention, and spinlock contention | | [sqlplan-review](#sqlplan-review) | Analyze a single `.sqlplan` file — 112 checks, prioritized report | | [sqlplan-compare](#sqlplan-compare) | Diff two plans to find what caused a regression | | [sqlindex-advisor](#sqlindex-advisor) | Derive indexes from operator patterns + consolidate optimizer suggestions into a ranked `CREATE INDEX` script | | [sqldeadlock-review](#sqldeadlock-review) | Analyze a deadlock XML graph — identify pattern and fix | | [sqlblocking-review](#sqlblocking-review) | Analyze a live or already-finished blocking incident — 55 checks (BL1–BL55): head blocker identification and state, chain topology, lock escalation and Sch-M evidence, transaction/isolation faults, per-index and Query Store lock wait history, statistics/lock-partitioning/foreign-key causes, ORM and platform patterns, capture and alerting readiness | | [sqlplan-batch](#sqlplan-batch) | Batch-analyze a folder of plans — dashboard + index script | | [sqlquerystore-review](#sqlquerystore-review) | Analyze Query Store DMV output — 32 checks for regressed queries, plan instability, resource hotspots, configuration health, SQL 2019/2022 IQP/PSP/DOP/CE feedback, QS hints, and auto-tuning | | [sqlprocstats-review](#sqlprocstats-review) | Analyze collected `sys.dm_exec_procedure_stats` snapshots — 25 checks (R1–R25) for top consumers, per-execution efficiency, N+1 patterns, trends, and advanced runtime patterns | | [sqlclusterlog-review](#sqlclusterlog-review) | Analyze WSFC `CLUSTER.LOG` for Always On AG root-cause diagnosis — 30 checks (L1–L30): lease timeouts, health check failures, quorum loss, node eviction, network partition, RHS crashes, Cloud Witness, Azure Arc, Contained AG | | [sqlhadr-review](#sqlhadr-review) | Analyze Always On AG health from `sys.dm_hadr_*` DMVs — 27 checks (H1–H28, H21 retired): replica connectivity, data loss risk, recovery time, throughput, configuration, modern AG features, seeding/initialization integrity | | [sqlag-review](#sqlag-review) | Audit Always On AG configuration correctness across all layers — 38 checks (F1–F38): prerequisites, replica design, listener architecture, backup strategy, endpoint security, distributed AG topology, Basic/Contained AG constraints, AG scale ceiling, automatic seeding/manual-restore mismatch | | [sqlerrorlog-review](#sqlerrorlog-review) | Analyze SQL Server ERRORLOG for operational issues — 34 checks (E1–E34): AG failover events, lease expiry, memory pressure, I/O slow, corruption warnings, login failure bursts, startup/shutdown, SQL 2019/2022 feature events | | [sqlspn-review](#sqlspn-review) | Analyze SQL Server SPN configuration and Kerberos delegation settings — 54 checks (K1–K54): SPN presence, service account binding, AG listener and alias, permissions, delegation, AD account sensitivity, Entra ID hybrid, gMSA rollover, CNAME alias, double-hop platform constraints (linked server, SSISDB), clock skew and token size, SQL Server on Linux keytabs, client driver limits | | [sqlmemory-review](#sqlmemory-review) | Analyze SQL Server memory pressure from memory clerks, PLE, plan cache, and memory grants — 20 checks (O1–O20): PLE, plan cache bloat, grant queue depth, grant timeout, ColumnStore/XTP footprint, OS pressure, LPIM, Max Server Memory | | [sqlperfmon-review](#sqlperfmon-review) | Analyze Windows Perfmon counter data for the SQL Server host — 15 checks (PM1–PM15): CPU attribution between SQL Server and other processes, privileged/kernel time, page life expectancy shape, virtual address space growth, OS available memory, per-volume disk latency, hot volume masked by `_Total`, compile/recompile rates against batch throughput, plan cache growth by cache type, batch request baseline shift, client cancellation rate, capture integrity | | [sqlworkload-compare](#sqlworkload-compare) | Compare two captures of the same workload — baseline and current, fast day and slow day, before and after a change — 14 checks (WC1–WC14): comparability, per-query regressions matched by `query_hash` and ranked by added time, regression class (CPU-, reads-, wait-driven), new and disappeared heavy queries, volume shifts, configuration/version/database-option differences, wait-profile shift | | [sqldiskio-review](#sqldiskio-review) | Analyze SQL Server file-level I/O latency and auto-growth events from sys.dm_io_virtual_file_stats — 15 checks (Z1–Z15): data/log latency, hot file, stall ratio, storage placement, TempDB co-location, auto-growth sizing and timing | | [sqlencryption-review](#sqlencryption-review) | Analyze the full SQL Server encryption posture from DMV output — 112 checks (A1–A112) across 20 categories: TDE, Always Encrypted, CLE, Backup, Transport/TLS, Certificates, Keys, Key Hierarchy, EKM/AKV, Compliance, TLS Hardening, AE Advanced, Key Lifecycle, Ledger, Azure, DMK Password Auto-Open (sp_control_dbmasterkey_password/SSISDB), DDM Patterns, Compliance Explicit (PCI-DSS v4/HIPAA/GDPR/FedRAMP), Operational Validation, Advanced Crypto Patterns | | [sqlbootstraplog-review](#sqlbootstraplog-review) | Analyze SQL Server Setup Bootstrap logs (Summary.txt, Detail.txt, MSI logs, ConfigurationFile.ini) to diagnose failed installs, failed CU/SP patches, and risky setup-time configuration — 25 checks (U1–U25): exit-code extraction, failed setup rules, pending reboot, MSI Return value 3 forensics, service accounts, IFI, TempDB layout, security surface, directory placement | | [sqldbconfig-review](#sqldbconfig-review) | Analyze SQL Server instance and database configuration drift from sp_configure and sys.databases output — 38 checks (B1–B38): MAXDOP alignment to NUMA, Cost Threshold for Parallelism, Max Server Memory, auto-shrink, auto-close, compatibility level, RCSI, page verification, auto-statistics, VLF count, percent auto-growth, Instant File Initialization, TempDB file count, surface area exposure, service-SID sysadmin membership, Common Criteria compliance, scheduler count vs OS CPU count, access check cache, power plan, non-Microsoft loaded modules | | [ssrstracelog-review](#ssrstracelog-review) | Analyze SQL Server Reporting Services (SSRS) report server trace logs, RSReportServer.config/ReportingServicesService.exe.config excerpts, ExecutionLog3 output, and Event Log entries — 24 checks (G1–G24): trace configuration health, report server database connectivity, memory pressure and AppDomain recycling, report processing/rendering performance, subscription delivery failures, scale-out and encryption key management | | [sqlmigration-review](#sqlmigration-review) | Audit SQL Server migration version/edition/platform compatibility — 15 checks (Y1–Y15): edition-gated features, version/compatibility-level ceiling, collation, discontinued features, In-Memory OLTP, Azure SQL limits, backup/log-chain readiness, AG seeding edition limits, source lifecycle urgency. Dispatcher with own checks | | [sqlmigration-security-review](#sqlmigration-security-review) | Audit security-object portability for a SQL Server migration — 15 checks (J1–J15): login portability, permission fidelity, credentials/proxies, certificates/keys, CMS registrations | | [sqlmigration-objects-review](#sqlmigration-objects-review) | Audit operational-object portability for a SQL Server migration — 16 checks (M1–M16): SQL Agent jobs/operators/alerts/proxies, linked servers, Database Mail, backup devices, custom errors, server triggers, XE sessions, endpoints | --- ## Quick Start **5 minutes to your first analysis:** 1. Install the skills (see [Installation](#installation) below) 2. In SSMS, open [skills/sqlplan-review/examples/horrible.sqlplan](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](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](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)** ```bash # Add the marketplace (once per machine or project) /plugin marketplace add vanterx/mssql-performance-skills # Install all 29 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** ```bash npx skills add vanterx/mssql-performance-skills # requires Node.js >= 18 ``` Uses the third-party [`skills` npm package](https://www.npmjs.com/package/skills) — not maintained by Anthropic or this repo. **Option 3: Manual** ```bash 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 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`: ```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: | MCP Primitive | What's exposed | |---------------|----------------| | **31 Tools** | `list_skills`, `get_skill`, `get_reference`, `route_artifact` (26 artifact types: `tsql`, `sqlplan`, `plancompare`, `planbatch`, `deadlock`, `blocking`, `waits`, `trace`, `stats`, `querystore`, `procstats`, `hadr`, `clusterlog`, `errorlog`, `spn`, `memory`, `diskio`, `encryption`, `dbconfig`, `setuplog`, `ssrslog`, `agconfig`, `migration`, `migrationsecurity`, `migrationobjects`, `mixed`), plus one tool per skill (×27) that accepts your artifact as `input` | | **Resources** | `mssql://skills` index, `mssql://skills/{name}` (×27), `mssql://skills/{name}/references` index plus one resource per reference file, `mssql://guide`, `mssql://version-compat` | | **26 Prompts** | One per skill — pass your artifact as `input` | > The server auto-deploys via GitHub Actions on every push. Source: [`mcp-server/`](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: | Target | Location | |--------|----------| | GitHub Copilot | `.github/copilot-instructions.md` (append) | | Cursor | `.cursor/rules/.mdc` (one per skill) | | Windsurf | `.windsurf/rules/` | | Cline | `.clinerules/` | | Aider | `.aider.conf.yml` under `custom-instructions` | 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](PERFORMANCE_TUNING_GUIDE.md) for symptom-based routing. > **Which checks apply to your SQL Server version?** See [VERSION_COMPATIBILITY.md](skills/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 ║ ║ 27 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 ║ ║ 45 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 ║ ║ │ 112 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 ║ ║ ║ ║ Blocking chain DMVs / blocked process report XML ║ ║ │ ║ ║ ▼ ║ ║ /sqlblocking-review blocking_capture.txt ║ ║ 55 checks — head blocker and whether it self-resolves, ║ ║ chain depth/fan-out, escalation, Sch-M, isolation, ORM ║ ║ defaults, per-index history when the chain is gone, BPR ║ ║ ║ ║ 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 ║ ║ 34 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 ║ ║ 38 checks — prerequisites, replica design, listener, ║ ║ backup strategy, endpoint security, distributed AG ║ ║ │ Kerberos / auth failure in ERRORLOG? ║ ║ ▼ ║ ║ /sqlspn-review ║ ║ 54 checks — MSSQLSvc SPN, AG listener, delegation, ║ ║ Entra ID hybrid, gMSA rollover, CNAME alias, linked ║ ║ server / SSISDB double-hop limits, clock skew, token ║ ║ size, encryption types, Linux keytabs, driver limits ║ ║ ║ ╚══════════════════════════════════════════════════════════════╝ ``` **Not sure which skill to use?** See [PERFORMANCE_TUNING_GUIDE.md](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 **45 checks** (V1–V45) based on the Waits and Queues methodology as practised by Paul Randal (SQLskills) and Brent Ozar's First Responder Kit. **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. **Spinlock contention (V45)** covers the CPU burned spinning on a spinlock, which never appears as a wait type — requires a `sys.dm_os_spinlock_stats` snapshot pair. ### Triggers ``` /sqlwait-review /wait-review /waits ``` ### How to capture wait statistics ```sql 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](PERFORMANCE_TUNING_GUIDE.md#the-server-is-slow-but-i-dont-know-why--queries-blocking-or-resource-pressure) 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 **27 checks** (X1–X12 event-level, X13–X27 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)** ```sql 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** ```sql 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: ```sql 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 **112 checks** (S1–S38 statement-level, N1–N74 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 ... ``` **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 `` 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):** | Rule | Operator Pattern | Recommendation | |------|-----------------|---------------| | D1 | Key Lookup / RID Lookup | Extend NC index with INCLUDE columns to eliminate the lookup | | D2 | Expensive scan with predicate (≥ 25% cost) | NC index on predicate columns | | D3 | Seek with residual predicate discarding > 80% of rows | Promote residual column to index key | | D4 | Eager Index Spool | Permanent NC index matching spool's seek predicate | | D5 | Costly Sort (≥ 10% cost) | Pre-ordered index matching filter + sort columns | | D6 | Nested Loops inner-side scan (> 1,000 executions) | NC index on inner table's join column | | D7 | Table Scan (heap) | Clustered index | | D8 | Backward scan | DESC index matching the reversed scan direction | **Source B — Optimizer suggestions:** Extracts all `` 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 ```sql -- [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); ``` --- ## sqlblocking-review Analyze a live SQL Server blocking chain: find the head blocker, decide whether the block will clear on its own, read the lock evidence, and name the transaction or isolation design fault behind it — and, when the incident is already over, work the historical evidence that survives it. 55 checks (BL1–BL55). ### Triggers ``` /sqlblocking-review /blocking-review /head-blocker /sqlblocking ``` ### Where to Get the Input - **Capture script (preferred):** `skills/sqlblocking-review/scripts/capture-blocking.sql`, run while the blocking is happening — twice, 30–60 seconds apart, if you can. ``` sqlcmd -S -E -i skills/sqlblocking-review/scripts/capture-blocking.sql -o blocking_capture_1.txt ``` - **Blocked process report:** off by default. Turn it on, then capture `blocked_process_report` in an Extended Events session: ```sql EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'blocked process threshold', 20; -- seconds; 5 is the minimum the lock monitor can detect RECONFIGURE; ``` - **SSMS:** Reports → Standard Reports → *Activity - All Blocking Transactions*, or Activity Monitor's **Blocked By** column. - **`sp_who2`** output (the `BlkBy` column) also works, with less detail. - **Community tools:** `sp_WhoIsActive @find_block_leaders = 1`, `sp_BlitzWho`, `sp_BlitzFirst @SinceStartup = 1`, `sp_HumanEvents`/`sp_HumanEventsBlockViewer`, or a blocking-tree script — paste the output as-is. - **After the fact:** run section 6 of the capture script for per-index lock hot spots, Query Store lock waits, and blocking counters. See [`references/community-practices.md`](skills/sqlblocking-review/references/community-practices.md) for the tooling map. ### Usage ``` /sqlblocking-review blocking_capture.txt ``` Or paste the DMV output, the blocked process report XML, or a description of the symptom. ### What It Checks | Group | Checks | Covers | |-------|--------|--------| | Chain topology | BL1–BL7 | Head blocker identification, wait duration, chain depth, fan-out, cross-database chains, concurrency exhaustion, chronic recurrence | | Head-blocker state | BL8–BL15 | Long-running query, sleeping session with an open transaction, orphaned transaction, rollback, client not fetching results, client/server distributed deadlock, non-lock wait at the head, maintenance work | | Lock evidence | BL16–BL23 | Lock escalation, object-level X locks, `Sch-M` blocking, hot resources, key-range locks, conversion waits, application locks, lock footprint | | Transaction and isolation | BL24–BL30 | Long open transactions, transactions across client round-trips, elevated isolation, implicit transactions, lock hints, RCSI candidates, version store side effects | | Observability and platform | BL31–BL36 | Blocked process threshold, capture targets, escalation overrides, scan-driven footprints, ADR and optimized locking | | Historical and aggregate evidence | BL37–BL42 | Instance-wide lock wait share, per-index lock hot spots and escalation attempts (`sys.dm_db_index_operational_stats`), Query Store lock waits, blocking performance counters, sampled blocking logs | | Structural and engine-level causes | BL43–BL48 | A benign head blocker with a `Sch-M` request queued behind it, statistics updates, lock partitioning, unindexed foreign keys, triggers and cascades, writes that lock every row they read | | Client, tooling, and platform | BL49–BL54 | ORM and driver transaction defaults, client timeout and retry policy, Azure SQL differences, readable-secondary redo blocking, commit acknowledgement waits, alerting and escalation path | The first question the analysis answers is whether the head blocker is **sleeping with an open transaction** (BL9/BL10 — it will not clear; kill it, then fix the client's error handling) or **actively running** (BL8 — it clears when the query finishes; tune the query instead). **Sample output:** See [`skills/sqlblocking-review/examples/blocking-analysis.md`](skills/sqlblocking-review/examples/blocking-analysis.md). --- ## 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:** ```sql 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 `` XML block directly. ### Patterns Detected | Pattern | Description | |---------|-------------| | P1 | Forward/reverse access order (fix: consistent lock order) | | P2 | Reader/writer conflict (fix: READ_COMMITTED_SNAPSHOT) | | P3 | Update lock escalation (fix: add index on WHERE columns) | | P4 | Missing index → page lock (fix: add row-level index) | | P5 | Key Lookup deadlock (fix: covering index, eliminate lookup) | | P6 | SERIALIZABLE phantom (fix: switch to SNAPSHOT isolation) | | P7 | Foreign key check conflict (fix: index FK column in child) | | P8 | Self-deadlock (fix: eliminate cursor, review MERGE) | --- ## sqlplan-batch Batch-analyze a folder of `.sqlplan` files. Applies all 112 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: ```sql -- 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 **54 checks** (K1–K54) across eleven 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), Entra ID / hybrid and advanced scenarios (K31–K40), double-hop platform constraints (K41–K44), Kerberos environment prerequisites (K45–K48), SQL Server on Linux keytabs (K49–K51), and client driver and service state (K52–K54). ### Triggers ``` /sqlspn-review ``` ### How to capture SPN and delegation data ```powershell 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). **Entra ID / Hybrid and Advanced (K31–K40):** Entra ID hybrid join SPN gap (K31), Entra-only auth with orphaned AD SPN (K32), Azure SQL MI Windows Authentication flow not configured (K33), gMSA password rollover SPN drift (K34), FCI node-specific SPN leak (K35), distributed AG forwarder listener SPN (K36), `TrustedToAuthForDelegation` set for an RBCD path (K37), encryption type mismatch between account and KDC policy (K38), Write-SPN blocked by AdminSDHolder (K39), DNS CNAME alias without SPN (K40). **Double-Hop Platform Constraints (K41–K44):** Linked server delegation path relies on RBCD, which linked servers do not support (K41); linked server constrained delegation below SQL 2017 CU17 (K42); SSISDB package double-hop under constrained delegation, which SSISDB does not support (K43); named instance dynamic port prevents a stable SPN (K44). **Kerberos Environment Prerequisites (K45–K48):** Clock skew beyond the 5-minute tolerance (K45), Kerberos token size exceeded — SQL error 17832 (K46), RC4-only encryption types under AES enforcement (K47), client and server across a forest boundary (K48). **SQL Server on Linux (K49–K51):** Keytab not configured in `mssql-conf` (K49), keytab encryption types mismatch the AD account (K50), keytab file ownership or permissions wrong (K51). **Client Driver and Service State (K52–K54):** Legacy provider cannot use Kerberos over named pipes (K52), service account password changed without a service restart (K53), report server missing `RSWindowsNegotiate` (K54). --- ## 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`](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`](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 38 checks (F1–F38) 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–F38 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, SQLDIAG failover diagnostics log turned off **Usage:** ``` /sqlag-review [paste AG catalog view output, or describe the topology] ``` **Sample output:** See [`skills/sqlag-review/examples/ag-config-analysis.md`](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 subsystem warnings, and security events. Applies 34 checks (E1–E34) across seven categories. **Triggers:** `/sqlerrorlog-review` **Input:** Paste content from the ERRORLOG file (default: `C:\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Log\ERRORLOG`) — ideally current + at least one prior (`ERRORLOG.1`) — or describe the symptoms / paste selected lines with context. **Checks:** 33 total — - E1–E8 AG/HA: failover event, lease expiry, replica state change, database joining failure, sync suspended, AG health check timeout, redo thread error, secondary not synchronising - E9–E14 Memory/resource pressure: FAIL_PAGE_ALLOCATION, OS memory pressure, insufficient memory, worker thread exhaustion, non-yielding scheduler, memory grant timeout - E15–E19 I/O and storage: I/O subsystem slow, database corruption warning, TempDB space exhaustion, log backup overdue, VLF proliferation signal - E20–E24 Startup/shutdown/connectivity: abnormal shutdown, repeated restarts, login failure burst, linked server error, connectivity error - E25–E28 Configuration/informational: trace flag active, Max Server Memory default, ERRORLOG rotation gap, SQL Server version - E29–E33 SQL 2019/2022 modern features: ADR PVS cleanup stall, IQP DOP feedback applied, Ledger verification failure, CE feedback model version change, Azure Arc agent disconnect **Usage:** ``` /sqlerrorlog-review [paste ERRORLOG content] ``` **Sample output:** See [`skills/sqlerrorlog-review/examples/ERRORLOG-analysis.md`](skills/sqlerrorlog-review/examples/ERRORLOG-analysis.md). --- ## sqlmemory-review Analyzes SQL Server memory pressure from buffer pool metrics (PLE), plan cache composition, memory grants, and memory clerk data. Applies 20 checks (O1–O20) across four categories. **Triggers:** `/sqlmemory-review`, `/memory-review`, `/ple-check` **Input:** Paste output from `sys.dm_os_memory_clerks`, `sys.dm_os_ring_buffers` (`RING_BUFFER_RESOURCE_MONITOR` or `RING_BUFFER_OOM`), `sys.dm_exec_query_memory_grants`, PLE counters, or `sys.dm_os_sys_memory` — any subset, combined or alone — or describe the symptom ("PLE is 200 and dropping, RESOURCE_SEMAPHORE is 15% of waits"). **Checks:** 20 total — - O1–O5 Buffer pool/PLE: low PLE, declining trend, NUMA node imbalance, single-database dominance, excessive stolen memory - O6–O10 Plan cache: single-use bloat, high compilation rate, oversized individual plan, excessive lock memory, low cache hit rate (churn) - O11–O15 Memory grants: queue depth, grant timeout, oversized grant, Resource Governor pool imbalance, Buffer Pool Extension active on SSDs - O16–O20 Memory clerk/OS/configuration: ColumnStore pressure, In-Memory OLTP (XTP) pressure, OS memory pressure notifications, LPIM misconfiguration, Max Server Memory not explicitly set **Usage:** ``` /sqlmemory-review [paste memory clerk / PLE / memory grant output] ``` **Sample output:** See [`skills/sqlmemory-review/examples/memory_pressure-analysis.md`](skills/sqlmemory-review/examples/memory_pressure-analysis.md). --- ## sqlperfmon-review Analyzes Windows Performance Monitor counter data for a SQL Server host to establish whether the operating system itself is the bottleneck and which process is responsible. Applies 15 checks (PM1–PM15) across five categories. Every other skill in this library reads something SQL Server produced about itself. This one reads what Windows observed about the process, which is the only way to answer three questions the engine cannot answer about itself: is the machine saturated, is it SQL Server doing it, and is the time going to kernel code the engine does not control. **Triggers:** `/sqlperfmon-review`, `/perfmon-review`, `/perfmon` **Input:** A `relog`-converted CSV from a `.blg` binary log (preferred), a Perfmon data collector CSV, `sys.dm_os_performance_counters` output (engine counters only — the host counters are not in the DMV, and those checks report NOT ASSESSED), a pasted extract of a few counters with timestamps, or describe the symptom ("sqlservr is at 1500% processor time on a 24-core box"). **Capture:** ``` rem What is in the log and over what time range relog C:\PerfLogs\sqlhost.blg -q rem Convert to CSV relog C:\PerfLogs\sqlhost.blg -f csv -o C:\temp\sqlhost.csv ``` **Checks:** 15 total — - PM1–PM3 CPU attribution: SQL Server process saturation, non-SQL process competition, privileged/kernel time share - PM4–PM6 Memory: page life expectancy shape over time, virtual address space growth against committed memory, OS available memory - PM7–PM8 Storage: per-volume logical disk latency, hot volume masked by the `_Total` instance - PM9–PM12 Compilation and workload: compiles and recompiles as a share of batch throughput, plan cache object growth by cache type, batch request baseline shift - PM13–PM15 Client behaviour and capture integrity: client cancellation (attention) rate, collection gaps and too-short sample windows, and a second `sqlservr` process that makes attribution ambiguous **On thresholds:** Microsoft documents what each counter measures but publishes no numeric target for most of them. This skill does not invent numbers to fill the gap. Where a threshold already exists elsewhere in the library it is reused (PM7 takes `/sqldiskio-review`'s latency thresholds so the two cannot disagree about the same disk; PM4 defers to `/sqlmemory-review` O1 for page life expectancy); where none exists, the check is a ratio the capture can compute (PM9, PM10) or a deviation from a baseline the user supplies (PM12). **Usage:** ``` /sqlperfmon-review [paste the relog CSV, or attach the converted file] ``` **Sample output:** See [`skills/sqlperfmon-review/examples/perfmon-counters-analysis.md`](skills/sqlperfmon-review/examples/perfmon-counters-analysis.md). --- ## sqlworkload-compare Compares two captures of the same workload — a baseline and a current period, a fast day and a slow day, before and after an upgrade or configuration change — and explains what changed. Applies 14 checks (WC1–WC14) across four categories. Every other skill in this library reviews one capture against thresholds. This one reviews two captures against each other, which answers a different question: not "what is wrong" but "what changed". A query that is slow on both days is not the cause of the difference, however slow it is. **Triggers:** `/sqlworkload-compare`, `/workload-compare`, `/compare-workload`, `/slow-vs-fast` **Input:** Two captures labelled baseline and current — preferably the output of `scripts/capture-workload-windows.sql`, which compares two time windows from one database's Query Store by `query_hash` and snapshots the configuration and wait statistics. Trace or Extended Events aggregates, `sys.dm_exec_query_stats` snapshot pairs, configuration and wait outputs from each side, or a description of each period also work. **Checks:** 14 total — - WC1 Comparability: window length, core count, sources and filters, equivalent business periods - WC2–WC10 Query-level change: regression and improvement matched by `query_hash` (full outer match, ranked by added time), new and disappeared heavy queries, systemic regression, regression class (CPU-, reads-, wait-driven), execution-volume shift with flat cost - WC11–WC13 Configuration change: `sp_configure` `value_in_use`, server properties (version, update level, edition, CPU, memory), database options and database-scoped configurations - WC14 Wait profile: dominant category change or ≥ 2× growth, normalised per second per CPU **On method:** the comparison descends from the one in `microsoft/SqlNexus`, and corrects its defects — it inner-joined its sides, took a top 30 per side before matching, applied its duration floor to one side only, compared totals across windows of different length, and reported a zero baseline as 999%. **Usage:** ``` /sqlworkload-compare [paste capture-workload-windows.sql output, or the baseline and current captures] ``` **Sample output:** See [`skills/sqlworkload-compare/examples/workload-windows-analysis.md`](skills/sqlworkload-compare/examples/workload-windows-analysis.md). --- ## sqldiskio-review Analyzes SQL Server file-level I/O latency and auto-growth events from `sys.dm_io_virtual_file_stats`, `sys.master_files`, and default trace auto-growth records. Applies 15 checks (Z1–Z15) across three categories. **Triggers:** `/sqldiskio-review`, `/diskio-review`, `/io-latency` **Input:** A snapshot pair from `sys.dm_io_virtual_file_stats` (preferred — two captures with the delta calculated), a single cumulative capture, `sys.master_files` output, default-trace auto-growth events, or describe the symptom ("log file grew three times today, data drive showing 80ms reads"). **Checks:** 15 total — - Z1–Z5 Latency/stall: data file read latency, data file write latency, log file write latency, hot data file, high stall ratio - Z6–Z10 Storage placement/configuration: data and log on same volume, TempDB on same volume as user databases, TempDB log file latency, file count imbalance, database file on system drive - Z11–Z15 Auto-growth patterns: events in last 24h, data file growth too small, log file growth too small, growth during production hours, I/O stall trend worsening **Usage:** ``` /sqldiskio-review [paste sys.dm_io_virtual_file_stats / sys.master_files output] ``` **Sample output:** See [`skills/sqldiskio-review/examples/diskio-analysis.md`](skills/sqldiskio-review/examples/diskio-analysis.md). --- ## Collection Framework Each skill's `scripts/` directory contains T-SQL and PowerShell capture scripts that collect SQL Server DMV data and produce report output for that skill. ### Folder map | Folder | Purpose | Feeds skill | |--------|---------|-------------| | `skills/sqlprocstats-review/scripts/collection/` | Persistent collectors — schema, tables, SPs, Agent job, PowerShell deployment | `/sqlprocstats-review`, `/sqlwait-review` | | `skills/sqlwait-review/scripts/` | Ad-hoc `sys.dm_os_wait_stats` capture (single snapshot + differential) | `/sqlwait-review` | | `skills/sqltrace-review/scripts/` | Extended Events session setup + ring buffer / file / `.trc` readers | `/sqltrace-review` | | `skills/sqldeadlock-review/scripts/` | system_health reader + dedicated XE session for deadlock capture | `/sqldeadlock-review` | | `skills/sqlquerystore-review/scripts/` | Queries A–D for Query Store analysis | `/sqlquerystore-review` | | `skills/sqlplan-review/scripts/` | Plan cache, running query, and Query Store plan extraction | `/sqlplan-review`, `/sqlplan-compare` | | `skills/sqlstats-review/scripts/` | `SET STATISTICS IO, TIME ON` capture template | `/sqlstats-review` | ### Quick deploy (full collection framework) ```powershell # Deploy all collectors to a new monitoring database .\skills\sqlprocstats-review\scripts\collection\Deploy-DmvCollection.ps1 ` -ServerInstance 'SQL01\PROD' ` -Database 'DBAMonitor' ` -Collectors All ` -IncludeAgentJob ` -TrustServerCertificate # Deploy only wait stats collector .\skills\sqlprocstats-review\scripts\collection\Deploy-DmvCollection.ps1 ` -ServerInstance 'SQL01' ` -Collectors WaitStats # Preview without executing .\skills\sqlprocstats-review\scripts\collection\Deploy-DmvCollection.ps1 ` -ServerInstance 'SQL01' ` -Collectors All ` -WhatIf ``` **Collectors:** `All` | `ProcStats` | `WaitStats` | `QueryStats` | `FileIo` | `Memory` | `PerfCounters` ### After deployment ```sql -- Run a manual collection EXECUTE collect.usp_CollectAll @debug = 1; -- View results (paste into /sqlprocstats-review) -- See skills/sqlprocstats-review/scripts/collection/04_report_queries.sql — proc/trigger/function stats (Q1-Q5) -- See skills/sqlprocstats-review/scripts/collection/12_report_all_collections.sql — all collectors + health log -- Verify all collectors are succeeding SELECT collector_name, MAX(collection_time) AS last_run, MAX(status) AS last_status FROM collect.collection_log GROUP BY collector_name ORDER BY collector_name; ``` --- ## sqlprocstats-review Analyzes runtime statistics collected from `sys.dm_exec_procedure_stats`, `sys.dm_exec_trigger_stats`, and `sys.dm_exec_function_stats` via the collection framework in `skills/sqlprocstats-review/scripts/collection/`. Applies 25 checks (R1–R25) across five categories. **Triggers:** `/sqlprocstats-review`, `/proc-stats` **Input:** Paste output from any of the 5 reporting queries in `skills/sqlprocstats-review/scripts/collection/04_report_queries.sql`. **Checks:** 25 total — R1–R5 (top consumers), R6–R10 (per-execution efficiency), R11–R15 (pattern detection), R16–R20 (trend analysis, requires ≥ 3 snapshots), R21–R25 (advanced runtime patterns: natively compiled, CLR, trigger dominance, parallel-to-serial, QS instability). **Usage:** ``` # Single-snapshot analysis (Q1–Q4) /sqlprocstats-review [paste Q1 output] # Trend analysis (Q5 — requires ≥ 3 collections) /sqlprocstats-review [paste Q5 output] ``` **Setup (deploy collection framework first):** ```sql -- Run in order against your DBA/monitoring database: -- skills/sqlprocstats-review/scripts/collection/01_create_tables.sql -- skills/sqlprocstats-review/scripts/collection/02_usp_collect_procstats.sql -- skills/sqlprocstats-review/scripts/collection/03_usp_calculate_deltas.sql -- skills/sqlprocstats-review/scripts/collection/04_report_queries.sql (reporting queries — paste output into /sqlprocstats-review) -- skills/sqlprocstats-review/scripts/collection/05_create_agent_job.sql (optional Agent job) -- Collect manually: EXECUTE collect.usp_CollectProcStats; ``` **Sample output:** See [`skills/sqlprocstats-review/examples/proc_stats_output-analysis.md`](skills/sqlprocstats-review/examples/proc_stats_output-analysis.md). --- ## sqlencryption-review Analyzes the complete SQL Server encryption posture by reviewing output from DMVs covering all encryption layers. Applies 112 checks (A1–A112) across 20 categories: TDE, Always Encrypted, Cell-Level Encryption, Backup Encryption, Transport/TLS, TLS Hardening, Certificate Management, Asymmetric & Symmetric Keys, Key Hierarchy, EKM/AKV, Compliance, AE Advanced, Operational Key Lifecycle, Ledger, Azure-Specific, DMK Password Auto-Open, DDM Patterns, Compliance Explicit, Operational Validation, and Advanced Cryptographic Patterns. **Triggers:** `/sqlencryption-review`, `/encryption-review`, `/tde-review`, `/always-encrypted-review`, `/backup-encryption-review`, `/tls-review`, `/ledger-review`, `/ssisdb-review`, `/data-masking-review` **Input:** Paste output from the DMV collection queries in `SKILL.md` — or paste any subset relevant to your question. The skill adapts to partial input. **Checks:** 112 total — - A1–A8: TDE (database encryption state, scan progress, cert expiry/backup, DEK algorithm, sharing) - A9–A16: Always Encrypted (deterministic vs randomized, enclave, algorithm, CMK store, sensitive column gaps, CEK rotation) - A17–A21: Cell-Level Encryption (deprecated algorithms, unclosed keys, password-only protection, stale keys, double-encryption) - A22–A25: Backup Encryption (missing encryption, cert backup, weak algorithm, cert expiry) - A26–A30: Transport/TLS (ForceEncryption, live plaintext sessions, self-signed cert, TrustServerCertificate, cert expiry) - A31–A38: Certificate Management (private key protection, SB/AG endpoint cert age, cert-based logins, SHA1/MD5 hash, self-signed, backup, duplicate CN) - A39–A43: Asymmetric & Symmetric Keys (RSA_512/1024, CONTROL permission, stale keys, orphaned keys, KEY_SOURCE reuse) - A44–A48: Key Hierarchy (DMK backup, SMK encryption, DMK password-only, SMK backup, linked server encryption) - A49–A52: EKM / Azure Key Vault (inactive provider, AKV rotation policy, service-managed vs CMK, provider version) - A53–A56: Compliance (sensitivity classifications, sensitive column patterns, FIPS violations, audit gaps) - A57–A62: TLS Hardening (legacy TLS versions, weak ciphers, TLS 1.3, IPsec, Kerberos armoring, Named Pipes) - A63–A67: AE Advanced (enclave attestation URL, driver compatibility, CEK caching, enclave utilization, relaxed attestation) - A68–A72: Operational Key Lifecycle (password strength, AD CS auto-enrollment, key escrow, TDE scan baselining, log backup encryption) - A73–A76: Ledger (enablement, digest storage, hash algorithm, verification schedule) - A77–A80: Azure-Specific (key vault region, double encryption, attestation isolation, audit log encryption) - A81–A86: DMK Password Auto-Open (sp_control_dbmasterkey_password, SSISDB, SMK restore invalidation, no auto-open path, cross-server restore, AG secondaries) - A87–A91: DDM and Permissions (masking without encryption, UNMASK on broad role, CONTROL on certificate, RLS on AE columns, CLE cipher text leakage) - A92–A98: Compliance Explicit (PCI-DSS v4 PAN column encryption, annual key rotation evidence, GDPR Art.17 ledger conflict, FIPS mode, non-HSM EKM, key custodian policy, HIPAA PHI audit) - A99–A104: Operational Validation (job step passwords, plan cache exposure, AKV soft-delete/purge, DR restore test, credential rotation, AG listener TLS) - A105–A112: Advanced Crypto Patterns (cipher suite ordering, NTLM remote auth, SB remote cert, ENCRYPTBYPASSPHRASE, HASHBYTES deprecated, Database Mail, ENCRYPTBYCERT expiry, Azure MI AKV permissions) **Recommended DMV collection queries:** ```sql -- 1. Database encryption state SELECT database_id, name, is_encrypted FROM sys.databases; -- 2. DEK details + algorithm SELECT database_id, DB_NAME(database_id) AS db_name, encryption_state, encryption_state_desc, percent_complete, encryptor_type, key_algorithm, key_length, encryptor_thumbprint, set_date FROM sys.dm_database_encryption_keys; -- 3. Certificates (master DB) USE master; SELECT name, certificate_id, pvt_key_encryption_type_desc, issuer_name, subject, expiry_date, thumbprint, start_date FROM sys.certificates WHERE name NOT LIKE '##%'; -- 4. Symmetric keys (run per user database) SELECT name, symmetric_key_id, algorithm_desc, key_length, create_date, modify_date, is_open FROM sys.symmetric_keys WHERE name NOT LIKE '##MS_DatabaseMasterKey##'; -- 5. Sensitive-pattern columns without Always Encrypted SELECT DB_NAME() AS database_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, c.name AS column_name, c.column_encryption_key_id, tp.name AS user_type_name FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.types tp ON c.user_type_id = tp.user_type_id WHERE c.name LIKE '%ssn%' OR c.name LIKE '%credit_card%' OR c.name LIKE '%salary%' OR c.name LIKE '%password%' OR c.name LIKE '%dob%' OR c.name LIKE '%tax_id%'; -- 6. Recent backups — encryption state SELECT database_name, backup_start_date, type AS backup_type, key_algorithm, encryptor_thumbprint FROM msdb.dbo.backupset WHERE backup_start_date > DATEADD(DAY, -30, GETDATE()) ORDER BY database_name, backup_start_date DESC; -- 7. Active remote connections — TLS state SELECT session_id, client_net_address, encrypt_option, auth_scheme, protocol_type FROM sys.dm_exec_connections; ``` **Usage:** ``` /sqlencryption-review [paste DMV output from the queries above] ``` **Sample output:** See [`skills/sqlencryption-review/examples/encryption-audit-analysis.md`](skills/sqlencryption-review/examples/encryption-audit-analysis.md). --- ## sqldbconfig-review Analyzes SQL Server instance and database configuration drift against proven DBA best practices. Applies 38 checks (B1–B38) across seven categories: parallelism tuning, memory configuration, database-level settings, file and storage configuration, surface area exposure, scheduler and process settings, and host and licensing capacity. **Triggers:** `/sqldbconfig-review`, `/dbconfig-review`, `/config-audit` **Input:** Paste output from `EXEC sp_configure`, `sys.databases`, `sys.master_files`, `sys.dm_os_sys_info`, and `sys.dm_db_log_info` — or any subset. The skill also accepts natural language descriptions of symptoms. Use `scripts/capture-dbconfig.sql` to collect all inputs in one query run. **Checks:** 38 total — - B1–B5 Parallelism: MAXDOP vs. NUMA, Cost Threshold for Parallelism, Optimize for Ad Hoc Workloads - B6–B9 Memory: Max Server Memory, Min Server Memory, LPIM model, AWE - B10–B18 Database settings: auto-shrink, auto-close, compatibility level, RCSI, page verification, auto-statistics, Trustworthy, cross-DB chaining - B19–B23 File/Storage: VLF count, percent auto-growth (log + data), Instant File Initialization, TempDB file count - B24–B29 Surface area: CLR, OLE Automation, Ad Hoc Distributed Queries, instance chaining, remote admin connection, service-SID sysadmin membership - B30–B32 Scheduler and process: priority boost, lightweight pooling, min server memory pinned to max - B33–B38 Host and licensing capacity: Common Criteria compliance, scheduler count below CPU count, access check cache, Windows power plan, non-Microsoft loaded modules, high-overhead XE sessions or SQL Traces left running **Usage:** ``` /sqldbconfig-review [paste sp_configure + sys.databases + sys.master_files output] ``` **Sample output:** See [`skills/sqldbconfig-review/examples/config-drift-analysis.md`](skills/sqldbconfig-review/examples/config-drift-analysis.md). --- ## sqlbootstraplog-review Analyzes SQL Server Setup Bootstrap log files to diagnose failed installations, failed Cumulative Update / Service Pack patching, failed cluster node operations, and risky setup-time configuration. Applies 25 checks (U1–U25) across five categories: summary/outcome, rule-failure patterns, Detail.txt/MSI forensics, ConfigurationFile.ini review, and the Windows Installer cache. **Triggers:** `/sqlbootstraplog-review`, `/bootstrap-log`, `/setup-log` **Input:** Paste content from `Summary.txt`, `Detail.txt`, MSI/MSP logs, `ConfigurationFile.ini`, or `SystemConfigurationCheck_Report.htm` from `%programfiles%\Microsoft SQL Server\\Setup Bootstrap\Log\\` (or `%temp%\sqlsetup*.log` for unattended runs) — or describe the failure in plain language. **Checks:** 25 total — - U1–U6 Summary/outcome: final result failed, component error codes, failed rules, partial feature failure, per-instance patch failure, update-phase failure - U7–U13 Rule patterns: pending reboot, disk space, accounts/permissions, prerequisites, cluster rules, policy blockers, Global Rules phase - U14–U18 Forensics: Detail.txt end-of-file exceptions, MSI "Return value 3", setup crashes, cascade ordering, Datastore state - U19–U24 ConfigurationFile.ini: service accounts, instant file initialization, TempDB layout, security surface, feature sprawl, directory placement - U25 Windows Installer cache: a cached .msi/.msp missing from `%windir%\Installer`, which blocks every later patch, repair and uninstall **Reference script:** [`skills/sqlbootstraplog-review/scripts/check-pending-reboot.ps1`](skills/sqlbootstraplog-review/scripts/check-pending-reboot.ps1) — pre-flight pending-reboot gate for installs and patches (exit 0 = clear, 1 = reboot pending). **Usage:** ``` /sqlbootstraplog-review [paste Summary.txt / Detail.txt / ConfigurationFile.ini content] ``` **Sample output:** See [`skills/sqlbootstraplog-review/examples/setup_summary-analysis.md`](skills/sqlbootstraplog-review/examples/setup_summary-analysis.md). --- ## ssrstracelog-review Analyzes SQL Server Reporting Services (SSRS) report server trace logs, configuration files, ExecutionLog3 query output, and Windows Event Log entries to diagnose failed startups, report server database connectivity errors, memory pressure and AppDomain recycling, slow or aborted report processing, subscription delivery failures, and scale-out/encryption-key problems. Applies 24 checks (G1–G24) across six categories. **Triggers:** `/ssrstracelog-review`, `/ssrs-trace-review`, `/rs-trace-review` **Input:** Paste content from `ReportServerService_.log` (or `Microsoft.ReportingServices.Portal.WebHost_.log` for SSRS 2017+), the `` section of `ReportingServicesService.exe.config`, the `` section of `RSReportServer.config`, `ExecutionLog3` query output, Windows Application Event Log entries for "Report Server Windows Service", or describe the symptom in plain language. **Checks:** 24 total — - G1–G4 Trace configuration and log health: `DefaultTraceSwitch` level, file size/retention, restart/recycle frequency from trace file rollover, stale component-level trace overrides - G5–G9 Startup and report server database connectivity: `rsReportServerDatabaseUnavailable`, `rsReportServerDatabaseLogonFailed`, `rsErrorOpeningConnection` sub-causes, orphaned database pointers, `rsServerConfigurationError` - G10–G13 Memory pressure and AppDomain recycling: `MemorySafetyMargin`/`MemoryThreshold`/`WorkingSetMaximum` configuration, hard recycles from memory allocation failures, `RecycleTime`/`MaxAppDomainUnloadTime` tuning - G14–G18 Report processing and rendering: `ExecutionLog3` time-phase breakdown, legacy processing engine usage, `rsProcessingAborted` and error statuses, external image fetch latency - G19–G21 Subscriptions and delivery: file share and email delivery extension failures, subscription scheduling clustering - G22–G24 Scale-out and encryption keys: `rsInvalidReportServerDatabase` after upgrade, `rskeymgmt -j` join failures, missing symmetric key backups **Reference script:** [`skills/ssrstracelog-review/scripts/collect-ssrs-diagnostics.ps1`](skills/ssrstracelog-review/scripts/collect-ssrs-diagnostics.ps1) — collects trace configuration, server configuration, trace log rollover counts, recent ERROR/Exception lines, and related Event Log entries in one pass. **Usage:** ``` /ssrstracelog-review [paste ReportServerService_.log / config / ExecutionLog3 / Event Log content] ``` **Sample output:** See [`skills/ssrstracelog-review/examples/ssrs_trace-analysis.md`](skills/ssrstracelog-review/examples/ssrs_trace-analysis.md). --- ## sqlmigration-review Audits SQL Server migration compatibility — edition-gated features, version and compatibility-level ceilings, collation, discontinued features, In-Memory OLTP portability, Azure SQL platform limits, backup/log-chain readiness for the chosen migration mechanism, AG-seeding edition limits, and source lifecycle urgency. Applies 15 checks (Y1–Y15) across five categories. Dispatcher with its own checks — routes security-object and operational-object findings to `/sqlmigration-security-review` and `/sqlmigration-objects-review`, and overlap areas (AG, Kerberos, TDE/certs, config drift) to `/sqlag-review`, `/sqlhadr-review`, `/sqlspn-review`, `/sqlencryption-review`, `/sqldbconfig-review`. **Triggers:** `/sqlmigration-review`, `/migration-review`, `/sql-migration-readiness` **Input:** Pasted source/target server facts, output from [`skills/sqlmigration-review/scripts/capture-migration-facts.sql`](skills/sqlmigration-review/scripts/capture-migration-facts.sql), a natural-language description of the migration, or a file path to exported artifacts. Supports backup & restore and log shipping/Always On AG seeding as migration mechanisms; covers SQL Server 2008 R2 through 2022 plus Azure SQL Database/Managed Instance. **Checks:** 15 total — - Y1–Y4 Version & edition compatibility: edition-gated feature in use on source, target version below source's compatibility-level floor, discontinued feature still referenced, collation mismatch between source database and target instance - Y5–Y7 Platform compatibility / Azure SQL: unsupported cross-database/instance-level feature targeting Azure SQL Database, Managed Instance feature gap, In-Memory OLTP without Azure SQL DB tier support - Y8–Y13 Migration mechanism readiness: backup chain gap blocking restore, recovery model mismatch for log shipping, AG seeding into a database already in an AG, distributed AG topology gap, Basic AG database-count/readable-secondary cap exceeded, Contained AG constraint violation - Y14 Lifecycle: source SQL Server version past Microsoft's end-of-support date - Y15 Source topology transition: Failover Cluster Instance source retired without a client redirect plan **Usage:** ``` /sqlmigration-review [paste source/target facts, or describe the migration] ``` **Sample output:** See [`skills/sqlmigration-review/examples/migration-facts-analysis.md`](skills/sqlmigration-review/examples/migration-facts-analysis.md). --- ## sqlmigration-security-review Audits the security-object migration plan for a SQL Server move — login portability, server/database permission fidelity, credentials and proxies, certificates/keys, and Central Management Server registrations. Applies 15 checks (J1–J15) across four categories. Dispatched here from `/sqlmigration-review` for the security-object family; companion to `/sqlspn-review` (Kerberos) and `/sqlencryption-review` (key hierarchy detail). **Triggers:** `/sqlmigration-security-review`, `/migration-security-review`, `/sql-migration-logins` **Input:** Pasted login/permission facts, output from [`skills/sqlmigration-security-review/scripts/capture-security-facts.sql`](skills/sqlmigration-security-review/scripts/capture-security-facts.sql), a natural-language description, or a file path to exported artifacts. **Checks:** 15 total — - J1–J5 Login portability: orphaned database user, SID mismatch from manually recreated login, login type unsupported on Azure SQL Database, password policy not migrated, default database missing on target - J6–J9 Permission fidelity: lost server role membership, lost database role membership during orphan repair, explicit GRANT/DENY not migrated, cross-database ownership chain broken - J10–J12 Credentials & secrets: `sys.credentials` secret cannot be scripted and must be re-entered, proxy created before its backing credential, linked server stored login not migrated - J13–J14 Certificates & keys: certificate-backed object migrated without its certificate, database master key not backed up before migration - J15 CMS: stale Central Management Server registration pointing at the source instance **Usage:** ``` /sqlmigration-security-review [paste login/permission facts, or describe the migration] ``` **Sample output:** See [`skills/sqlmigration-security-review/examples/security-facts-analysis.md`](skills/sqlmigration-security-review/examples/security-facts-analysis.md). --- ## sqlmigration-objects-review Audits the operational-object migration plan for a SQL Server move — SQL Agent jobs/operators/alerts/proxies, linked servers, Database Mail, backup devices, custom error messages, server-level triggers, Extended Events sessions, and endpoints. Applies 16 checks (M1–M16) across eight categories. Dispatched here from `/sqlmigration-review` for the operational-object family; companion to `/sqlmigration-security-review` (credential sequencing) and `/sqlag-review` (endpoint/AG overlap). **Triggers:** `/sqlmigration-objects-review`, `/migration-objects-review`, `/sql-migration-instance-objects` **Input:** Pasted instance-object facts, output from [`skills/sqlmigration-objects-review/scripts/capture-objects-facts.sql`](skills/sqlmigration-objects-review/scripts/capture-objects-facts.sql), a natural-language description, or a file path to exported artifacts. **Checks:** 16 total — - M1–M6 SQL Agent: job step database scope stale after rename, owner login unresolved on target, operator notification address unreachable, alert tied to a custom message not migrated, proxy migrated without its credential, schedule tuned for the source's time zone - M7–M9 Linked servers: provider not installed on target, network path unreachable from new location, `collation_compatible` setting drift - M10–M11 Database Mail: profile/account not migrated, SMTP relay allow-list excludes target's outbound IP - M12 Backup infrastructure: backup device path unreachable on target - M13 Custom errors: `sys.messages` entry not migrated before a dependent alert relies on it - M14 Server triggers: DDL/logon trigger definition not migrated - M15 XE sessions: user-defined Extended Events session definition not migrated - M16 Endpoints: non-AG endpoint definition not migrated **Usage:** ``` /sqlmigration-objects-review [paste instance-object facts, or describe the migration] ``` **Sample output:** See [`skills/sqlmigration-objects-review/examples/objects-facts-analysis.md`](skills/sqlmigration-objects-review/examples/objects-facts-analysis.md). --- ## mssql-performance-review Agentic offline orchestrator. Routes mixed SQL Server artifacts (or a symptom description) to the right specialised skills, runs an adversarial root-cause check, and emits a single consolidated report with explicit evidence chain, risk-rated fixes, and rollback steps for every recommendation. **Strictly offline.** The orchestrator never contacts a SQL Server. It only reads files you provide and emits scripts you run yourself. Air-gap compatible; no connection strings; no consent prompts. > **New user?** Read [`skills/mssql-performance-review/HOW_TO_USE.md`](skills/mssql-performance-review/HOW_TO_USE.md) — a guide covering installation, every input mode, every flag, three worked end-to-end examples, output interpretation, follow-up Q&A patterns, domain memory setup, the capture-bundle workflow, the baseline-diff verification loop, troubleshooting, and the offline trust model. **Triggers:** `/mssql-performance-review`, `/mssql-perf-review`, `/mssql-full-review`, `/sql-triage` **Input modes:** | Mode | Example | |------|---------| | Directory | `/mssql-performance-review ./incident-20260517/` | | File list | `/mssql-performance-review ./slow-proc.sqlplan ./wait-stats.txt` | | Inline paste | paste content blocks directly into chat | | Symptom-only | `/sql-triage CPU pegged at 95% on PROD-SQL01 since 09:00` | | Resume from bundle | `/mssql-performance-review --resume ./captures//` | | Verification (baseline-diff) | `/mssql-performance-review --baseline ./state//state.json ./new-captures/` | **Questions to ask** — give it the artifacts and ask what you want to know: | Question | Answered mainly by | |----------|--------------------| | What is the dominant bottleneck, and which queries use the most CPU, including parameter-sensitive ones? | `sqlwait-review`, `sqlquerystore-review`, `sqlprocstats-review`, `sqltrace-review` | | Is the slow I/O the storage or SQL Server reading too much? | `sqldiskio-review`, `sqlperfmon-review`, `sqlwait-review` | | Who is the head blocker and why does it hold its locks? | `sqlblocking-review` | | Which queries were waiting rather than running, and on what? | Wait-bound query attribution (trace batches joined to `sys.dm_exec_requests` snapshots) | | What changed between the fast run and the slow run? | `sqlworkload-compare`, `sqlplan-compare` | | Give me a prioritised summary with risk and rollback. | Consolidated Fix Priority | Each report opens with a one-line AI-generated notice, checks first that the artifacts cover the incident window and each other, and names the capture script and SQL LogScout scenario for anything missing. To strip host names, logins and addresses before sharing an artifact, run [`scripts/redact_artifact.py`](skills/mssql-performance-review/scripts/README.md) on it: it substitutes consistent tokens (`HOST_01`), so cross-artifact correlations survive. **Eleven cross-cutting primitives** (the full agentic stack): | Primitive | What it does | |-----------|--------------| | Evidence chain | Every Critical finding cites ≥3 check IDs from ≥2 skills with source artifact + observed value + threshold | | Risk-aware recommendations | Every fix has action + effort + window + risk class + side effects + rollback + verification + confidence | | Adversarial root-cause check | Mandatory pass that tries to disprove the primary hypothesis before declaring a root cause | | Confidence-driven early termination | Stop probing when 3+ skills converge HIGH with no contradiction | | Multi-model routing | Haiku for triage, Sonnet for synthesis, Opus for the adversarial pass | | Skill-graph DAG | Dynamic dispatch graph — probes that depend on each other sequence correctly; everything else runs in parallel | | Domain memory | Per-instance facts (MAXDOP, AG topology, partitioning) inform every recommendation | | Follow-up Q&A | After the report, ask "why?" — most answers are free from the in-context evidence chain | | Capture bundle generator | When artifacts are missing, emit read-only `.sql` scripts for you to run | | Verification checklist | Every recommendation specifies which capture to re-run and expected metric change | | Baseline-diff feedback loop | After deploy, `--baseline` tags each prior recommendation as verified-effective / partial / no-change / regressed-elsewhere | **Command flags:** | Flag | Effect | |------|--------| | `--exhaustive` | Run every applicable skill even after 3+ converge | | `--phases` | Fixed five-phase dispatch instead of dynamic DAG | | `--baseline ` | Tag prior recommendations after deploy | | `--resume ` | Continue from a capture-bundle paste-back | | `--instance ` | Load `~/.mssql-perf-review/instances/.json` domain memory | | `--capture-instance-facts` | Emit DMV survey bundle for populating `facts.json` | | `--save-session` | Persist session state for later `--baseline` | **Workflow at a glance:** ``` artifacts ──► /mssql-performance-review ──► consolidated report │ symptom ──► /sql-triage ──► capture bundle ◄──┘ (if artifacts missing) │ ▼ you run scripts in SSMS │ ▼ paste outputs into bundle │ ▼ /mssql-performance-review --resume ──► consolidated report │ ▼ deploy fixes │ ▼ re-capture (24h later) │ ▼ /mssql-performance-review --baseline ──► verification report │ ▼ feedback.jsonl appends (future hypothesis learning) ``` **Sample outputs:** - [`skills/mssql-performance-review/examples/mixed-artifacts-analysis.md`](skills/mssql-performance-review/examples/mixed-artifacts-analysis.md) — artifact-driven review with full report - [`skills/mssql-performance-review/examples/symptom-first-analysis.md`](skills/mssql-performance-review/examples/symptom-first-analysis.md) — symptom-only triage with capture-bundle response - [`skills/mssql-performance-review/examples/capture-bundle-example/`](skills/mssql-performance-review/examples/capture-bundle-example/) — sample bundle (README, scripts, paste-back template, manifest.json) - [`skills/mssql-performance-review/examples/baseline-diff-analysis.md`](skills/mssql-performance-review/examples/baseline-diff-analysis.md) — verification report 24h after deploy with recommendation tags **When to use this vs a specialised skill directly:** - Use **this** when you have multiple artifact types together, only a symptom, or want cross-skill validation and an adversarial check. - Use the **specialised skill** when you have one artifact and know which skill it needs (e.g., one `.sqlplan` → `/sqlplan-review`). The orchestrator does not duplicate the specialised skills — it composes them. **Deep references** (loaded on demand by the orchestrator): | File | Topic | |------|-------| | [`references/check-explanations.md`](skills/mssql-performance-review/references/check-explanations.md) | Methodology, dispatch heuristics, hypothesis classes, conflict catalogue | | [`references/evidence-schema.md`](skills/mssql-performance-review/references/evidence-schema.md) | `evidence.json` schema, validation rules, human rendering | | [`references/risk-rubric.md`](skills/mssql-performance-review/references/risk-rubric.md) | Risk-class definitions, environmental escalators, rollback rules | | [`references/adversarial-prompts.md`](skills/mssql-performance-review/references/adversarial-prompts.md) | Disproof templates per hypothesis class | | [`references/model-routing.md`](skills/mssql-performance-review/references/model-routing.md) | Per-sub-skill model assignment, quality safeguards | | [`references/skill-dag.md`](skills/mssql-performance-review/references/skill-dag.md) | DAG construction, static + dynamic edge catalogue | | [`references/domain-memory.md`](skills/mssql-performance-review/references/domain-memory.md) | `facts.json` schema, rejection/escalation catalogue | | [`references/followup-qa.md`](skills/mssql-performance-review/references/followup-qa.md) | Question taxonomy, when-to-probe rules | | [`references/capture-bundle-spec.md`](skills/mssql-performance-review/references/capture-bundle-spec.md) | Bundle layout, manifest schema, resume flow | | [`references/verification-checklist.md`](skills/mssql-performance-review/references/verification-checklist.md) | Tagging rules, timing guidance, feedback.jsonl schema | --- ## Thresholds Quick Reference | Metric | Info | Warning | Critical | |--------|------|---------|----------| | Memory grant | ≥ 512 MB | ≥ 1 GB | ≥ 4 GB | | Memory grant wait | — | > 0 ms | ≥ 5,000 ms | | Compile CPU | — | ≥ 1,000 ms | ≥ 5,000 ms | | Row estimate mismatch | 100×–999× | ≥ 1,000× | — | | Nested loop executions | > 1,000 | > 10,000 | — | | Spill to TempDb | — | Level 1 | Level ≥ 2 | | Key Lookup rows | — | > 1,000 rows or executions | — | | Operator cost share | — | ≥ 25% | ≥ 50% | | Missing index suggestions | — | > 5 in one plan | — | | IN list seek ranges | — | > 20 ranges | — | --- ## Example See [`skills/sqlplan-review/examples/horrible-analysis.md`](skills/sqlplan-review/examples/horrible-analysis.md) for a full analysis of [`skills/sqlplan-review/examples/horrible.sqlplan`](skills/sqlplan-review/examples/horrible.sqlplan) — a deliberately bad plan demonstrating parameter sniffing, memory grant failure, TempDb spills, leading-wildcard LIKE, implicit conversion, and 5M-row key lookups. --- ## Check Reference | Range | Count | Skill | Category | |-------|-------|-------|----------| | T1–T85 | 85 | tsql-review | T-SQL static analysis | | I1–I18 | 18 | sqlstats-review | Statistics IO checks | | W1–W9 | 9 | sqlstats-review | Statistics time/wait checks | | X1–X27 | 27 | sqltrace-review | Trace event-level + workload | | V1–V45 | 45 | sqlwait-review | Wait statistics + trend analysis + IQP/PSP/ADR waits + spinlock contention | | S1–S38 | 38 | sqlplan-review | Execution plan — statement-level | | N1–N74 | 74 | sqlplan-review | Execution plan — node-level | | C1–C20 | 20 | sqlplan-compare | Plan regression checks | | D1–D13 | 13 | sqlindex-advisor | Index derivation rules | | P1–P17 | 17 | sqldeadlock-review | Deadlock patterns | | BL1–BL55 | 55 | sqlblocking-review | Blocking chain: head blocker identification and state classification, chain topology, lock escalation/Sch-M/key-range evidence, transaction and isolation design, blocked process report readiness | | Q1–Q32 | 32 | sqlquerystore-review | Query Store health + regressions + IQP/PSP/CE/DOP feedback | | R1–R25 | 25 | sqlprocstats-review | Procedure/trigger/function stats | | L1–L30 | 30 | sqlclusterlog-review | WSFC cluster log analysis | | H1–H28 | 27 | sqlhadr-review | Always On AG health checks, including seeding/initialization integrity (H21 retired — merged into sqlag-review F15) | | F1–F38 | 38 | sqlag-review | AG configuration correctness: prerequisites, replica design, listener, backup, endpoint security, distributed AG, advanced features, scale ceiling, manual-restore seeding mismatch | | E1–E34 | 34 | sqlerrorlog-review | ERRORLOG operational analysis | | K1–K54 | 54 | sqlspn-review | SPN and Kerberos delegation analysis | | O1–O20 | 20 | sqlmemory-review | Memory pressure: PLE, plan cache, grants, clerks | | PM1–PM15 | 15 | sqlperfmon-review | Windows Perfmon counters — CPU attribution, memory, per-volume disk latency, compilation rates, capture integrity | | WC1–WC14 | 14 | sqlworkload-compare | Two-capture workload comparison — comparability, per-query regression by query_hash and its class, new/disappeared heavy queries, volume shifts, configuration and version differences, wait-profile shift | | Z1–Z15 | 15 | sqldiskio-review | File I/O latency and auto-growth | | A1–A112 | 112 | sqlencryption-review | TDE, Always Encrypted, CLE, backup, transport, TLS hardening, certs, keys, EKM, compliance, AE advanced, key lifecycle, Ledger, Azure, DMK password auto-open, DDM patterns, compliance explicit, operational validation, advanced crypto | | B1–B38 | 38 | sqldbconfig-review | Configuration drift: MAXDOP/NUMA, CTP, Max Server Memory, auto-shrink, auto-close, compat level, RCSI, page verification, VLF count, percent growth, IFI, TempDB file count, surface area, service-SID sysadmin membership, Common Criteria compliance, scheduler vs CPU count, access check cache, power plan, non-Microsoft loaded modules | | U1–U25 | 25 | sqlbootstraplog-review | Setup Bootstrap logs: failed install/patch outcome, setup rules, Detail.txt/MSI forensics, ConfigurationFile.ini review | | G1–G24 | 24 | ssrstracelog-review | SSRS trace logs: trace config health, report server DB connectivity, memory/AppDomain recycling, report processing/rendering, subscription delivery, scale-out/encryption keys | | Y1–Y15 | 15 | sqlmigration-review | Migration compatibility: edition-gated features, version/compat-level ceiling, collation, discontinued features, In-Memory OLTP, Azure SQL limits, backup/log-chain readiness, AG seeding limits, lifecycle, FCI client-redirect planning | | J1–J15 | 15 | sqlmigration-security-review | Migration security objects: login portability, permission fidelity, credentials/proxies, certificates/keys, CMS registrations | | M1–M16 | 16 | sqlmigration-objects-review | Migration operational objects: SQL Agent jobs/operators/alerts/proxies, linked servers, Database Mail, backup devices, custom errors, server triggers, XE sessions, endpoints | | (none) | — | mssql-performance-review | Agentic orchestrator (dispatcher; no own checks) | | **Total** | **950** | **29 skills** | |