SQL Server plan cache bloat: single-use ad-hoc plans wasting memory

SQL Server plan cache bloat from single-use ad-hoc plans is a slow degradation. Queries return correct results. The instance stays up. But memory that should cache hot data pages holds thousands of compiled plans that will never execute again. Operators typically discover it when PLE drifts down for no obvious reason, when buffer cache hit ratio slips, or when compilations per second stays elevated relative to batch requests. By the time it surfaces in user-facing latency, the plan cache has been stealing buffer pool memory for weeks.

The pattern is well-defined. An application submits non-parameterized SQL. Each literal value produces a distinct query text. SQL Server compiles each one, caches the resulting plan, and assigns it usecounts = 1. When the same logical query arrives with a different literal, a new plan is created. Over time, the plan cache fills with plans that executed exactly once and will never be reused.

This is distinct from a compile storm or a parameter sniffing regression. Those are acute events. Plan cache bloat is chronic. It is a slow leak of buffer pool memory into CACHESTORE_SQLCP, the plan cache clerk that holds ad-hoc and prepared plans. The fix is almost always the same shape: stop caching plans you will never reuse, parameterize the workload, or both.

What this means

The plan cache is not free. It lives in memory that SQL Server could otherwise use for the buffer pool. When CACHESTORE_SQLCP grows large enough to displace data pages, the buffer pool shrinks, page life expectancy drops, and physical read volume climbs. The system trades memory that was doing useful work for memory holding plans that executed once.

The canonical signature is a large count of objtype = 'Adhoc' plans with usecounts = 1 in sys.dm_exec_cached_plans. The playbook threshold for investigation is single-use plans consuming more than 2GB, or more than 50% of total plan cache size. Anything above roughly 10-15% of max server memory in the plan cache clerk is worth a look.

A second signature is elevated SQL Compilations/sec relative to Batch Requests/sec. The healthy ratio is under 10%. When single-use ad-hoc queries dominate, the ratio climbs toward 100% because almost every batch requires a fresh compile.

flowchart TD
    A[Non-parameterized ad-hoc SQL] --> B[Each literal caches a unique plan]
    B --> C[Plan cache fills with usecounts=1 plans]
    C --> D[Buffer pool memory stolen]
    D --> E[PLE drops, physical reads rise]
    C --> F[Cache evictions drive recompiles]
    F --> G[Compilation CPU cost climbs]
    E --> H[Query latency increases]
    G --> H

Common causes

CauseWhat it looks likeFirst thing to check
Application emits non-parameterized SQLsys.dm_exec_cached_plans dominated by objtype = 'Adhoc' with usecounts = 1; query texts differ only in literal valuesSample sys.dm_exec_sql_text for the top single-use plans and look for inlined literals
ORM generating unique SQL per callSame as above, but the app uses Entity Framework, Hibernate, or similar; sometimes caused by lambda expressions that prevent parameterizationCheck ORM configuration for parameterization options
Reporting or BI tool submitting ad-hoc queriesBurst of single-use plans during report windows; CACHESTORE_SQLCP grows sharply at specific timesCorrelate plan cache growth with batch request patterns by time of day
optimize for ad hoc workloads disabledFull plans cached on first execution even for queries that never run againCheck EXEC sp_configure 'optimize for ad hoc workloads'
Plan cache eviction driving recompilesSQL Compilations/sec high, but plan cache is not growing because memory pressure evicts plans as fast as they are addedCheck CACHESTORE_SQLCP size relative to max server memory

Quick checks

These are all read-only. Run them from any session with VIEW SERVER STATE.

-- Single-use plan count and wasted memory
SELECT COUNT(*) AS single_use_plans,
       SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS wasted_mb
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1 AND objtype = 'Adhoc';

-- Plan cache by object type
SELECT objtype,
       COUNT(*) AS plan_count,
       SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS size_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_mb DESC;

-- Plan cache clerk size relative to buffer pool
SELECT type, name, pages_kb / 1024 AS size_mb
FROM sys.dm_os_memory_clerks
WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP', 'MEMORYCLERK_SQLBUFFERPOOL')
ORDER BY pages_kb DESC;

-- Sample the worst offenders (query text)
SELECT TOP (50)
    usecounts,
    objtype,
    size_in_bytes / 1024 AS size_kb,
    SUBSTRING(t.text, 1, 200) AS query_text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE p.usecounts = 1 AND p.objtype = 'Adhoc'
ORDER BY p.size_in_bytes DESC;

-- Compilations relative to batch requests (cumulative; sample twice and compute rate)
SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('SQL Compilations/sec', 'SQL Re-Compilations/sec', 'Batch Requests/sec')
  AND object_name LIKE '%SQL Statistics%';

-- Check whether optimize for ad hoc workloads is enabled
EXEC sp_configure 'optimize for ad hoc workloads';

How to diagnose it

  1. Quantify the bloat. Run the single-use plan query from the quick checks. If wasted_mb is under 1GB on a server with tens of GB of max server memory, plan cache bloat is probably not your primary problem. Look elsewhere: wait stats, memory grants, blocking.

  2. Confirm the source. Sample the top single-use plans by size. If the query texts differ only in literal values, you have non-parameterized ad-hoc SQL. If the texts are structurally different, you may have a workload that genuinely issues many distinct queries, which is a different problem.

  3. Check the compilation ratio. Compute SQL Compilations/sec divided by Batch Requests/sec over a sampling window. Above 10% warrants investigation. Near 100% confirms the plan cache is providing almost no reuse benefit.

  4. Correlate with memory pressure. Check PLE, buffer cache hit ratio, and PAGEIOLATCH_* waits. If single-use plan bloat is the cause, you should see PLE trending down and physical read volume climbing as the buffer pool shrinks.

  5. Identify the application. Group single-use plans by the originating database or application name from sys.dm_exec_sessions if you can capture them in flight, or by dbid from the cached plans.

  6. Check the server configuration. Confirm whether optimize for ad hoc workloads is enabled and whether any databases use forced parameterization.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Single-use plan count and sizeDirect measure of wasted memorywasted_mb above 2GB or above 50% of plan cache
