CLAUDE.md git:20261028.d6bebb7A

Immutable. This exact content never changes and is served at /api/v1/blob/8883a87e3d6be2b9.

# mssql-performance-skills

A Claude Code skills library for SQL Server performance tuning — T-SQL static analysis, execution plan review, I/O statistics, workload traces, index recommendations, deadlock diagnosis, live blocking chain analysis, regression detection, and batch workload analysis.

## Purpose

Provides twenty-nine slash-command skills — twenty-five specialised review skills, three SQL Server migration-readiness skills, plus one agentic orchestrator (`mssql-performance-review`) that dispatches the right specialised skill(s) to mixed artifact inputs. Specialised skills cover T-SQL source code, `.sqlplan` XML files, STATISTICS IO/TIME output, Profiler/XE trace data, deadlock graphs, index recommendations, live blocking chains (head blocker identification, lock escalation and Sch-M evidence, transaction and isolation design faults), wait statistics, Query Store data, procedure/trigger/function runtime stats collected from `sys.dm_exec_procedure_stats`, Always On AG health from `sys.dm_hadr_*` DMVs, Always On AG configuration correctness (prerequisites, replica design, listener architecture, backup strategy, endpoint security, distributed AG topology, Basic and Contained AG constraints), Windows Server Failover Cluster log files, SQL Server ERRORLOG files, SQL Server SPN and Kerberos delegation configuration, server memory pressure analysis, file-level I/O latency analysis, full encryption infrastructure (TDE, Always Encrypted, CLE, backup encryption, TLS, certificate/key lifecycle, key hierarchy, EKM, compliance), instance/database configuration drift (MAXDOP, Max Server Memory, auto-shrink, compatibility level, RCSI, VLF count, IFI, TempDB sizing, surface area), SQL Server Setup Bootstrap log analysis (failed installs/patches, setup rules, ConfigurationFile.ini review), SQL Server Reporting Services (SSRS) report server trace log analysis (trace configuration, database connectivity, memory/AppDomain recycling, report processing performance, subscription delivery, scale-out encryption keys), SQL Server migration readiness (version/edition/platform compatibility, security-object portability, operational-object portability) for moves between on-prem instances and to/from Azure SQL, and Windows Performance Monitor counter analysis for the host itself (CPU attribution between SQL Server and other processes, privileged/kernel time, page life expectancy shape, virtual address space growth, per-volume disk latency, compilation rates against batch throughput, client cancellation rate, capture integrity), and two-period workload comparison (baseline against current, fast day against slow day, before and after a change: per-query regressions matched by query_hash and classified as CPU-, reads- or wait-driven, new and disappeared heavy queries, execution-volume shifts, and configuration, version and wait-profile differences). No application code — content is Markdown only.

## Tech Stack

- **Format:** Markdown + YAML frontmatter
- **Runtime:** Claude Code skill loader (reads `SKILL.md` files from `~/.claude/skills/`)
- **No build system, no tests, no runtime dependencies** — skills are Markdown. Dev-only lint tooling (ruff, PSScriptAnalyzer, ScriptDom) gates the repo's scripts and the T-SQL in its docs; see *Linting* under Development Constraints

## Key Files

### Skills (SKILL.md — loaded at runtime)

