Find Ad Hoc Plan Cache Bloat with sys.dm_exec_cached_plans

calendar Aug 13, 2026 / · 9 min read · sql server sys.dm_exec_cached_plans sys.dm_exec_sql_text plan cache ad hoc queries optimize for ad hoc workloads usecounts size_in_bytes compiled plan stub performance monitoring scripts  ·
Share on: linkedin copy

Not every plan in SQL Server's cache earns its place. A query compiled once — because its literal values were hard-coded into the text instead of passed as parameters — gets a full compiled plan cached right alongside plans an application reuses thousands of times a day. sys.dm_exec_cached_plans is where that difference becomes measurable: usecounts shows whether a plan was ever looked up again, and size_in_bytes shows exactly how many bytes it's still holding in memory for the privilege of never running twice.

Purpose and Overview

SQL Server 2022 narrowed the permission needed to query sys.dm_exec_cached_plans from VIEW SERVER STATE to the more specific VIEW SERVER PERFORMANCE STATE, but the diagnostic itself is unchanged across every supported version: the view returns one row per cached query plan, and two of its columns — usecounts and size_in_bytes — are enough to quantify exactly how much of SQL Server's plan cache is occupied by plans that ran once and will never run again.

Ad hoc plan cache bloat has two common causes, both covered directly in Microsoft's own guidance on the optimize for ad hoc workloads server configuration option. The first is query parameters whose data types aren't consistently defined — a string parameter passed at one length on one call and a different length on the next compiles two separate plans instead of reusing one. The second, and more common, is queries that were never parameterized at all: an application builds SQL text with literal values baked in, and the Database Engine has no way to recognize that two functionally identical queries are the same query, so it compiles and caches a distinct plan for every combination of values, lengths, and precisions submitted.

The script below produces two views of that bloat: a summary rollup that expresses single-use plan memory as a percentage of the entire plan cache — the number that turns "the cache feels bloated" into a specific figure worth acting on — and a detail query that surfaces the individual single-use ad hoc plans consuming the most memory, joined to their original query text.

Code Breakdown

Both queries filter on usecounts = 1 — the exact threshold Microsoft's own documentation uses to identify a plan that has never been reused since it was compiled.

 1WITH TotalCache AS (
 2    SELECT SUM(CAST(size_in_bytes AS BIGINT)) AS total_cache_bytes
 3    FROM sys.dm_exec_cached_plans
 4)
 5SELECT
 6    cp.objtype,
 7    cp.cacheobjtype,
 8    COUNT(*)                                                          AS single_use_plan_count,
 9    SUM(CAST(cp.size_in_bytes AS BIGINT))                             AS single_use_bytes,
10    SUM(CAST(cp.size_in_bytes AS BIGINT)) / 1024 / 1024               AS single_use_mb,
11    CAST(SUM(CAST(cp.size_in_bytes AS BIGINT)) * 100.0
12        / tc.total_cache_bytes AS DECIMAL(5,2))                       AS pct_of_total_plan_cache
13FROM sys.dm_exec_cached_plans AS cp
14CROSS JOIN TotalCache AS tc
15WHERE cp.usecounts = 1
16GROUP BY cp.objtype, cp.cacheobjtype, tc.total_cache_bytes
17ORDER BY single_use_bytes DESC;

A companion query drops the aggregation and surfaces the specific single-use ad hoc plans consuming the most memory, joined to sys.dm_exec_sql_text for the original query text:

 1SELECT TOP 25
 2    cp.usecounts,
 3    cp.size_in_bytes,
 4    cp.size_in_bytes / 1024                                          AS size_kb,
 5    cp.cacheobjtype,
 6    cp.plan_handle,
 7    st.text                                                          AS query_text
 8FROM sys.dm_exec_cached_plans AS cp
 9CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
10WHERE cp.usecounts = 1
11    AND cp.objtype = 'Adhoc'
12ORDER BY cp.size_in_bytes DESC;

Filtering to usecounts = 1 and the Adhoc spelling quirk

usecounts counts how many times a cached object has been looked up; per Microsoft's column reference it is not incremented when parameterized queries find a plan already in the cache, which is exactly why a plan sitting at usecounts = 1 is the reliable signal of a true one-off compile rather than an undercount. The second query narrows further with cp.objtype = 'Adhoc' — worth flagging because the column reference table describes this value in prose as "Ad hoc" with a space, but the value SQL Server actually stores, and the one Microsoft's own sample query filters on, is Adhoc with no space. Filtering on the spaced version silently returns zero rows.

Measuring bloat as a share of the entire plan cache, not just the single-use slice

The TotalCache CTE computes the grand total of size_in_bytes across every cached plan — reused and single-use alike — before the outer query filters to usecounts = 1. Dividing the single-use total by that unfiltered grand total, rather than by the sum of single-use bytes alone, is what turns the output into a genuine bloat percentage: a report scoped only to single-use plans always sums to 100% of itself and says nothing about how much of the whole cache those plans actually occupy.