CACHESTORE_SQLCP clerk sizeShows plan cache memory growth over timeAbove 10-15% of max server memory
Compilations to batch requests ratioIndicates plan cache reuse efficiencySustained above 10%, approaching 100% in severe cases
Page Life ExpectancyFalls when buffer pool shrinksDownward trend over days or weeks
Buffer cache hit ratioDeclines as buffer pool is squeezedBelow 95% on OLTP workloads
PAGEIOLATCH_* wait timeRises when physical reads increaseBecoming a top wait
MEMORYCLERK_SQLBUFFERPOOL sizeShould be the dominant memory clerkShrinking relative to plan cache

Fixes

The fixes fall into three categories, in order of preference: parameterize the workload, enable stub caching for ad-hoc plans, or force parameterization at the database level.

Parameterize the application

The durable fix is to stop emitting non-parameterized SQL. Use sp_executesql with parameters, parameterized stored procedures, or ORM configurations that emit parameterized queries. This eliminates the root cause: the same logical query with different literals should compile once and reuse the plan.

This is also the only fix that reduces compilation CPU cost in addition to memory waste. Parameterized queries hit the plan cache on second and subsequent executions.

The tradeoff: parameterization can introduce parameter sniffing problems if data distribution is skewed. SQL Server 2022 introduced Parameter Sensitive Plan optimization to mitigate this for some query shapes.

Enable optimize for ad hoc workloads

This is the lowest-risk mitigation when you cannot immediately change the application. When enabled, SQL Server stores a compiled plan stub on first execution instead of the full plan. The full plan replaces the stub only if the same batch executes again. Single-use queries consume a stub instead of a full plan.

Server-level option:

-- Enable at the server level (requires advanced options)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

Database-scoped option:

-- Enable at the database level
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

Important behaviors:

  • Enabling the option does not affect plans already in cache. To clear existing single-use plans, run DBCC FREESYSTEMCACHE('SQL Plans') or ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE for the database-scoped variant.
  • Turning the database-scoped option on or off clears the plan cache for that database. Plan for the compile spike.
  • With only a stub cached, you cannot view the execution plan for a query that has not been promoted to a full plan. Disable the option temporarily if you need to capture a specific ad-hoc plan.

This fix reduces memory waste but does not reduce compilation CPU cost. Every batch still compiles once. The stub just prevents the full plan from being cached.

Forced parameterization

Forced parameterization tells SQL Server to parameterize eligible literals in ad-hoc SQL at the database level, so the same query shape with different literals reuses one plan.

ALTER DATABASE [YourDatabase] SET PARAMETERIZATION FORCED;

This is a bigger hammer. It can dramatically reduce single-use plan count and compilation rate for workloads the application cannot easily change. But it also introduces parameter sniffing risk on skewed data distributions. Test it on a non-production copy of the workload first.

Forced parameterization does not parameterize every query. Certain constructs are not eligible. Check the official documentation for the exception list.

Clear the plan cache (emergency only)

If plan cache bloat is actively causing memory pressure and you need immediate relief before applying a durable fix:

-- Clears only ad-hoc and prepared plans, leaves stored procedure plans intact
DBCC FREESYSTEMCACHE('SQL Plans');

This is a temporary measure. Without one of the fixes above, the bloat returns as the workload continues. Expect a compile spike immediately after as queries recompile.

Do not run DBCC FREEPROCCACHE without arguments in production unless you accept a full plan cache flush and the resulting compile storm.

Prevention

  • Enable optimize for ad hoc workloads on every instance. It is OFF by default and is the single most effective guard against single-use plan bloat.
  • Monitor single-use plan size as a trend, not just a threshold. A growing wasted_mb over weeks indicates workload drift toward ad-hoc SQL.
  • Track the compilations to batch requests ratio. Sustained above 10% means the plan cache is not doing its job.
  • Review ORM configurations during application onboarding. Certain ORM constructs, such as IN clauses with varying list sizes or dynamic predicates, can emit non-parameterized SQL.
  • Watch plan cache size creeping above 10-15% of max server memory. That is the point where the buffer pool is meaningfully squeezed.
  • Capture a baseline after enabling optimize for ad hoc workloads. You need to know what normal looks like before drift starts.

How Netdata helps

Netdata surfaces the signals that reveal plan cache bloat before it becomes user-visible latency:

  • Single-use plan count and wasted MB collected from sys.dm_exec_cached_plans, so you see bloat accumulate over time rather than discovering it during an incident.
  • Plan cache clerk size tracked alongside MEMORYCLERK_SQLBUFFERPOOL, making the tradeoff between plan cache and buffer pool visible on one chart.
  • Compilations per second and batch requests per second as correlated rates, with the ratio computed so you do not have to sample and calculate by hand.
  • Page Life Expectancy and buffer cache hit ratio trended continuously, showing the downstream effect of buffer pool pressure from plan cache growth.
  • ML-based anomaly detection on these signals flags the slow drift that threshold-based alerting misses, which is exactly the failure mode of plan cache bloat.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.