| File | Purpose |
|------|---------|
| [skills/mssql-performance-review/SKILL.md](skills/mssql-performance-review/SKILL.md) | Agentic offline orchestrator: `mssql-performance-review`. No checks of its own (dispatcher, like `sqlplan-batch`). Routes mixed artifacts to the 28 specialised skills, runs adversarial root-cause check, emits evidence chain + risk-rated fixes + rollback. |
| [skills/tsql-review/SKILL.md](skills/tsql-review/SKILL.md) | Static T-SQL source analysis: `tsql-review`. 85 checks (T1–T85) — structural, correctness, security, deprecated syntax, performance smells, SQL 2017–2022 modern syntax |
| [skills/sqlstats-review/SKILL.md](skills/sqlstats-review/SKILL.md) | STATISTICS IO/TIME parser + analysis: `sqlstats-review`. 27 checks (I1–I18 IO, W1–W9 time), per-statement tables, grand totals |
| [skills/sqltrace-review/SKILL.md](skills/sqltrace-review/SKILL.md) | Profiler / XE trace analysis: `sqltrace-review`. 27 checks (X1–X12 event-level, X13–X27 workload aggregate), top-consumer tables |
| [skills/sqlwait-review/SKILL.md](skills/sqlwait-review/SKILL.md) | Wait statistics analysis: `sqlwait-review`. 45 checks (V1–V45) — I/O, lock, parallelism, memory, CPU, latch, log space, poison/throttle waits, backup I/O, insert hotspots, cumulative skew, trend analysis, modern feature waits, memory grants, file I/O latency, IQP/PSP/ADR waits, TempDB metadata |
| [skills/sqlplan-review/SKILL.md](skills/sqlplan-review/SKILL.md) | Runtime plan analysis: `sqlplan-review`. 112 checks (S1–S38, N1–N74), thresholds, output format |
| [skills/sqlplan-compare/SKILL.md](skills/sqlplan-compare/SKILL.md) | Regression detection: `sqlplan-compare`. Diff two plans (C1–C20) |
| [skills/sqlindex-advisor/SKILL.md](skills/sqlindex-advisor/SKILL.md) | Index recommendations: `sqlindex-advisor`. Derive indexes from operator patterns (D1–D13) + optimizer suggestions + missing index DMVs |
| [skills/sqldeadlock-review/SKILL.md](skills/sqldeadlock-review/SKILL.md) | Deadlock analysis: `sqldeadlock-review`. 17 patterns (P1–P17), lock cycle extraction, remediation |
| [skills/sqlblocking-review/SKILL.md](skills/sqlblocking-review/SKILL.md) | Live and historical blocking analysis: `sqlblocking-review`. 55 checks (BL1–BL55) — head blocker identification and state classification, chain topology, lock escalation/Sch-M/key-range evidence, transaction and isolation design, blocked process report readiness, per-index and Query Store lock wait history, statistics/lock-partitioning/foreign-key causes, ORM and Azure platform patterns |
| [skills/sqlplan-batch/SKILL.md](skills/sqlplan-batch/SKILL.md) | Batch workload: `sqlplan-batch`. Aggregate dashboard across many `.sqlplan` files |
| [skills/sqlquerystore-review/SKILL.md](skills/sqlquerystore-review/SKILL.md) | Query Store analysis: `sqlquerystore-review`. 32 checks (Q1–Q32) — regressed queries, plan instability, resource hotspots, query waits, operational health, IQP/PSP/DOP/CE feedback, QS hints, auto-tuning |
| [skills/sqlprocstats-review/SKILL.md](skills/sqlprocstats-review/SKILL.md) | Procedure/trigger/function runtime stats analysis: `sqlprocstats-review`. 25 checks (R1–R25) — top consumers, per-execution efficiency, pattern detection, trend analysis |
| [skills/sqlclusterlog-review/SKILL.md](skills/sqlclusterlog-review/SKILL.md) | WSFC cluster log analysis: `sqlclusterlog-review`. 30 checks (L1–L30) — lease timeouts, health check failures, quorum loss, node eviction, network partition, RHS crashes, AG resource transitions |
| [skills/sqlhadr-review/SKILL.md](skills/sqlhadr-review/SKILL.md) | Always On AG health analysis: `sqlhadr-review`. 27 checks (H1–H28, H21 retired — merged into sqlag-review F15) — replica connectivity, data loss risk, recovery time, throughput, configuration, and seeding/initialization integrity |
| [skills/sqlag-review/SKILL.md](skills/sqlag-review/SKILL.md) | Always On AG configuration audit: `sqlag-review`. 38 checks (F1–F38) — prerequisites, replica design, listener architecture, backup strategy, endpoint security, distributed AG topology, Basic and Contained AG constraints, operational monitoring |
| [skills/sqlerrorlog-review/SKILL.md](skills/sqlerrorlog-review/SKILL.md) | SQL Server ERRORLOG analysis: `sqlerrorlog-review`. 34 checks (E1–E34) — AG failover events, lease expiry, memory pressure, I/O slow, corruption warnings, login failure bursts, startup/shutdown, and configuration signals |
| [skills/sqlspn-review/SKILL.md](skills/sqlspn-review/SKILL.md) | SPN and Kerberos delegation analysis: `sqlspn-review`. 54 checks (K1–K54) — MSSQLSvc SPN presence, service account binding, AG listener and alias, permissions, Kerberos delegation, AD account sensitivity, Entra ID hybrid and Azure SQL MI Windows Auth, double-hop platform constraints (linked server RBCD, SSISDB, SQL 2017 CU17 floor), Kerberos environment prerequisites (clock skew, token size, encryption types, forest boundary), SQL Server on Linux keytabs, client driver and service state |
| [skills/sqlmemory-review/SKILL.md](skills/sqlmemory-review/SKILL.md) | Memory pressure analysis: `sqlmemory-review`. 20 checks (O1–O20) — PLE, plan cache bloat, memory grants, memory clerks, ColumnStore/XTP footprint, OS pressure, LPIM, Max Server Memory |
| [skills/sqldiskio-review/SKILL.md](skills/sqldiskio-review/SKILL.md) | File-level I/O latency and auto-growth analysis: `sqldiskio-review`. 15 checks (Z1–Z15) — data/log latency, hot files, stall ratio, storage placement, auto-growth events and sizing |
| [skills/sqlperfmon-review/SKILL.md](skills/sqlperfmon-review/SKILL.md) | Windows Perfmon counter analysis: `sqlperfmon-review`. 15 checks (PM1–PM15) — SQL process CPU saturation, non-SQL CPU competition, privileged/kernel time share, 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 object growth by cache type, batch request baseline shift, client cancellation (attention) rate, counter collection integrity |
| [skills/sqlworkload-compare/SKILL.md](skills/sqlworkload-compare/SKILL.md) | Two-capture workload comparison: `sqlworkload-compare`. 14 checks (WC1–WC14) — capture comparability, per-query 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 shifts, `sp_configure` / server property / database option / database-scoped configuration differences, wait-profile shift normalised per second per CPU |
| [skills/sqlencryption-review/SKILL.md](skills/sqlencryption-review/SKILL.md) | Full SQL Server encryption infrastructure analysis: `sqlencryption-review`. 112 checks (A1–A112) — TDE, Always Encrypted, CLE symmetric keys, backup encryption, transport/TLS, certificate lifecycle, asymmetric/symmetric key management, DMK/SMK key hierarchy (including sp_control_dbmasterkey_password and SSISDB), EKM/AKV, TLS/network hardening, Always Encrypted advanced (enclave attestation, driver compatibility), operational key lifecycle, SQL Server 2022 Ledger, Azure-specific encryption, dynamic data masking patterns, compliance explicit checks (PCI-DSS v4, HIPAA, GDPR, FedRAMP, CMMC, NY-DFS), operational validation (job step passwords, plan cache exposure, AKV soft-delete), advanced cryptographic patterns (ENCRYPTBYPASSPHRASE, HASHBYTES, Service Broker certs, NTLM) |
| [skills/sqldbconfig-review/SKILL.md](skills/sqldbconfig-review/SKILL.md) | Instance and database configuration drift analysis: `sqldbconfig-review`. 38 checks (B1–B38) — MAXDOP/NUMA alignment, Cost Threshold for Parallelism, Optimize for Ad Hoc Workloads, Max Server Memory, LPIM, auto-shrink, auto-close, compatibility level, RCSI, page verification, auto-statistics, Trustworthy, cross-DB chaining, VLF count, percent auto-growth, Instant File Initialization, TempDB file count, surface area exposure, service-SID sysadmin membership, Common Criteria compliance overhead, scheduler count below OS CPU count, access check cache overrides, Windows power plan |
| [skills/sqlbootstraplog-review/SKILL.md](skills/sqlbootstraplog-review/SKILL.md) | SQL Server Setup Bootstrap log analysis: `sqlbootstraplog-review`. 25 checks (U1–U25) — failed install/patch outcome and exit codes, failed setup rules (pending reboot, disk space, accounts, prerequisites, cluster), Detail.txt/MSI forensics, ConfigurationFile.ini review (service accounts, IFI, TempDB, security surface, directories) |
| [skills/ssrstracelog-review/SKILL.md](skills/ssrstracelog-review/SKILL.md) | SQL Server Reporting Services trace log analysis: `ssrstracelog-review`. 24 checks (G1–G24) — trace configuration drift, report server database connectivity, memory thresholds and AppDomain recycling, report processing/data retrieval/rendering performance, subscription delivery failures, scale-out and encryption key health |
| [skills/sqlmigration-review/SKILL.md](skills/sqlmigration-review/SKILL.md) | SQL Server migration compatibility audit: `sqlmigration-review`. 15 checks (Y1–Y15) — edition-gated features, version/compatibility-level ceiling, collation, discontinued features, In-Memory OLTP, Azure SQL platform limits, backup/log-chain readiness, AG seeding edition limits, source lifecycle urgency. Dispatcher with own checks — routes to `sqlmigration-security-review`, `sqlmigration-objects-review`, and overlapping specialised skills |
| [skills/sqlmigration-security-review/SKILL.md](skills/sqlmigration-security-review/SKILL.md) | SQL Server migration security-object portability audit: `sqlmigration-security-review`. 15 checks (J1–J15) — login portability (orphaned users, SID mismatch, login type, password policy), permission fidelity (server/DB role membership, GRANT/DENY, ownership chains), credentials/proxies, certificates/keys, CMS registrations |
| [skills/sqlmigration-objects-review/SKILL.md](skills/sqlmigration-objects-review/SKILL.md) | SQL Server migration operational-object portability audit: `sqlmigration-objects-review`. 16 checks (M1–M16) — SQL Agent jobs/operators/alerts/proxies, linked servers, Database Mail, backup devices, custom error messages, server triggers, XE sessions, endpoints |

### Human Reference (references/check-explanations.md — not loaded at runtime by default)

