# Mssql Performance Review

> 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…

- **Type:** Skill
- **Install:** `agentstack add skill-vanterx-mssql-performance-skills-mssql-performance-review`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [vanterx](https://agentstack.voostack.com/s/vanterx)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [vanterx](https://github.com/vanterx)
- **Source:** https://github.com/vanterx/mssql-performance-skills/tree/main/skills/mssql-performance-review

## Install

```sh
agentstack add skill-vanterx-mssql-performance-skills-mssql-performance-review
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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 to control token cost

**Tier 2 — cost and intelligence:**

- **Multi-model cost routing** — each phase runs on the right model (Haiku for classification and triage, Sonnet for synthesis and deep dive, Opus for the adversarial pass). Cuts cost ~40% vs all-Sonnet at no quality loss. 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 cost nothing. 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//`. 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//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 run.

## Artifact Classification

Content-based, not extension-reliant:

| Artifact signal | Routes to |
|-----------------|-----------|
| `` root + `` | sqldeadlock-review |
| `query_store_*` table refs, plan_id / runtime_stats columns | sqlquerystore-review |
| `total_worker_time`, `database_id` from `sys.dm_exec_procedure_stats` | sqlprocstats-review |
| `replica_id`, `synchronization_state` columns | sqlhadr-review |
| `sys.availability_groups` columns (`failure_condition_level`, `health_check_timeout`, `automated_backup_preference_desc`, `basic_features`, `is_contained`), `sys.availability_replicas` with `backup_priority` / `session_timeout` / `seeding_mode_desc`, `sys.database_mirroring_endpoints`, `sys.availability_group_listeners` with `is_conformant` (the per-IP `sys.availability_group_listener_ip_addresses` view carries IP/state, not `is_conformant`) | sqlag-review |
| `RES_EVENT`, `00000a1c` GUID prefixes, `Cluster.Resource` lines | sqlclusterlog-review |
| `spid` prefixes with `Logon`, `Server`, `Backup` markers | sqlerrorlog-review |
| `MSSQLSvc/`, `setspn` output, `Existing SPN found for` | sqlspn-review |
| `memory_node_id`, `pages_kb`, `type` from `sys.dm_os_memory_clerks`; `page_life_expectancy`; `sys.dm_os_sys_info` output | sqlmemory-review |
| `io_stall_read_ms`, `num_of_reads`, `size_on_disk_bytes` from `sys.dm_io_virtual_file_stats`; auto-growth events | sqldiskio-review |
| `sys.dm_database_encryption_keys`, `sys.certificates`, `sys.symmetric_keys`, TLS cipher output; explicit encryption audit request | sqlencryption-review |
| `sp_configure` name/value pairs, `sys.databases` settings columns (`is_auto_shrink_on`, `compatibility_level`), `sys.configurations` output | sqldbconfig-review |
| `Final result:` / `Exit code (Decimal):` / `Detailed results:` blocks, `Setup Bootstrap` paths, `ConfigurationFile.ini` parameters, MSI `Return value 3` | sqlbootstraplog-review |
| `ReportServerService_.log` trace lines, `` / `` config sections from `ReportingServicesService.exe.config` / `RSReportServer.config`, `ExecutionLog3` columns, "Report Server Windows Service" Event Log entries | ssrstracelog-review |
| Ambiguous `.txt` | Inspect first 100 lines, pick the highest-priority match; ask the user if still ambiguous |

## Hypothesis Generation

Before dispatching skills, generate two or three ranked hypotheses from the classified inputs (and the symptom description, if any). Each hypothesis maps to a probe sequence — the subset of specialised skills that would confirm or refute it.

Example hypotheses:

| Hypothesis class | Trigger signals | Probe sequence |
|------------------|----------------|----------------|
| Parameter sniffing | Wide duration variance in stats / trace; multiple plans for same query_hash | sqlstats-review → sqlplan-review → sqlquerystore-review → sqlplan-compare |
| Missing index | Key Lookup or large scan visible in plan; high logical reads on one table | sqlplan-review → sqlindex-advisor → sqlplan-batch (if folder) |
| Server-wide I/O bottleneck | PAGEIOLATCH_SH dominant in wait stats | sqlwait-review → sqlstats-review → sqlplan-review on top reader |
| Deadlock loop | error 1205 reported, deadlock XML present | sqldeadlock-review → sqlplan-review on victim |
| AG failover root cause | ERRORLOG shows lease expiry, CLUSTER.LOG present | sqlerrorlog-review → sqlclusterlog-review → sqlhadr-review |
| AG configuration review | sys.availability_groups / sys.availability_replicas / sys.database_mirroring_endpoints output present, or user asks about AG setup, backup failures on secondary, listener routing, endpoint certificate | sqlag-review |
| Kerberos auth fail | NTLM fallback, login burst, setspn output present | sqlspn-review → sqlerrorlog-review (login burst correlation) |
| Workload regression | Two plans for same query, dates differ, durations diverge | sqlplan-compare → sqlplan-review on both |

Record hypotheses with initial confidence (HIGH / MEDIUM / LOW). Confidence updates as probes complete.

## Dispatch — DAG-Driven

Tier 1 used a fixed five-phase order. Tier 2 replaces fixed phases with a dynamic skill-graph DAG built from artifact types and probe findings. The DAG walks with maximal parallelism, follows edges that become available as findings accumulate, and stops on early termination.

The full DAG construction rules and the catalogue of static + dynamic edges are in `references/skill-dag.md`. Load that reference when:

- An input has more than one artifact type and you need to determine the dispatch graph
- A probe's finding might open a follow-up edge to another skill
- You need to know which skills can run in parallel

The default DAG starts with these static edges (loaded into context here for the common case):

- `tsql-review` first on any `.sql` files (no execution data needed)
- `sqlwait-review`, `sqltrace-review`, `sqlstats-review`, `sqlquerystore-review`, `sqlprocstats-review` in parallel (triage breadth)
- `sqlplan-review` per `.sqlplan` (or `sqlplan-batch` for folders), feeding `sqlindex-advisor`
- `sqlplan-compare` for plan pairs; `sqldeadlock-review` for deadlock XML
- `sqlerrorlog-review` → `sqlclusterlog-review` → `sqlhadr-review` for AG / failover questions
- `sqlspn-review` for Kerberos / login signals

Dynamic edges open during the walk — e.g., `sqlplan-review` firing S9 opens an edge to `sqlquerystore-review` for plan-instability confirmation even when Query Store is not in the initial DAG (resulting in a Missing Artifact entry if no Query Store output is in the input).

`--phases` flag forces tier-1 fixed-phase behavior for environments where DAG variability is undesirable.

## Multi-Model Cost Routing

Each phase runs on a specific model tier. The default routing minimises cost without sacrificing quality on high-stakes phases. The full phase-to-model table and override rules are in `references/model-routing.md`. Load it when:

- The user asks about cost
- The user passes `--model-tier {economy|standard|maximum}` or `--no-adversarial`
- You need to set the `model` parameter on an Agent subagent dispatch

Default model assignments (the common case):

- Classification, hypothesis generation, cost summary, follow-up Q&A: **Haiku 4.5**
- Triage subagents (specialised skill dispatch): **Haiku 4.5** unless the sub-skill defaults to Sonnet (sqlplan-review, sqlplan-batch, sqlplan-compare, sqlindex-advisor, sqldeadlock-review, sqlclusterlog-review)
- Synthesis, conflict detection, deep-dive analysis: **Sonnet 4.6**
- Adversarial root-cause pass: **Opus 4.5** (claude-opus-4-5; cannot be downgraded even on `--model-tier economy` — quality-critical)

Report the per-phase cost breakdown in the Summary block:

```
Cost: ~USD 0.21 (Haiku 23k tokens, Sonnet 31k tokens, Opus 6k tokens).
```

## Domain Memory

If `~/.mssql-perf-review/instances/.json` exists, load the facts and validate every recommendation against them. The file schema, rejection/escalation catalogue, and staleness rules are in `references/domain-memory.md`. Load that reference when:

- A facts file is present for the target instance
- A recommendation might be redundant (MAXDOP already set, RCSI already enabled, IFI already on)
- A recommendation affects partitioned tables, AG topology, or compatibility level

The orchestrator reads facts.json — it never writes silently. If facts are absent, recommendations are generic and the report notes this. If facts are older than 90 days, the orchestrator warns and downgrades rejection/escalation to "review and confirm".

When facts.json drives a rejection or escalation, the recommendation explicitly cites the file:

```
Rank 1 — REJECTED: facts.json says maxdop already = 8
- Cite: ~/.mssql-perf-review/instances/PROD-SQL01.json
- Replacement recommendation: [next-best, or "no MAXDOP action needed"]
```

## Version-Aware Check Suppression

When `facts.version` is set (e.g. `"version": "SQL Server 2017"`), apply version-gating before emitting the report:

1. Read `VERSION_COMPATIBILITY.md` (`~/.claude/skills/VERSION_COMPATIBILITY.md` if installed, or `skills/VERSION_COMPATIBILITY.md` from the repo). If unavailable, skip suppression silently — do not error.
2. Parse the version-gated check catalog sections (e.g. `## SQL Server 2022+ Only Checks`, `## SQL Server 2019+ Only Checks`). Each section lists check IDs and the minimum SQL Server version required.
3. For every check whose minimum version exceeds `facts.version`, treat it as `SKIP (version)`:
   - **Verbose mode:** Show the row as `SKIP (version: requires SQL 20XX+, instance is SQL 20YY)` in the Check Evaluation Log.
   - **Standard report:** Omit the row entirely — do not surface a finding that cannot apply.
4. Do **not** suppress `NOT ASSESSED` rows caused by missing input artifacts. Version suppression only applies to checks the instance cannot support, not to checks whose input was not provided.

Version facts are treated as a minimum filter only. A check marked `SQL 2019+` fires on SQL 2019, 2022, and later; it is suppressed on SQL 2016 and earlier.

## Follow-Up Q&A

After the report is delivered, stay in the session to answer follow-up questions. Most are free — they read from the in-context evidence chain without new tool calls. The question taxonomy (5 categories), when-to-probe rules, refusal patterns, and answer format are in `references/followup-qa.md`. Load that reference when:

- The user asks "why" or "why not" about a finding or recommendation
- The user asks for a detail from a specialised skill's raw output
- The user asks for the report re-filtered or re-ranked
- The user provides new artifacts and asks "did the fix work?"

Refuse only when the question requires live SQL execution (the orchestrator is strictly offline) or is genuinely out of scope (e.g., upgrade strategy, license cost). Refusal is explicit and brief.

## Capture Bundle Generator

When artifacts are missing (symptom-only input, or partial artifacts that leave hypotheses unconfirmed), emit a self-contained capture bundle to `./captures//`. The bundle contains read-only `.sql` scripts curated for the active hypotheses, a README explaining run order and security, a PASTE-RESULTS-HERE.md template, and a manifest.json mapping scripts to target sub-skills.

The orchestrator does not run the scripts. The user runs them and pastes results back. `--resume ./captures//` continues the analysis from the paste-back.

Full bundle layout, curation rules per hypothesis class, manifest schema, and resume flow are in `references/capture-bundle-spec.md`. Load that reference when:

- The input has missing artifacts and the hypotheses cannot be confirmed without more data
- The user invokes `/sql-triage` with a symptom but no files
- The user passes `--resume` and the orchestrator needs to parse a prior bundle's paste-back

Bundle README and PASTE-RESULTS-HERE.md are filled in from templates at `assets/bundle-readme-template.md` and `assets/paste-results-template.md`. Always include the trust note: "The orchestrator will not contact your SQL Server."

## Verification Checklist and Baseline-Diff Feedback

Every report ends with a Verification — After Deploying Fixes section that turns the per-recommendation `verification` field into an actionable post-deploy checklist. The user re-captures after deploying fixes and returns with `--baseline ./state//state.json` to compare.

When `--baseline` is present:

1. Load prior `state.json` (evidence chain + recommendations).
2. Run normal tier-1/tier-2 dispatch on the new artifacts.
3. For each prior recommendation, find the corresponding finding in the new review's evidence chain.
4. Tag each prior recommendation as `verified-effective` / `partial` / `no-chang

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [vanterx](https://github.com/vanterx)
- **Source:** [vanterx/mssql-performance-skills](https://github.com/vanterx/mssql-performance-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-vanterx-mssql-performance-skills-mssql-performance-review
- Seller: https://agentstack.voostack.com/s/vanterx
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.