Surfacing the worst offenders with CROSS APPLY sys.dm_exec_sql_text

The second query pairs each cached plan with its source text through sys.dm_exec_sql_text, a table-valued function that takes a plan_handle and returns the batch text that produced it. Ordering by size_in_bytes descending surfaces the specific queries whose plans are largest — often the ones with the most complex WHERE clauses, largest literal lists, or heaviest joins — which is where the case for parameterizing the application code, rather than just tolerating the cache pressure, gets made.

Reading cacheobjtype: Compiled Plan vs. Compiled Plan Stub

cacheobjtype identifies what kind of object the row represents: a full Compiled Plan, a Compiled Plan Stub, a Parse Tree, or one of several CLR- and extended-procedure-related types. Before optimize for ad hoc workloads is turned on, a single-use ad hoc query caches as a full Compiled Plan. After it's turned on, the first compile of a new ad hoc batch caches only a small Compiled Plan Stub instead — comparing the single_use_mb total under cacheobjtype = 'Compiled Plan' before and after enabling the option is the most direct way to see the setting's memory effect on a specific instance.

Key Benefits and Use Cases

  • Turns "the cache feels bloated" into a number — the percentage-of-total-cache column gives a specific figure to bring to a change request instead of an impression.
  • Separates the cause from the symptom — grouping by objtype and cacheobjtype shows whether the bloat is genuinely ad hoc queries versus un-reused prepared statements or other cached object types.
  • Names the worst offenders — the detail query's join to sys.dm_exec_sql_text hands over the actual query text driving the largest single-use plans, the starting point for a parameterization fix at the source.
  • No configuration required — both queries run against a stock instance with no trace flags, Extended Events session, or Query Store setup needed first.
  • Before/after measurable — because the rollup separates Compiled Plan from Compiled Plan Stub, the same query validates whether optimize for ad hoc workloads actually reduced memory after it's enabled.

Performance Considerations

  • Memory-only, resets on restart: like every sys.dm_os_*-family DMV, sys.dm_exec_cached_plans reflects only what's currently in memory — a service restart or a manual cache clear zeroes it out, and the bloat rebuilds only as new ad hoc batches compile under production load.
  • Permission requirement changed in SQL Server 2022: SQL Server 2019 and earlier need VIEW SERVER STATE; SQL Server 2022 and later, along with Azure SQL Managed Instance, require the narrower VIEW SERVER PERFORMANCE STATE instead.
  • Enabling the option doesn't touch what's already cached: setting optimize for ad hoc workloads to 1 affects only plans compiled after the change — existing single-use plans stay in the cache at full size until they age out naturally, or until the cache is cleared with ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE or the instance restarts.
  • It fixes memory, not CPU: Brent Ozar has documented that on an application generating high volumes of unparameterized queries, optimize for ad hoc workloads "doesn't help because compilations still cause high CPU, and the queries aren't grouped in the plan cache (and age out so quickly)" — this script measures the memory side of the problem, but a high-compile-rate instance may still need parameterization at the application layer to address CPU.
  • Trace flag 8032 is the opposite lever: if reused plans are being evicted too aggressively rather than the cache being full of single-use bloat, trace flag 8032 reverts the cache's size limits to a more permissive setting — but it can starve other memory consumers like the buffer pool if applied without first checking that single-use bloat isn't the actual cause.

Practical Tips

  • Run the summary query before and after enabling optimize for ad hoc workloads and compare single_use_mb under cacheobjtype = 'Compiled Plan' specifically — that delta is the actual memory recovered.
  • Schedule the summary query as a SQL Server Agent job writing to a history table so pct_of_total_plan_cache becomes a trend line rather than a single snapshot, since bloat rebuilds continuously under an unparameterized workload.
  • Before flipping the server option, pull a sample of the detail query's query_text column and check whether the pattern is inconsistent parameter typing or genuinely unparameterized literals — the fix at the application layer differs for each.
  • If CPU and compilations/sec are elevated alongside the memory bloat, evaluate Forced Parameterization or Query Store alongside this fix rather than relying on optimize for ad hoc workloads alone, since it addresses cache memory, not compile CPU.
  • Pair this report with sp_BlitzCache from the Brent Ozar First Responder Kit for a deeper look at resource usage per plan once the worst single-use offenders are identified here.

Conclusion

sys.dm_exec_cached_plans, filtered to usecounts = 1 and rolled up against the total size of the plan cache, turns ad hoc plan cache bloat from a hunch into a measured percentage — and the detail query behind it names the specific queries responsible. That pairing is the evidence a DBA needs before flipping optimize for ad hoc workloads on a production instance, and the same query validates afterward that the setting actually recovered the memory it promised.

References

Posts in this series