# Plan Cache Analysis

> Use when the user asks about plan cache bloat, single-use or ad hoc plans, poor plan reuse, SET option pollution or parameter sniffing suspects. For one query's plans, use `plan-cache-qds-bridge`.

- Skill: `microsoft/plan-cache-analysis` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx get-skill add microsoft/plan-cache-analysis`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/plan-cache-analysis/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/plan-cache-analysis

---


# Plan Cache Analysis

## Purpose

Use this skill when the user asks about plan cache bloat,
cached plan efficiency, parameter sniffing suspects,
or single-use ad hoc query patterns.

It provides a staged workflow for cache composition,
top offenders, parameterization health, SET option
pollution, and `optimize for ad hoc workloads` guidance.

## Prerequisites

- Must be connected to the target database.
- Confirm which server and database you are connected to
  (e.g. `SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME(), SERVERPROPERTY('EngineEdition')`).
- Run every diagnostic step in this skill with the
  SQL query tool.
- Plan cache data is cumulative since last server restart
  or manual cache clear.

## Required tools

- A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL
  Tools MCP server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`,
  `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides for
  running T-SQL and reading result sets;
  [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the SQL
  Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup` skill,
  which asks before installing or configuring anything. If no SQL tool can be made available, give the
  user the T-SQL to run and ask for the results.
- Least privilege: connect that tool with a diagnostic login that cannot write, for example one that
  holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure
  SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read
  permission these queries need. A tool is only as read-only as the login it connects with.

With the SQL Tools MCP server, run these DMV queries through `mssql_execute_query`. To inspect a
cached plan, pass its `plan_handle` as `planHandle` to `SummarizePlan`, `GetPlanWarnings` or
`PlanBottleneckAnalysis` instead of selecting the plan XML. One plan-tool call per plan; do not
re-run the statement to compare variants.

## Parameters

| Parameter | Default | Description |
|-----------|---------|-------------|
| TopN | 50 | Number of top plans to return in ranked results |
| DatabaseName | NULL | Optional database name filter. NULL = all databases (SQL Server/MI) or current database (Azure SQL DB). |
| MinExecutionCount | 10 | Minimum executions for a plan to appear in parameter sniffing analysis |

## Platform Support

| EngineEdition | Platform | Supported | Notes |
|---|---|---|---|
| 1 | Personal / Desktop Engine | Yes | Legacy; not available in SQL Server 2005+ |
| 2 | Standard | Yes | Standard, Developer, Web, BI editions |
| 3 | Enterprise | Yes | Enterprise, Developer, Evaluation editions |
| 4 | Express | Yes | Express editions |
| 5 | Azure SQL Database | Yes | Single-database scope, no cross-database queries |
| 6 | Azure Synapse Analytics (Dedicated SQL Pool) | **No** | Plan cache DMVs behave differently — stop here |
| 8 | Azure SQL Managed Instance | Yes | Instance-wide scope, most on-prem DMVs available |
| 9 | Azure SQL Edge | Yes | DMV behavior similar to SQL Server on-prem |
| 11 | Azure Synapse Serverless SQL Pool / Microsoft Fabric (non-SQL DB) | **No** | Plan cache DMVs not available — stop here |
| 12 | Fabric SQL Database | Yes | Behaves like Azure SQL Database (single-database scope) |
| other | SQL Server on-prem / IaaS | Yes | Full DMV access |

## Remediation Safety

- All diagnostic queries in this skill are read-only.
- State-changing commands in the references (`sp_configure` with `RECONFIGURE`, and
  `ALTER DATABASE SCOPED CONFIGURATION`) are **recommendations only**.
- Never run any of them without the user's explicit confirmation for that specific command. A
  request to investigate, diagnose, or "fix" the problem is not that confirmation.
- Before asking, explain the impact and rollback: `sp_configure` changes apply to the whole instance,
  and each change is reverted by setting the previous value.

## Investigation Workflow

Run the steps in order.
Load each query from `references/queries.md`, replace the
placeholders with the skill parameters, and execute it with
the SQL query tool.

### Step 0: Detect Platform and Capabilities
Load and execute the Step 0 query from `references/queries.md`.
This determines the SQL Server engine edition and routes the skill to the correct platform path.
If `is_unsupported_platform = 1` (editions 6, 11), stop — this skill does not support Azure Synapse Dedicated SQL Pool or Azure Synapse Serverless SQL Pool / Microsoft Fabric non-SQL DB endpoints.

