/mssql-performance-review

name: mssql-performance-review
description: Agentic offline orchestrator for end-to-end SQL Server performance reviews. Forms hypotheses from artifacts or symptoms, dispatches the specialised review skills (tsql-review, sqlplan-review, sqlwait-review, sqlstats-review, sqltrace-review, sqlquerystore-review, sqlprocstats-review, sqldeadlock-review, sqlhadr-review, sqlag-review, sqlclusterlog-review, sqlerrorlog-review, sqlspn-review, sqlplan-compare, sqlindex-advisor, sqlplan-batch, sqlmemory-review, sqldiskio-review, sqlencryption-review, sqldbconfig-review, sqlbootstraplog-review, ssrstracelog-review), runs an adversarial check on the primary root cause, and produces a consolidated fix priority with explicit evidence chain, risk, and rollback for each recommendation. Use this skill whenever a user has mixed SQL Server artifacts (.sqlplan, .sql, statistics output, trace data, wait stats, deadlock XML, AG / cluster / ERRORLOG, setspn output, Query Store, procstats, memory clerks, file I/O stats, encryption audit, sp_configure output, setup bootstrap logs, SSRS report server trace logs, AG catalog view output) and is not sure which specialised skill to run, or when the user describes a symptom ("CPU is high", "AG failed over", "this query is slow", "SSRS reports timing out", "AG backup failing on secondary") and needs the analysis routed for them. Trigger on /mssql-performance-review, /mssql-perf-review, /mssql-full-review, /sql-triage, full SQL Server performance review, end-to-end SQL Server review, root cause analysis with mixed artifacts. Strictly offline — never opens a connection to SQL Server.
triggers:
  - /mssql-performance-review
  - /mssql-perf-review
  - /mssql-full-review
  - /sql-triage

SQL Server Performance Review Orchestrator Skill

Purpose

A dispatch skill that turns a mixed pile of SQL Server artifacts (or a symptom description) into a single, evidence-backed performance review. It does not redefine any checks — it routes work to the 18 specialised review skills, then synthesises their findings into one consolidated report.

The orchestrator is **strictly offline**: it reads files the user provides, generates capture-script bundles when artifacts are missing, and emits analysis reports. It never opens a connection to a SQL Server. All execution against the database is the user's action.

This skill applies eleven cross-cutting primitives that distinguish it from a naive dispatcher:

**Tier 1 — agentic core:**

• **Evidence chain** (E-tags) — every finding cites the source artifact, the specialised check ID, the observed value, and the threshold violated, so any recommendation is reproducible from the input set
• **Risk-aware recommendations** — every recommended fix carries action, effort, blocking window, risk class, side effects, explicit rollback, and post-deployment verification
• **Adversarial root cause check** — after the primary hypothesis is identified, a deliberate pass tries to disprove it; contradicting evidence escalates an alternative hypothesis instead of being suppressed
• **Confidence-driven early termination** — once three or more specialised skills converge on the same root cause with HIGH confidence and no active contradiction, additional probes are skipped as redundant

**Tier 2 — routing and intelligence:**

• **Multi-model routing** — each phase runs on the right model (Haiku for classification and triage, Sonnet for synthesis and deep dive, Opus for the adversarial pass). See `references/model-routing.md`.
• **Skill-graph DAG** — replaces fixed phase ordering with a dynamic dependency DAG built from artifact types and probe findings. Probes that depend on each other sequence correctly; everything else runs in parallel. See `references/skill-dag.md`.
• **Domain memory** — per-instance facts (MAXDOP, cores, AG topology, partitioning, RCSI status) loaded from a user-managed JSON file inform every recommendation: redundant recommendations rejected, environment-aware escalators applied. See `references/domain-memory.md`.
• **Follow-up Q&A** — after the report, the orchestrator stays in the session and answers questions ("why this index ordering?", "why was MAXDOP not recommended?") from the in-context evidence chain. Most follow-ups need no new dispatch. See `references/followup-qa.md`.

**Tier 3 — offline loop closure:**

• **Capture bundle generator** — when artifacts are missing, emit a self-contained bundle of read-only `.sql` scripts + README + paste-back template + manifest to `./captures/<run-id>/`. The user runs the scripts; paste-back into the template; `--resume` continues the analysis. The orchestrator never contacts SQL Server. See `references/capture-bundle-spec.md`.
• **Verification checklist** — every recommendation gets a dedicated re-capture suggestion + expected metric movement + timing rule. Promoted to a Verification — After Deploying Fixes section in the report. See `references/verification-checklist.md`.
• **Baseline-diff feedback loop** — when the user returns with `--baseline ./state/<prior>/state.json` and new artifacts, the orchestrator tags each prior recommendation as `verified-effective` / `partial` / `no-change` / `regressed-elsewhere` / `cannot-evaluate`. Tags append to `evals/feedback.jsonl` (gitignored) so future hypothesis selection improves from real-world outcomes. See `references/verification-checklist.md`.

Input

Accept any of:

• A directory path containing mixed artifacts (`.sqlplan`, `.sql`, `.txt`, `.xdl`, `.log`, `.json`)
• A list of file paths
• Inline content blocks pasted into chat (one block per artifact, with type hint)
• A natural-language symptom description ("CPU is high on prod since 09:00, no recent deploy")

The orchestrator first classifies each input, then routes per the dispatch table below. When the input is symptom-only and no artifacts are available, the orchestrator describes which captures would resolve the hypothesis and (in tier 3) generates a capture bundle the user can