$ guides / microsoft-sql-server / microsoft-sql-server-plan-cache-bloat ▌

Operations Guides

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

Cause	What it looks like	First thing to check
Application emits non-parameterized SQL	`sys.dm_exec_cached_plans` dominated by `objtype = 'Adhoc'` with `usecounts = 1`; query texts differ only in literal values	Sample `sys.dm_exec_sql_text` for the top single-use plans and look for inlined literals
ORM generating unique SQL per call	Same as above, but the app uses Entity Framework, Hibernate, or similar; sometimes caused by lambda expressions that prevent parameterization	Check ORM configuration for parameterization options
Reporting or BI tool submitting ad-hoc queries	Burst of single-use plans during report windows; `CACHESTORE_SQLCP` grows sharply at specific times	Correlate plan cache growth with batch request patterns by time of day
`optimize for ad hoc workloads` disabled	Full plans cached on first execution even for queries that never run again	Check `EXEC sp_configure 'optimize for ad hoc workloads'`
Plan cache eviction driving recompiles	`SQL Compilations/sec` high, but plan cache is not growing because memory pressure evicts plans as fast as they are added	Check `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

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.
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.
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.
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.
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.
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

Signal	Why it matters	Warning sign
Single-use plan count and size	Direct measure of wasted memory	`wasted_mb` above 2GB or above 50% of plan cache
`CACHESTORE_SQLCP` clerk size	Shows plan cache memory growth over time	Above 10-15% of max server memory
Compilations to batch requests ratio	Indicates plan cache reuse efficiency	Sustained above 10%, approaching 100% in severe cases
Page Life Expectancy	Falls when buffer pool shrinks	Downward trend over days or weeks
Buffer cache hit ratio	Declines as buffer pool is squeezed	Below 95% on OLTP workloads
`PAGEIOLATCH_*` wait time	Rises when physical reads increase	Becoming a top wait
`MEMORYCLERK_SQLBUFFERPOOL` size	Should be the dominant memory clerk	Shrinking 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 (PSP) optimization to mitigate this for some query shapes. PSP optimization requires SQL Server 2022 or later with database compatibility level 160, and it only handles equality predicates; SQL Server 2025 extends it to DML statements and tempdb.

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 (SQL Server 2019 and later)
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. Constructs that bypass it include INSERT...EXECUTE statements, statements inside stored procedures, triggers, or user-defined functions, client-side prepared statements, statements inside T-SQL cursors, statements run with ANSI_PADDING or ANSI_NULLS set to OFF, statements that reference variables, batches with more than 2,097 eligible literals, statements with the RECOMPILE query hint, COMPUTE clauses, and WHERE CURRENT OF. Clauses such as TOP, HAVING, GROUP BY, ORDER BY, LIKE patterns, and FOR XML are also not parameterized.

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.

The Netdata solution

Microsoft SQL Server monitoring with Netdata

Netdata monitors SQL Server with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate wait statistics, blocking chains, transaction-log and TempDB pressure, Page Life Expectancy, memory grants, per-file I/O stalls, and AlwaysOn send/redo queues against the rest of your stack so you catch the incidents in these runbooks before they page anyone.

See SQL Server monitoring → Start monitoring free