| File | Purpose |
|------|---------|
| [skills/mssql-performance-review/references/check-explanations.md](skills/mssql-performance-review/references/check-explanations.md) | Methodology reference for the orchestrator: dispatch heuristics, symptom-to-probe-sequence map, hypothesis classes, recommendation conflict catalogue, and rationale for the standard analysis order |
| [skills/tsql-review/references/check-explanations.md](skills/tsql-review/references/check-explanations.md) | Plain-English explanation of all 85 T-checks with SQL examples, fix recipes, and Quick Reference table |
| [skills/sqlstats-review/references/check-explanations.md](skills/sqlstats-review/references/check-explanations.md) | Plain-English explanation of all 27 I/W checks with IO output examples and fix recipes |
| [skills/sqltrace-review/references/check-explanations.md](skills/sqltrace-review/references/check-explanations.md) | Plain-English explanation of all 27 X-checks with trace output examples, capture how-tos, and quick reference |
| [skills/sqlwait-review/references/check-explanations.md](skills/sqlwait-review/references/check-explanations.md) | Plain-English explanation of all 44 V-checks with wait type descriptions, capture queries, and category quick reference |
| [skills/sqlplan-review/references/check-explanations.md](skills/sqlplan-review/references/check-explanations.md) | Plain-English explanation of all 111 S/N checks with XML examples and fix recipes |
| [skills/sqlplan-compare/references/check-explanations.md](skills/sqlplan-compare/references/check-explanations.md) | C1–C20 regression checks explained — what each change means and why it causes a slowdown |
| [skills/sqlindex-advisor/references/check-explanations.md](skills/sqlindex-advisor/references/check-explanations.md) | Plain-English explanation of all 13 D-checks (D1–D13) with XML examples, fix recipes, filtered index and hash match guidance, and Quick Reference table |
| [skills/sqldeadlock-review/references/check-explanations.md](skills/sqldeadlock-review/references/check-explanations.md) | P1–P17 deadlock patterns, lock concepts, how to capture XML |
| [skills/sqlblocking-review/references/check-explanations.md](skills/sqlblocking-review/references/check-explanations.md) | Plain-English explanation of all 55 BL-checks with DMV output examples, the head-blocker state table, background on lock modes and `wait_resource` formats, fix recipes, and Quick Reference table |
| [skills/sqlplan-batch/references/check-explanations.md](skills/sqlplan-batch/references/check-explanations.md) | How to read each dashboard section, prioritisation guide, next-step workflow |
| [skills/sqlquerystore-review/references/check-explanations.md](skills/sqlquerystore-review/references/check-explanations.md) | Plain-English explanation of all 32 Q-checks with Query Store DMV examples and fix recipes |
| [skills/sqlprocstats-review/references/check-explanations.md](skills/sqlprocstats-review/references/check-explanations.md) | Plain-English explanation of all 25 R-checks with collection table examples and fix recipes |
| [skills/sqlclusterlog-review/references/check-explanations.md](skills/sqlclusterlog-review/references/check-explanations.md) | Plain-English explanation of all 30 L-checks with CLUSTER.LOG examples, fix recipes, and Quick Reference table |
| [skills/sqlhadr-review/references/check-explanations.md](skills/sqlhadr-review/references/check-explanations.md) | Plain-English explanation of all 27 H-checks with DMV examples, fix recipes, and Quick Reference table |
| [skills/sqlag-review/references/check-explanations.md](skills/sqlag-review/references/check-explanations.md) | Plain-English explanation of all 38 F-checks with catalog view examples, T-SQL fix recipes, and Quick Reference table |
| [skills/sqlerrorlog-review/references/check-explanations.md](skills/sqlerrorlog-review/references/check-explanations.md) | Plain-English explanation of all 34 E-checks with ERRORLOG examples, fix recipes, and Quick Reference table |
| [skills/sqlspn-review/references/check-explanations.md](skills/sqlspn-review/references/check-explanations.md) | Plain-English explanation of all 54 K-checks with setspn/AD attribute examples, delegation model tables, Kerberos encryption type bitmask reference, and Quick Reference table |
| [skills/sqlmemory-review/references/check-explanations.md](skills/sqlmemory-review/references/check-explanations.md) | Plain-English explanation of all 20 O-checks with DMV examples, fix recipes, and Quick Reference table |
| [skills/sqldiskio-review/references/check-explanations.md](skills/sqldiskio-review/references/check-explanations.md) | Plain-English explanation of all 15 Z-checks with sys.dm_io_virtual_file_stats examples, fix recipes, and Quick Reference table |
| [skills/sqlperfmon-review/references/check-explanations.md](skills/sqlperfmon-review/references/check-explanations.md) | Plain-English explanation of all 15 PM-checks with counter paths, worked per-core arithmetic, fix recipes, and a "How to Read a Counter" primer covering the four facts that most often cause a Perfmon capture to be misread |
| [skills/sqlworkload-compare/references/check-explanations.md](skills/sqlworkload-compare/references/check-explanations.md) | Plain-English explanation of all 14 WC-checks with side-by-side example output, the four rules for comparing two captures honestly, worked added-time and per-second-per-CPU arithmetic, and Quick Reference table |
| [skills/sqlencryption-review/references/check-explanations.md](skills/sqlencryption-review/references/check-explanations.md) | Plain-English explanation of all 112 A-checks with DMV examples, T-SQL fix code, and Quick Reference table covering all 20 categories |
| [skills/sqlblocking-review/references/community-practices.md](skills/sqlblocking-review/references/community-practices.md) | Community practice digest: the four-question method, a tooling map (sp_WhoIsActive, First Responder Kit, sp_HumanEvents, Query Store, index operational stats, Ola Hallengren's WAIT_AT_LOW_PRIORITY parameters), the practitioner rules each check encodes, where popular advice needs qualifying, and the source list |
| [skills/sqlwait-review/references/community-practices.md](skills/sqlwait-review/references/community-practices.md) | Community practice digest for wait statistics: the Waits and Queues method (Paul Randal / SQLskills, Brent Ozar First Responder Kit), a tooling map (two-snapshot delta, SQLskills wait library, sp_BlitzFirst, sp_Blitz poison waits), the practitioner rules the V-checks encode, where popular advice needs qualifying, and the source list |
| [skills/sqlencryption-review/references/concepts.md](skills/sqlencryption-review/references/concepts.md) | Background concepts (19 topics): symmetric vs asymmetric encryption, public/private keys, SQL Server algorithm reference, key hierarchy, TLS deep dive, FIPS 140-2, PCI-DSS, HIPAA, GDPR, SOX, FedRAMP, ISO 27001, TDE performance, DR with encryption, AE performance, SQL Ledger, DMK password auto-open (sp_control_dbmasterkey_password), passphrase-based encryption (PBKDF1 vs PBKDF2), DDM vs encryption |
| [skills/sqlencryption-review/references/howto-tde-setup.md](skills/sqlencryption-review/references/howto-tde-setup.md) | Step-by-step TDE deployment guide: cert creation, DEK, enabling encryption, monitoring scan, cert backup, restore procedure |
| [skills/sqlencryption-review/references/howto-always-encrypted.md](skills/sqlencryption-review/references/howto-always-encrypted.md) | Step-by-step Always Encrypted setup: CMK (AKV/Windows), CEK, column encryption, app changes, enclave config, CLE migration |
| [skills/sqlencryption-review/references/howto-tls-config.md](skills/sqlencryption-review/references/howto-tls-config.md) | Step-by-step TLS 1.2/1.3 config: certificate request from CA, binding, ForceEncryption, cipher suite ordering, verification |
| [skills/sqlencryption-review/references/howto-key-rotation.md](skills/sqlencryption-review/references/howto-key-rotation.md) | Step-by-step key rotation: TDE cert, backup cert, SB/AG endpoint certs, CLE symmetric keys, AE CMK/CEK, DMK/SMK regeneration |
| [skills/sqlencryption-review/references/howto-crypto-shredding.md](skills/sqlencryption-review/references/howto-crypto-shredding.md) | Step-by-step cryptographic erasure for GDPR right-to-erasure via per-customer encryption keys |
| [skills/sqlencryption-review/references/howto-disaster-recovery.md](skills/sqlencryption-review/references/howto-disaster-recovery.md) | Step-by-step DR for encrypted databases: TDE restore, encrypted backup restore, cross-version, AG failover, CMK migration, SMK regeneration |
| [skills/sqlencryption-review/references/howto-dmk-password-management.md](skills/sqlencryption-review/references/howto-dmk-password-management.md) | Step-by-step sp_control_dbmasterkey_password guide: SSISDB setup, cross-server restore, AG replica registration, SMK restore invalidation, sys.master_key_passwords internals |
| [skills/sqlencryption-review/references/howto-dynamic-data-masking.md](skills/sqlencryption-review/references/howto-dynamic-data-masking.md) | DDM decision guide: masking vs encryption, UNMASK permission management, DDM interaction with AE/CLE, DDM + RLS patterns |
| [skills/sqlencryption-review/references/howto-agent-jobs.md](skills/sqlencryption-review/references/howto-agent-jobs.md) | Secure SQL Agent job patterns: certificate-based key opens, proxy credentials, TRY/CATCH cleanup, alerts for encryption errors, job step audit queries |
| [skills/sqlencryption-review/references/error-reference.md](skills/sqlencryption-review/references/error-reference.md) | Common encryption errors reference: Msg 33111, 33104, 15581, 33081, 15318, self-signed cert, audit failures, EKM errors, enclave attestation, TLS handshake errors |
| [skills/sqldbconfig-review/references/check-explanations.md](skills/sqldbconfig-review/references/check-explanations.md) | Plain-English explanation of all 38 B-checks with T-SQL examples, fix recipes, and Quick Reference table |
| [skills/sqlbootstraplog-review/references/check-explanations.md](skills/sqlbootstraplog-review/references/check-explanations.md) | Plain-English explanation of all 25 U-checks with Summary.txt/Detail.txt/MSI log excerpts, fix recipes, and Quick Reference table |
| [skills/ssrstracelog-review/references/check-explanations.md](skills/ssrstracelog-review/references/check-explanations.md) | Plain-English explanation of all 24 G-checks with trace log/config excerpts, fix recipes, and Quick Reference table |
| [skills/sqlmigration-review/references/check-explanations.md](skills/sqlmigration-review/references/check-explanations.md) | Plain-English explanation of all 15 Y-checks with T-SQL examples, fix recipes, and Quick Reference table |
| [skills/sqlmigration-security-review/references/check-explanations.md](skills/sqlmigration-security-review/references/check-explanations.md) | Plain-English explanation of all 15 J-checks with T-SQL examples, fix recipes, and Quick Reference table |
| [skills/sqlmigration-objects-review/references/check-explanations.md](skills/sqlmigration-objects-review/references/check-explanations.md) | Plain-English explanation of all 16 M-checks with T-SQL examples, fix recipes, and Quick Reference table |

### Root Documentation

| File | Purpose |
|------|---------|
| [README.md](README.md) | User-facing guide: triggers, input formats, output samples for all 29 skills |
| [PERFORMANCE_TUNING_GUIDE.md](PERFORMANCE_TUNING_GUIDE.md) | Decision guide: which skill to use for which scenario, symptom-based routing, artifact capture how-tos, 950-check ID reference |
| [skills/VERSION_COMPATIBILITY.md](skills/VERSION_COMPATIBILITY.md) | SQL Server version compatibility matrix — which of the 950 checks apply to SQL 2008 R2 through SQL 2022 and Azure SQL; skill-level support matrix; cumulative active check counts per version |
| [.claude/docs/architectural_patterns.md](.claude/docs/architectural_patterns.md) | Cross-cutting conventions: check ID namespacing, input polymorphism, output format, companion pipeline, dollar-sign avoidance |
| [.claude-plugin/marketplace.json](.claude-plugin/marketplace.json) | Claude Code plugin marketplace manifest — registers this repo as a marketplace with one plugin entry pointing to `./` |
| [.claude-plugin/plugin.json](.claude-plugin/plugin.json) | Plugin manifest — declares `"skills": "./skills"` so all 29 SKILL.md files are discovered by the plugin system |
| [mcp-server/src/index.ts](mcp-server/src/index.ts) | MCP server entry point — CORS preflight, `GET /health`, error handling, then Cloudflare Workers fetch handler using `WebStandardStreamableHTTPServerTransport` (stateless, one server per request) |
| [mcp-server/src/skill-loader.ts](mcp-server/src/skill-loader.ts) | `SkillMeta` interface — no fs access; all skill data pre-bundled into `skills-data.ts` at deploy time |
| [mcp-server/src/skills-data.ts](mcp-server/src/skills-data.ts) | Generated file — run `npm run bundle` to regenerate from `skills/*/SKILL.md`. Do not edit manually |
| [mcp-server/scripts/bundle-skills.ts](mcp-server/scripts/bundle-skills.ts) | Build-time codegen: reads all SKILL.md files + PERFORMANCE_TUNING_GUIDE.md → writes `skills-data.ts` |
| [mcp-server/wrangler.toml](mcp-server/wrangler.toml) | Cloudflare Workers config — worker name `mssql-mcp`, live at `https://mssql-mcp.tsx113.workers.dev` |
| [mcp-server/src/tools.ts](mcp-server/src/tools.ts) | MCP tools: `list_skills`, `get_skill`, `get_reference`, `route_artifact` (28 artifact types including `mixed` → orchestrator; the schema enum is derived from the routing map), plus one per-skill tool that accepts `input` |
| [mcp-server/src/resources.ts](mcp-server/src/resources.ts) | MCP resources: `mssql://skills`, `mssql://skills/{name}` (×29), per-skill `/references` index and reference files, `mssql://guide`, `mssql://version-compat` |
| [mcp-server/src/prompts.ts](mcp-server/src/prompts.ts) | MCP prompts: one per skill, accepts `{ input }` and returns analysis prompt via shared `buildAnalysisPrompt` |
| [mcp-server/src/prompt-builder.ts](mcp-server/src/prompt-builder.ts) | Shared `buildAnalysisPrompt(skillName, skillContent, input)` helper used by both `tools.ts` and `prompts.ts` |
| [.github/workflows/deploy-mcp.yml](.github/workflows/deploy-mcp.yml) | GitHub Actions CD — auto-deploys to Cloudflare Workers on push when `mcp-server/`, `skills/`, or `PERFORMANCE_TUNING_GUIDE.md` changes |
| [.mcp.json](.mcp.json) | Project-scoped MCP server config — registers the `microsoft-learn` HTTP server (`https://learn.microsoft.com/api/mcp`) so every contributor gets Microsoft Learn validation tools without per-machine setup; see [.claude/docs/ms-learn-validation.md](.claude/docs/ms-learn-validation.md) |

### Examples

| Path | What it demonstrates |
|------|---------------------|
| [skills/sqlplan-review/examples/horrible.sqlplan](skills/sqlplan-review/examples/horrible.sqlplan) | Reference bad plan: parameter sniffing, spills, implicit conversion, key lookups |
| [skills/sqlplan-review/examples/horrible-analysis.md](skills/sqlplan-review/examples/horrible-analysis.md) | Reference output of `/sqlplan-review` on the above plan |
| [skills/tsql-review/examples/](skills/tsql-review/examples/) | `slow_proc.sql` with 12 anti-patterns + expected analysis |
| [skills/sqlstats-review/examples/](skills/sqlstats-review/examples/) | SSMS STATISTICS IO/TIME output + expected analysis |
| [skills/sqlplan-compare/examples/](skills/sqlplan-compare/examples/) | Baseline + regression `.sqlplan` pair + diff analysis |
| [skills/sqldeadlock-review/examples/](skills/sqldeadlock-review/examples/) | P1 lock-order deadlock XML + analysis |
| [skills/sqlblocking-review/examples/](skills/sqlblocking-review/examples/) | Two-capture blocking chain: orphaned transaction head blocker, 3-level chain, object-level X lock, 4,863 range locks from HOLDLOCK, trace flag 1211 enabled + analysis |
| [skills/sqltrace-review/examples/](skills/sqltrace-review/examples/) | `fn_trace_gettable` output with N+1, sniffing, spills + analysis |
| [skills/sqlwait-review/examples/](skills/sqlwait-review/examples/) | `sys.dm_os_wait_stats` output with I/O, lock, memory, CXPACKET + analysis |
| [skills/sqlindex-advisor/examples/](skills/sqlindex-advisor/examples/) | Index advisor output for `horrible.sqlplan` |
| [skills/sqlplan-batch/examples/](skills/sqlplan-batch/examples/) | Aggregate dashboard for a 3-plan batch |
| [skills/sqlquerystore-review/examples/](skills/sqlquerystore-review/examples/) | Query Store DMV output with plan instability, forced plan failure, N+1 + analysis |
| [skills/sqlprocstats-review/examples/](skills/sqlprocstats-review/examples/) | Q1 report output with CPU hotspot, parameter sniffing, N+1 caller, blocking signal + analysis |
| [skills/sqlclusterlog-review/examples/](skills/sqlclusterlog-review/examples/) | CLUSTER.LOG with lease timeout, heartbeat loss, AG offline transition, VerboseLogging=0 + analysis |
| [skills/sqlhadr-review/examples/](skills/sqlhadr-review/examples/) | 3-replica AG with disconnected secondary, 620 MB redo queue, secondary lag 85 sec + analysis |
| [skills/sqlag-review/examples/](skills/sqlag-review/examples/) | 3-replica AG catalog output: failure_condition_level=1, SIMPLE recovery database, SUPPORTED endpoint encryption, session_timeout=10 on WAN replica, backup priority ties, no read-only routing URL + analysis |
| [skills/sqlerrorlog-review/examples/](skills/sqlerrorlog-review/examples/) | ERRORLOG with I/O slow → AG lease expiry → failover sequence, login failure burst, trace flags + analysis |
| [skills/sqlspn-review/examples/](skills/sqlspn-review/examples/) | setspn + AD attribute output: duplicate SPN, unconstrained delegation, missing delegation target SPN, end-user in Protected Users + analysis |
| [skills/sqlmemory-review/examples/](skills/sqlmemory-review/examples/) | Memory clerk + PLE + grant queue output: ColumnStore pressure, single-use plan bloat, oversized grant blocking 4 sessions + analysis |
| [skills/sqldiskio-review/examples/](skills/sqldiskio-review/examples/) | sys.dm_io_virtual_file_stats + auto-growth trace: 47 ms data reads, 31 ms log writes, 3 auto-grow events on same volume + analysis |
| [skills/sqlencryption-review/examples/](skills/sqlencryption-review/examples/) | Multi-database encryption audit DMV output: TDE off on HRPayroll/ArchiveDB, expired TDE cert, RC4/3DES CLE keys, unencrypted backups, plaintext remote sessions, no SMK/DMK backup, self-signed TLS + analysis |
| [skills/sqlperfmon-review/examples/](skills/sqlperfmon-review/examples/) | `relog` CSV from a 16-core host: SQL Server at 90.5% of the machine, kernel time climbing to 39% of total CPU, one data volume at 52 ms hidden behind a passing `_Total`, compiles at 35% of batches, plan cache growth confined to `SQL Plans`, client cancellations starting, and a 135-second collector gap over the peak + analysis |
| [skills/sqlworkload-compare/examples/](skills/sqlworkload-compare/examples/) | Query Store two-window capture (Monday vs Tuesday, SalesDB): compatibility level 140 → 160, CU12 → CU14 and cost threshold 50 → 5 between the windows; reads-, CPU- and wait-driven regressions, a new reconciliation job, a moved export, a doubled lookup rate, and lock waits up 5.1× per second per CPU + analysis |
| [skills/sqldbconfig-review/examples/](skills/sqldbconfig-review/examples/) | sp_configure + sys.databases + sys.master_files + VLF count output: MAXDOP=0 on 4-NUMA, Max Server Memory unset, auto-shrink on SalesDB, ReportDB at compat 100 with auto-close + percent growth + analysis |
| [skills/ssrstracelog-review/examples/](skills/ssrstracelog-review/examples/) | SSRS trace config + LogFiles listing + Event Log + ExecutionLog3 output: report server database connectivity outage, verbose trace switch, processing timeout, file share subscription impersonation failure + analysis |

### Scripts

| File | Purpose |
|------|---------|
| [skills/sqlencryption-review/scripts/capture-all-encryption.ps1](skills/sqlencryption-review/scripts/capture-all-encryption.ps1) | PowerShell: captures all encryption DMVs to timestamped output files |
| [skills/sqlencryption-review/scripts/test-tls.ps1](skills/sqlencryption-review/scripts/test-tls.ps1) | PowerShell: verifies TLS configuration via SChannel registry + connection test |
| [skills/sqlencryption-review/scripts/README.md](skills/sqlencryption-review/scripts/README.md) | Script usage guide with prerequisites and examples |
| [skills/sqlbootstraplog-review/scripts/check-pending-reboot.ps1](skills/sqlbootstraplog-review/scripts/check-pending-reboot.ps1) | PowerShell: detects pending-reboot conditions (CBS, Windows Update, PendingFileRenameOperations, pending rename, SCCM) that fail SQL Server Setup's "Restart computer" rule — companion to `sqlbootstraplog-review` U7 |
| [skills/sqlbootstraplog-review/scripts/README.md](skills/sqlbootstraplog-review/scripts/README.md) | Script usage guide: signals checked, parameters, exit codes, automation pattern |
| [skills/ssrstracelog-review/scripts/collect-ssrs-diagnostics.ps1](skills/ssrstracelog-review/scripts/collect-ssrs-diagnostics.ps1) | PowerShell: collects RStrace config, Service config, trace log rollover counts, recent ERROR/Exception lines, and Application Event Log entries for the Report Server Windows Service |
| [skills/ssrstracelog-review/scripts/README.md](skills/ssrstracelog-review/scripts/README.md) | Script usage guide: sections collected, parameters, prerequisites, ExecutionLog3 query handoff |
| [skills/sqlperfmon-review/scripts/capture-perfmon.ps1](skills/sqlperfmon-review/scripts/capture-perfmon.ps1) | PowerShell: two modes — `Collect` creates a `logman` counter log, runs it, stops and deletes the collector, and converts the `.blg` to CSV with `relog`; `Convert` turns an existing `.blg` (PSSDIAG, SQL LogScout, or a colleague's capture) into CSV with no elevation and nothing created on the server. Uses the `LogicalDisk(*)` and `Plan Cache(*)` wildcard instances deliberately, because PM8 and PM11 cannot be assessed from `_Total` |
| [skills/sqlperfmon-review/scripts/README.md](skills/sqlperfmon-review/scripts/README.md) | Script usage guide: modes, parameters, the counter set and why two instances are wildcards, what the script cannot capture (`cpu_count`, physical RAM), and the `sys.dm_os_performance_counters` fallback with its limits |
| [skills/sqlworkload-compare/scripts/capture-workload-windows.sql](skills/sqlworkload-compare/scripts/capture-workload-windows.sql) | T-SQL: read-only two-window Query Store comparison by `query_hash` (full outer match, execution-weighted averages, per-minute rates, aborted counts), plus configuration and wait-statistics snapshots for WC11–WC14 |
| [skills/sqlworkload-compare/scripts/README.md](skills/sqlworkload-compare/scripts/README.md) | Script usage guide: the three parts and the checks each feeds, window alignment to the Query Store interval, what the script cannot reconstruct, and required permissions |
| [skills/sqlblocking-review/scripts/capture-blocking.sql](skills/sqlblocking-review/scripts/capture-blocking.sql) | T-SQL: read-only blocking capture — blocking chain with head blocker and statement text, open transactions with age, waiting tasks joined to their locks, lock footprint per session, and blocking observability/concurrency configuration |
| [skills/sqlag-review/scripts/capture-ag-config.sql](skills/sqlag-review/scripts/capture-ag-config.sql) | T-SQL: collects sys.availability_groups, sys.availability_replicas, sys.availability_group_listeners, sys.availability_group_listener_ip_addresses, sys.database_mirroring_endpoints, AG database recovery models, certificates, and XE sessions in one batch |
| [skills/sqlplan-review/scripts/extract_plan.py](skills/sqlplan-review/scripts/extract_plan.py) | Python (stdlib only): flattens a `.sqlplan` into the digest `sqlplan-review` analyses — handles UTF-16 and mis-declared encodings, computes self time (row vs batch mode, per-thread, coordinator excluded, pass-through operators looked through), normalises cardinality per execution. Invoked at Step 0 of the skill; `--node N` for one operator, `--sql` for untruncated statement text |
| [skills/sqlplan-review/scripts/Extract-SqlPlan.ps1](skills/sqlplan-review/scripts/Extract-SqlPlan.ps1) | PowerShell 5.1+/7+ (no modules): port of `extract_plan.py` with byte-identical output, for hosts without Python. `-Sql` is a switch plus `-Statement <id>`, since a `[string]` parameter cannot be passed bare |
| [skills/mssql-performance-review/scripts/redact_artifact.py](skills/mssql-performance-review/scripts/redact_artifact.py) | Python (stdlib only): replaces host names, logins, IP addresses, SIDs, GUIDs, email addresses and Luhn-valid card numbers in any artifact with consistent tokens (`HOST_01`, `USER_03`) via an optional local `--map` file, so cross-artifact correlations survive redaction. Keeps built-in principals, Perfmon counter paths, the showplan namespace and query text. `--self-test` runs the built-in fixtures |
| [skills/mssql-performance-review/scripts/README.md](skills/mssql-performance-review/scripts/README.md) | Redaction script usage: options, categories replaced and kept, why consistent tokens, limits |
| [skills/sqlplan-review/scripts/README.md](skills/sqlplan-review/scripts/README.md) | Script usage guide: extractor invocation for both runtimes, why a tool rather than reading the XML, digest-section-to-check mapping, hardening notes, and the three capture scripts |

### Lint tooling (dev-only)

| File | Purpose |
|------|---------|
| [scripts/lint-scripts.sh](scripts/lint-scripts.sh) | Single entry point: runs the three gates below over git-tracked and untracked-unignored files. `--strict` makes a missing linter a FAIL (CI); without it a missing linter is a WARN and is skipped |
| [scripts/lint-tsql.ps1](scripts/lint-tsql.ps1) | T-SQL syntax gate: parses every `.sql` file and every ` ```sql ` block in every `.md` file with ScriptDom (`TSql170Parser`). Fetches the pinned ScriptDom package from nuget.org once, verifies its SHA-256 before loading, caches it in `.lint-cache/`. Honours `<!-- lint-tsql: skip <reason> -->` markers and `-Baseline` |
| [scripts/lint-tsql-baseline.tsv](scripts/lint-tsql-baseline.tsv) | Known-broken T-SQL blocks in docs, keyed by content hash — real or suspected recipe defects awaiting a validated fix. Shrink-only: a fixed, moved, or deleted block's entry fails the gate as stale |
| [scripts/lint-powershell.ps1](scripts/lint-powershell.ps1) | PowerShell gate: PSScriptAnalyzer with the repo settings, every severity including ParseError |
| [PSScriptAnalyzerSettings.psd1](PSScriptAnalyzerSettings.psd1) | PSScriptAnalyzer rule exclusions, each with its reason |
| [ruff.toml](ruff.toml) | ruff rules for every `.py` in the repo — no file list, so new scripts are covered automatically. `target-version = "py38"` enforces the repo-wide Python 3.8 floor: newer syntax is a hard error |
| [.github/workflows/lint.yml](.github/workflows/lint.yml) | CI: installs pinned ruff and PSScriptAnalyzer, runs `lint-scripts.sh --strict` on every PR |

### Project subagents

| File | Purpose |
|------|---------|
| [.claude/agents/mslearn-validator.md](.claude/agents/mslearn-validator.md) | Read-only subagent: validates DMV columns, enum values, T-SQL syntax, cmdlets, registry names and version gates in changed files (or a named skill, or the current diff) against Microsoft Learn MCP. Returns the audit-prompt issue table with Learn URLs, unlabelled undocumented claims, candidate `CLAIM_SENTINELS` entries, and a verified-claims list for the PR record |
| [.claude/agents/check-author.md](.claude/agents/check-author.md) | Adds, edits or retires a check in an existing skill: three-part SKILL.md entry, five-part explanation, Learn lookups for its own claims, every count and range touch point, then `verify-docs.sh` and `lint-scripts.sh` until clean |
| [.claude/agents/skill-scaffolder.md](.claude/agents/skill-scaffolder.md) | Creates a new skill end to end: unused two-letter prefix, the §1.5 layout, untrusted-artifact block, registration in the `P2S` map, CLAUDE.md, README, guide, version matrix, orchestrator and `ARTIFACT_SKILL_MAP`, then all gates and the MCP tests |
| [.claude/agents/tsql-lint-triager.md](.claude/agents/tsql-lint-triager.md) | Triages `lint-tsql.ps1` failures as fragment, parser gap (confirmed on Learn), removed syntax or real defect; fixes defects, adds reasoned skip markers, removes stale baseline lines, never adds new ones |
| [.claude/agents/citation-auditor.md](.claude/agents/citation-auditor.md) | Read-only: finds uncredited checks, thresholds, formulas, quotes and adapted work, verifies cited sources say what the repo claims, and flags credits replaced by neutral wording |
| [.claude/agents/consistency-reviewer.md](.claude/agents/consistency-reviewer.md) | Read-only: semantic drift `verify-docs.sh` cannot see — conflicting thresholds across skills, references to retired checks, one-way companion links, routing tables that disagree, stale sample IDs and uncovered counts |
| [.claude/agents/example-builder.md](.claude/agents/example-builder.md) | Writes or refreshes `examples/` input and `-analysis.md`: fictional but realistic data planned around a Critical/Warning/Info spread plus near misses, analysis in the exact Output Format, every quoted number recomputed |
| [.claude/agents/mcp-sync.md](.claude/agents/mcp-sync.md) | Regenerates `skills-data.ts`, checks `ARTIFACT_SKILL_MAP`, resources and prompts cover every skill and reference file, fixes stale counts under `mcp-server/`, runs typecheck and vitest. Never deploys |

## Installing Skills

**Option 1: Plugin Marketplace (recommended)**
```bash
/plugin marketplace add vanterx/mssql-performance-skills
/plugin install mssql-performance-skills@mssql-performance-skills
```

**Option 2: `npx` one-liner** — requires [Node.js](https://nodejs.org) (>= 18)
```bash
npx skills add vanterx/mssql-performance-skills          # user scope
npx skills add vanterx/mssql-performance-skills -g       # global
```

**Option 3: Manual fallback:**
```bash
cp -r skills/* ~/.claude/skills/          # global (all 29 skills)
cp -r skills/* .claude/skills/            # project-scoped
```

## Adding a New Check to an Existing Skill

These steps apply to any skill. Replace `<skill>` with the skill directory name and `<PREFIX>` with its check letter(s). Delegate the whole procedure to the `check-author` subagent — see *Delegating to project subagents* below.

1. Add the check to `skills/<skill>/SKILL.md` under the correct section, following the **Trigger → Severity → Fix** three-part structure
2. Add a full explanation entry to `skills/<skill>/references/check-explanations.md`, following the **five-part structure** (What it means / How to spot it / Example / Fix options / Related checks)
3. Update the check count in the skill's frontmatter `description` field and in its `## Purpose` section
4. Update the section header range (e.g., `T1–T50` → `T1–T51`) in both files
5. Update the Quick Reference table at the bottom of `references/check-explanations.md` if the skill has one
6. Update the check count in [PERFORMANCE_TUNING_GUIDE.md](PERFORMANCE_TUNING_GUIDE.md) (Check ID Reference table)

## Adding a New Skill

Delegate the whole procedure to the `skill-scaffolder` subagent — see *Delegating to project subagents* below.

1. Create `skills/<skill-name>/SKILL.md` and `skills/<skill-name>/references/check-explanations.md` following the patterns in [.claude/docs/architectural_patterns.md](.claude/docs/architectural_patterns.md)
2. Choose an unused check prefix. All 26 single letters are taken (S, N, C, D, P, T, I, W, X, V, Q, R, H, L, E, K, O, Z, A, B, U, G, F, Y, J, M), so new skills use a two-letter prefix — `BL` (`sqlblocking-review`) is the first. `scripts/verify-docs.sh` matches `^### [A-Z]{1,2}[0-9]`, so pick a pair that no existing prefix collides with and add it to the `P2S` map in Check 34
3. Add the skill to the Key Files tables above
4. Add install line to [README.md](README.md) Installation section and `## Skills` table
5. Add a full `## <skill-name>` section to [README.md](README.md) with triggers, usage, and output sample
6. Add the skill to [PERFORMANCE_TUNING_GUIDE.md](PERFORMANCE_TUNING_GUIDE.md) Skills at a Glance, Skill Scope Comparison, and relevant scenario sections
7. Add example input + analysis files to `skills/<skill-name>/examples/`
8. Add `tsql-review` as companion in `sqlplan-review/SKILL.md` (or the relevant existing companion)

## Git Hooks

Run once after cloning to install the pre-commit hook:

```bash
bash scripts/install-hooks.sh
```

The hook watches for staged `skills/*/SKILL.md` changes and automatically runs `npm run bundle` + re-stages `mcp-server/src/skills-data.ts` before the commit lands. Without it, you must run `cd mcp-server && npm run bundle` manually before every commit that touches a skill.

## Development Scratch Log

`changes.log` is a local, uncommitted development scratch log. It is `.gitignore`d and tracks work-in-progress notes during active development sessions. It is not part of the canonical project history — use `git log` for that.

## Development Constraints

Rules discovered during development that must be respected in every session.

### Skill authoring standard
All new and modified skills must conform to the Anthropic skill-creator best practices. Reference: [`.claude/docs/skill-creator-best-practices.md`](.claude/docs/skill-creator-best-practices.md). Automated checks run in `scripts/verify-docs.sh` (Checks 22–25): description ≥ 30 words with trigger phrases, `triggers:` field present, no bare ALWAYS/NEVER/MUST in body.

### Branch policy
Every change must be made on a new branch — never commit directly to `main`. Before starting work, create a branch (e.g. `git checkout -b <type>/<short-description>`), commit there, and open a PR to merge into `main`. This keeps `main` clean and ensures all changes go through review. Branch names follow the commit `<type>` vocabulary (`feat`, `fix`, `refactor`, `docs`, `test`, `chore`, `perf`, `ci`).

### Before committing
Always run `bash scripts/verify-docs.sh` — it checks documentation invariants and exits non-zero on any failure. The PostToolUse hook in `.claude/settings.json` runs it automatically after Write/Edit, but run it manually before `git commit` too. If the change touches code or T-SQL, also run `bash scripts/lint-scripts.sh` — see *Linting* below.

Checks 1–49 verify **structure** (counts, ranges, file presence, table rows). Checks 50–56 verify **claims, cross-skill content, shipped scripts and links** and are delegated to [scripts/verify-claims.py](scripts/verify-claims.py), which `verify-docs.sh` invokes once so all seven run against content read in a single pass:

| Check | Guards |
|-------|--------|
| 50 | Duplicated literal lists are byte-identical across every copy, and documented deliberate keeps are never excluded |
| 51 | Any claim Microsoft Learn does not document carries its `Unverified against Microsoft Learn` label |
| 52 | Fixed-width example tables keep their column count, so a long value cannot eat its separator |
| 53 | Specific claims that were wrong once stay fixed (a regression guard, not a correctness proof) |
| 54 | The untrusted-artifact-content rule is present, byte-identical and correctly placed in all 29 `SKILL.md` files |
| 55 | Every script under `skills/*/scripts/` has a `Requires:` permission line; capture `.sql` scripts contain no state-changing statement outside comments and strings; setup scripts (`*_create_*.sql`) say `CHANGES SERVER STATE` and carry a `Teardown` section |
| 56 | Every relative Markdown link in the repo resolves to a file that exists (URLs, anchors, code spans and the gitignored local-only folders are skipped) |

When adding content that duplicates a literal list, asserts something MS Learn does not document, or fixes a technical claim, extend the matching registry at the top of `verify-claims.py` — `DUPLICATED_LISTS`, `UNVERIFIED_CLAIMS`, or `CLAIM_SENTINELS`. If the untrusted-artifact-content rule is reworded, edit it in all 29 `SKILL.md` files in one pass — Check 54 fails on a single drifted copy; see [.claude/docs/architectural_patterns.md](.claude/docs/architectural_patterns.md) §12. Keep `CLAIM_SENTINELS` short and tie every entry to a defect that actually shipped; a broad banned-phrase linter is deliberately not implemented, because it produces false positives and pressures authors to word around the check instead of fixing the content.

These checks catch *regressions* of known-wrong claims. They cannot catch a new wrong claim, because nothing in the repo knows it is wrong yet — Microsoft Learn validation remains the actual gate, and this is a ratchet behind it.

If no working Python interpreter is present, checks 50–56 are skipped with a warning and the structure checks still run. Detection probes each candidate rather than trusting `command -v`: on Windows, `python3` normally resolves to the Microsoft Store App Execution Alias stub, which is on `PATH`, is not Python, and exits 49 with an install prompt.

### Credit and cite sources
Always credit the person, tool or document a check, threshold, formula, quote or rule comes from, and link the source wherever possible. Do not replace a name with neutral wording such as "community best practice". #15 did that, and the credits had to be restored later (see [.claude/docs/plans/restore-removed-references.md](.claude/docs/plans/restore-removed-references.md)).
- **Where credits go:** a short "Based on …" line in the `SKILL.md` Purpose section. Add inline credit where a direct quote or a named formula is used, because there the credit is part of the evidence. Put the full source list with links in `references/community-practices.md`, following the [sqlblocking-review](skills/sqlblocking-review/references/community-practices.md) and [sqlwait-review](skills/sqlwait-review/references/community-practices.md) pattern. Trigger text stays free of names.
- **Check before crediting.** Confirm every quote, formula or parameter name against the original source before attributing it. If the source can't be reached, keep the credit but label the specific claim unverified.
- **A community credit does not replace Microsoft Learn validation.** Community sources are credited for method and emphasis. Thresholds, DMV columns, syntax and version claims still follow Microsoft Learn, and anything Learn does not document carries the `Unverified against Microsoft Learn` label (Check 51).
- **Credit work adapted from other repositories or skills** in the commit message and in the skill itself. #65 used Erik Darling's `query-plan-analysis` skill without crediting it at first.

### Linting
Also run `bash scripts/lint-scripts.sh` before committing a change to any `.py`, `.ps1`, `.sql`, or to a ` ```sql ` block in any `.md`. CI runs it with `--strict` on every PR (`.github/workflows/lint.yml`). Local prerequisites: `pip install ruff`, `Install-PSResource PSScriptAnalyzer -Scope CurrentUser`, and PowerShell 7 (`pwsh`), which also loads ScriptDom — no .NET SDK needed.

**Python scripts: standard library only, Python 3.8+.** Shipped scripts run on whatever interpreter a DBA's jump box has, with no pip step. Any new `.py` is linted automatically — there is no list to register it in — and `ruff.toml`'s `target-version = "py38"` makes 3.9+ syntax a hard error. A script that genuinely needs a newer Python opts in by name under `[per-file-target-version]` in `ruff.toml` and documents its floor in its own docstring.

**T-SQL in Markdown is linted too.** Every ` ```sql ` block must parse as a T-SQL batch. A block that is correct as documentation but not runnable gets a marker on the line directly before its fence, and the reason is mandatory — a bare `skip` fails the gate:

```markdown
<!-- lint-tsql: skip fragment: clauses or expressions shown out of context, not a full statement -->
```

Use one of three reason kinds: `fragment:` (clauses, `...` elisions, `<placeholders>`, side-by-side alternatives), `parser-gap:` (syntax Microsoft Learn documents but ScriptDom rejects — name the construct, and confirm it on MS Learn first), or `removed-syntax:` (deliberately shows syntax SQL Server no longer accepts). Never use a marker to silence a block that is actually wrong.

**Real defects go in `scripts/lint-tsql-baseline.tsv`, which only shrinks.** Fixing a block changes its hash, so it drops out of the baseline and must then parse; delete its line in the same commit, or the gate fails on a stale entry. Do not add entries to get a new failure past the gate — fix it. `lint-tsql.ps1 -EmitBaseline` prints candidate lines, but each needs a human-written note.

ScriptDom is Microsoft's T-SQL parser library, not the engine's parser: it has real grammar gaps (several `ALTER AVAILABILITY GROUP` replica and listener options, `ALTER ENDPOINT` without `ROLE`, `SET ENCRYPTION SUSPEND`), which is why `parser-gap:` exists. It checks syntax only — a wrong column name or an option that is valid syntax in the wrong statement still parses.

### Dollar signs in SKILL.md files
Never write `$` immediately followed by a digit or `[` inside SKILL.md files. The skill loader performs shell-style variable expansion on the entire file content, so `$0` expands to the input file path argument, `$1`/`$15` expand to empty strings, and `$[...]` is parsed as deprecated bash arithmetic. This bites SQL Server content in practice — named instances (`MSSQL$SQL2019`), shell snippets, and currency literals in T-SQL examples. Rephrase with a placeholder (`MSSQL$<InstanceName>`) or drop the symbol (`Amount > 1500.00`). Dollar signs followed by a letter are safe: PowerShell variables (`$true`), instance placeholders, and JSON paths (`'$.path'`) all pass. Enforced by verify-docs.sh Check 5; see [.claude/docs/architectural_patterns.md](.claude/docs/architectural_patterns.md) §10.

### Check ID prefixes — currently taken
| Prefix | Skill |
|--------|-------|
| `S`, `N` | `sqlplan-review` |
| `C` | `sqlplan-compare` |
| `D` | `sqlindex-advisor` |
| `P` | `sqldeadlock-review` |
| `BL` | `sqlblocking-review` — two-letter prefix; all 26 single letters were taken when it was added |
| `PM` | `sqlperfmon-review` — two-letter prefix |
| `WC` | `sqlworkload-compare` — two-letter prefix |
| `T` | `tsql-review` |
| `I`, `W` | `sqlstats-review` |
| `X` | `sqltrace-review` |
| `V` | `sqlwait-review` |
| `Q` | `sqlquerystore-review` |
| `R` | `sqlprocstats-review` |
| `H` | `sqlhadr-review` |
| `L` | `sqlclusterlog-review` |
| `E` | `sqlerrorlog-review` |
| `K` | `sqlspn-review` |
| `O` | `sqlmemory-review` |
| `Z` | `sqldiskio-review` |
| `A` | `sqlencryption-review` |
| `B` | `sqldbconfig-review` |
| `U` | `sqlbootstraplog-review` |
| `G` | `ssrstracelog-review` |
| `F` | `sqlag-review` |
| `Y` | `sqlmigration-review` — dispatcher with own checks; routes overlap to `sqlmigration-security-review`/`sqlmigration-objects-review` and other specialised skills |
| `J` | `sqlmigration-security-review` |
| `M` | `sqlmigration-objects-review` |
| (none) | `mssql-performance-review` — dispatcher; delegates checks to other skills, like `sqlplan-batch` |

New skills must choose an unused prefix — a single uppercase letter if one is ever freed, otherwise an unused two-letter combination (`BL` is taken) — or document why they are dispatcher-style (no prefix) like the orchestrator and `sqlplan-batch`.

### references/check-explanations.md is not loaded at runtime by default
Only `SKILL.md` is loaded automatically by the Claude Code skill loader. The `references/check-explanations.md` file is human reference and on-demand context — Claude may load it when a user asks "explain check X" or for deeper fix-option detail. Do not put trigger conditions or thresholds there that Claude needs to act on without prompting.

### Microsoft Learn validation (mandatory)
All new and modified skills, checks, scripts, and reference content must be validated against current Microsoft Learn documentation before being considered complete. Use the Microsoft Learn MCP tools (`microsoft_docs_search`, `microsoft_docs_fetch`) to verify every DMV column name, T-SQL syntax, PowerShell cmdlet, configuration setting, and version compatibility claim. If documentation cannot be found, mark the content as "Unverified" rather than assuming correctness. Full policy: [`.claude/docs/ms-learn-validation.md`](.claude/docs/ms-learn-validation.md). Delegate the validation pass to the `mslearn-validator` subagent ([.claude/agents/mslearn-validator.md](.claude/agents/mslearn-validator.md)) — pass it the changed files or "the diff" — so the lookups stay out of the main context.

### Delegating to project subagents
The eight agents in `.claude/agents/` (listed under *Project subagents* in Key Files) each own one workflow. When a task matches a row below, hand it to that agent instead of doing it in the main session, and pass it the scope: file paths, a skill name, or "the diff".

| When the task is… | Delegate to |
|-------------------|-------------|
| Adding, changing, renumbering, merging or retiring a check in an existing skill | `check-author` |
| Adding a new skill, or porting an external skill into the library | `skill-scaffolder` |
| Creating or refreshing a skill's `examples/` input and `-analysis.md`, or after checks change in a skill that has examples | `example-builder` |
| `lint-scripts.sh` or CI reports T-SQL parse failures, or the baseline has stale entries | `tsql-lint-triager` |
| Any change to a `SKILL.md`, `check-explanations.md`, capture script or T-SQL recipe, before committing | `mslearn-validator` |
| Content adapted from a person, tool or repository; a reviewer asks where a threshold came from; before a release | `citation-auditor` |
| A change spanning several skills; two skills seem to give conflicting advice; before a release | `consistency-reviewer` |
| A skill was added, renamed or edited; the MCP bundle looks stale; MCP routing or deploy fails in CI | `mcp-sync` |

Rules:
- **Subagents cannot start other subagents.** After an editing agent (`check-author`, `skill-scaffolder`, `example-builder`, `tsql-lint-triager`) finishes, the main session runs `mslearn-validator` on the diff, and `mcp-sync` if a `SKILL.md` changed. Independent read-only passes (`mslearn-validator`, `citation-auditor`, `consistency-reviewer`) can run in parallel.
- **Agents do not commit.** The main session reviews the agent's report, applies any fixes a read-only agent recommends, runs the *Before committing* gates, and commits on the feature branch.
- **A read-only agent's findings are recommendations.** Check a finding against the cited source before applying it; an agent can be wrong.
- **Small edits stay in the main session.** A one-line wording fix or a typo does not need an agent; `mslearn-validator` is still required if the edit changes a technical claim.
- **The user's instruction wins.** If the user asks for the work to be done directly, or names a different agent, do that.

### Updating check counts — all 6 touch points
When adding or removing a check from any skill, update all of:
1. Skill frontmatter `description` field (count in the one-liner)
2. Skill `## Purpose` section (count in the narrative)
3. Section header range in `SKILL.md` (e.g., `T1–T50` → `T1–T51`)
4. Section header range in `references/check-explanations.md`
5. `references/check-explanations.md` Quick Reference table (if the skill has one)
6. `PERFORMANCE_TUNING_GUIDE.md` Check ID Reference table total (`**Total: N checks**`)

Then run `bash scripts/verify-docs.sh` to confirm Check 1 passes.

---

## Additional Documentation

| Topic | File |
|-------|------|
| Architectural patterns, conventions, design decisions | [.claude/docs/architectural_patterns.md](.claude/docs/architectural_patterns.md) |
| Microsoft Learn MCP validation policy (mandatory) | [.claude/docs/ms-learn-validation.md](.claude/docs/ms-learn-validation.md) |
| June 2026 MS Learn audit trail (historical, with cited sources) | [.claude/docs/audits/ms-learn-validation-2026-06.md](.claude/docs/audits/ms-learn-validation-2026-06.md) |
| Scenario-based skill selection, symptom routing | [PERFORMANCE_TUNING_GUIDE.md](PERFORMANCE_TUNING_GUIDE.md) |
| SQL Server version compatibility matrix | [skills/VERSION_COMPATIBILITY.md](skills/VERSION_COMPATIBILITY.md) |
| Skill usage, triggers, input/output examples | [README.md](README.md) |
| All check triggers, thresholds, fix logic | Each skill's `SKILL.md` — see Key Files table above |
| Plain-English check explanations with examples | Each skill's `references/check-explanations.md` — see Key Files table above |