| Finding | Recommendation |
|---------|----------------|
| `is_unsupported_platform = 1` (engine edition 6 or 11) | **Stop here** — this skill is not designed for Azure Synapse Dedicated SQL Pool (edition 6) or Azure Synapse Serverless SQL Pool / Microsoft Fabric Warehouse (edition 11). Plan cache DMVs behave differently in these environments. |
| `engine_edition = 12` (Fabric SQL Database) | Behaves like Azure SQL Database — proceed with all steps. |
| `engine_edition = 9` (Azure SQL Edge) | Proceed — DMV behavior is similar to SQL Server on-prem. |

### Step 1: Plan Cache Bloat Overview
Load and execute the Step 1 query from `references/queries.md`.
Key signals: `Plans Used Once` > 50% of total plans or
`MBs Used Once` > 25% of total MBs indicates plan cache bloat.
If ad hoc plans dominate by count or memory, use later steps
to isolate the worst offenders and the best remediation path.

### Step 2: Plan Cache Contents Detail
Load and execute the Step 2 query from `references/queries.md`.
Focus on large cached plans, single-use ad hoc plans,
low-reuse ad hoc plans, and single-use prepared plans.
The strongest signals are `objtype = 'Adhoc'` with
`usecounts = 1`, `usecounts <= 3`, or plans larger than 1 MB.

### Step 3: Platform-Aware Bloat Summary
Load and execute the Step 3 query from `references/queries.md`.
Key signals: Adhoc `single_use_pct >= 70`,
`single_use_mb >= 128`, or an overall verdict that calls out
classic plan cache bloat.
Use this step to decide whether the issue is instance-wide or
current-database scoped.

### Step 4: Top Single-Use Ad Hoc Plans
Load and execute the Step 4 query from `references/queries.md`.
Focus on top single-use Adhoc plans by memory, especially
string concatenation patterns, weak `sp_executesql` usage,
and very large plans.
These rows usually identify the highest-value queries to
parameterize or convert to stored procedures first.

### Step 5: Parameter Sniffing Detection
Load and execute the Step 5 query from `references/queries.md`.
Key signals are `HIGH suspicion`, very large
`max_to_min_ratio`, and supporting CPU or logical-read variance.
Treat this as a heuristic screen only; confirm true
parameter-sensitive behavior with Query Store or plan comparison.

### Step 6: SET Option Analysis
Load and execute the Step 6 query from `references/queries.md`.
Look for inconsistent `ARITHABORT`, `ANSI_NULLS`,
`QUOTED_IDENTIFIER`, or multiple `set_options` combinations.
These differences are strong indicators of plan cache pollution
caused by inconsistent connection settings.

### Step 7: Ad Hoc vs Prepared Summary
Load and execute the Step 7 query from `references/queries.md`.
Key signals: Adhoc `single_use_pct >= 50` suggests bloat,
while Prepared dominance usually indicates healthier reuse.
Use this step to summarize whether the workload is mostly
unparameterized or already trending toward reusable plans.

### Step 8: Optimize for Ad Hoc Workloads Check
Load and execute the Step 8 query from `references/queries.md`.
The key signal is whether `optimize for ad hoc workloads` is OFF
when earlier steps already show meaningful single-use bloat.
Report the platform-specific enable command, but do not execute
configuration changes unless the user explicitly asks.

## Gotchas

- Run only the diagnostic queries from `references/queries.md`
  in this skill flow.
- Do not modify the SQL in `references/queries.md`;
  only replace the documented placeholders.
- Engine edition 11 can be Azure Synapse Serverless SQL Pool
  or Microsoft Fabric (non-SQL DB endpoints). Both are unsupported
  because plan cache DMVs are not available. Only Fabric SQL
  Database (edition 12) is supported.
- `DatabaseName` is optional. On Azure SQL Database,
  the effective scope is the current database even if
  `DatabaseName` is supplied.
- Step 5 is heuristic only; blocking, spills, or memory pressure
  can mimic parameter sniffing.
- Step 6 inspects the top 200 compiled plans by size,
  so it is a targeted sample rather than a full-cache census.
- Step 8 returns recommended commands only;
  it does not change server or database settings.

## Report Reference

Format the final answer with the template in
`references/report-template.md`.
Cite the outputs from `references/queries.md` that justify
each recommendation in the final report.

