A plan can occupy cache memory after serving only one request. Finding single-use plans helps distinguish useful reuse from a stream of unrelated ad hoc statements.
Ask What the Cache Retains
The plan cache stores compiled information so future work can reuse it. Applications that send different literal text repeatedly create opportunities for separate entries. Those entries compete with other cached work for memory.
I inspect the size of the retained entries before reacting to their count. I also examine their statement shape before recommending parameterization. A collection of small stubs is different from a collection of full compiled plans.
A single-use entry is not automatically waste. A report requested once can still deserve compilation and execution. The practical concern is whether repeated business operations unnecessarily arrive as different statements.
The cache cannot predict which statement will become tomorrow's favorite. It retains entries according to its management policies and available memory. One snapshot therefore needs workload context before it supports a change.
Use the required server diagnostic permission for these queries. SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE for cache inspection. Older versions use VIEW SERVER STATE, with metadata visibility affecting what text is available.
Group Single-Use Plans by Object Type
The first query groups entries with usecounts equal to one by object type. It sums their retained size in binary megabytes. Keep the cache object type visible so full plans and other objects do not disappear into one total.
SELECT objtype, cacheobjtype,
COUNT_BIG(*) AS EntryCount,
SUM(CONVERT(decimal(28,2), size_in_bytes)) / 1048576.0 AS CachedMiB
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
GROUP BY objtype, cacheobjtype
ORDER BY CachedMiB DESC;Adhoc identifies ad hoc batches, while Prepared includes prepared or parameterized forms. Proc identifies cached procedures. Do not assume every object type has the same reuse pattern or application source.
Usecounts records cache lookups, not an exact business execution count. Some execution and lookup behavior makes that distinction important. Treat usecounts equal to one as the inspection criterion rather than an accounting ledger.
A compiled plan stub is intentionally smaller than a complete compiled plan. Keep those rows separate when the relevant server behavior is enabled. A large entry count can coexist with a modest retained size.
The totals describe this instance at collection time. Eviction and ongoing compilation can change them during the query. Save the capture time when comparing snapshots rather than treating the result as a fixed inventory.
Compare Single-Use Plans With the Whole Cache
Absolute memory tells you the size, while a proportion tells you its context. Calculate both from the same cache view scan. Use decimal arithmetic and protect the denominator when the selected cache is empty.
WITH CacheSize AS
(
SELECT SUM(CONVERT(decimal(28,2), size_in_bytes)) AS TotalBytes,
SUM(CASE WHEN usecounts = 1
THEN CONVERT(decimal(28,2), size_in_bytes)
ELSE CONVERT(decimal(28,2), 0) END) AS SingleUseBytes
FROM sys.dm_exec_cached_plans
)
SELECT TotalBytes / 1048576.0 AS TotalCacheMiB,
SingleUseBytes / 1048576.0 AS SingleUseMiB,
100.0 * SingleUseBytes / NULLIF(TotalBytes, 0) AS SingleUsePercentage
FROM CacheSize;Single-use plans taking a large proportion deserve inspection when memory is constrained. A proportion by itself still does not show user impact. Connect it with memory pressure, compilation activity, and the useful entries being displaced.
A restart clears the plan cache. A freshly started instance therefore tells you little about a longer workload pattern. Record server startup time and avoid comparing a fresh cache with a mature one as equivalent periods.
Configuration changes, explicit clearing, and memory pressure also disturb the retained population. Check those boundaries before drawing a trend. A sudden smaller total can reflect eviction rather than improved application behavior.
Inspect Representative Statements
Look at the largest single-use ad hoc entries and their text. The sample query also returns the handle so you can connect the text with further inspection. Keep the object type filters visible rather than selecting arbitrary cache content.
SELECT TOP (30) cp.plan_handle, cp.usecounts, cp.size_in_bytes,
cp.cacheobjtype, st.dbid, st.[text] AS BatchText
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.usecounts = 1
AND cp.objtype = N'Adhoc'
ORDER BY cp.size_in_bytes DESC, cp.plan_handle;Look for the same operation repeated with different identifiers or dates in its text. Those repeated shapes identify application calls worth reviewing. A unique administrative command deserves a different interpretation.
Batch text can contain sensitive literals. Store examples in a restricted investigation location and redact them before sharing. Cache diagnosis should not become another distribution path for customer data.
The text function does not identify every originating application request. Correlate it with appropriate application diagnostics or controlled observation. Avoid blaming a program solely because one statement resembles its tables.
Parameterize the Repeated Operation
Use parameters for values instead of assembling new text for every value. sp_executesql accepts a parameter definition and typed arguments. The demonstration below keeps its statement text identical across different synthetic inputs.
DECLARE @Sql nvarchar(max) =
N'SELECT @CustomerID AS CustomerID, @StartDate AS StartDate;';
EXEC sys.sp_executesql @Sql,
N'@CustomerID int, @StartDate date',
@CustomerID = 7, @StartDate = '20260101';
EXEC sys.sp_executesql @Sql,
N'@CustomerID int, @StartDate date',
@CustomerID = 8, @StartDate = '20260201';Use parameter types that agree with the target columns in real queries. Wrong types and lengths can introduce implicit conversions or unnecessary variation. Keeping text stable is useful, but the parameter contract must also be correct.
Object names cannot be passed as ordinary value parameters. When identifiers truly vary, validate and quote them separately. Do not turn that exception into permission to concatenate untrusted values throughout the batch.
Parameterization also introduces parameter sensitivity when data is skewed. Review the resulting plans with representative values. Better cache reuse and good estimates are related goals rather than interchangeable guarantees.
Discuss the Setting Without Changing It
The optimize for ad hoc workloads server option changes how first-use ad hoc plans are retained. It is a configuration decision to discuss after inspecting the workload. This article's queries do not change that option.
It does not rewrite application statements or remove the cost of compilation. Existing cached entries also do not instantly transform because someone discusses the setting. Evaluate expected behavior, monitoring, and change timing before any separate configuration decision.
Which repeated calls should share a typed statement instead of separate literal batches? That application question addresses the source of the entries. A cache option addresses how some entries are retained afterward.
Measure Single-Use Plans in the Next Workload Window
Capture the same grouped totals after an application change during a comparable workload period. Keep startup time, cache disruptions, and request volume beside the figures. Different workload windows need a qualified comparison.
Single-use plans remain useful evidence when their size, shape, and lifetime are considered together. Review the expensive retained examples and the repeated application pattern. Avoid treating every once-used entry as an automatic defect.
Finish with a concrete parameterization candidate and a method for checking its behavior. Keep configuration discussions separate from the read-only investigation. A useful cache review points to work that can be verified.
Related reading on this blog: When to Turn On Optimize for Ad Hoc Workloads? and Forced Parameterization: When It Cuts CPU and When It Hurts.
A single-use cache entry is not a verdict, it is a reuse question supported by context.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
