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-adminplugin (mssql_list_database_connections,mssql_connect_database, thenmssql_execute_query); any other SQL tool the agent host provides for running T-SQL and reading result sets;sqlcmdfrom a terminal. If the SQL Tools MCP server is not connected or lists no connections, follow thesql-tools-mcp-setupskill, 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 STATEbefore SQL Server 2022) or, on Azure SQL Database,VIEW DATABASE PERFORMANCE STATEor##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_configurewithRECONFIGURE, andALTER 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_configurechanges 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.mdin 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.
DatabaseNameis optional. On Azure SQL Database, the effective scope is the current database even ifDatabaseNameis 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.