SQL Server high compilations per second: plan cache pollution and CPU burn

SQL Compilations/sec is climbing, CPU is pinned, and the application is reporting latency even though storage and locking look clean. Nothing is “broken” in the error log, but the engine is spending a large share of its CPU budget turning query text into execution plans instead of executing them.

Compilation is expensive. Every new plan costs parse, optimization, and often a compile-time memory grant. When the ratio of SQL Compilations/sec to Batch Requests/sec climbs past roughly 10%, the plan cache is failing to do its job. Sustained above 20% with CPU pressure, you have an active problem.

This symptom has a short list of root causes, and each one has a distinct fingerprint in the DMVs. This article walks through confirming the symptom, identifying which cause you have, and fixing it without making parameter sniffing worse.

What this means

SQL Server caches execution plans so that the cost of optimization is paid once per distinct query shape, not once per execution. Three things break that deal:

1. Non-parameterized ad-hoc SQL. If the application sends literal values inline (WHERE CustomerId = 4815 instead of a parameter), every distinct value produces a distinct query text, a distinct cache lookup miss, and a fresh compilation. The plan cache fills with thousands of single-use plans. This is plan cache pollution, and it is by far the most common cause.
2. Plan eviction under memory pressure. The plan cache competes with the buffer pool for memory. When memory gets tight, plans are evicted, and the next execution of the same query compiles again. The compilations are a symptom; memory pressure is the disease.
3. Overused OPTION (RECOMPILE). The hint forces a fresh compile on every execution. Used surgically on a parameter-sniffing problem query it is a legitimate tool. Applied broadly, it guarantees that your compilation rate tracks your batch rate.

The counters SQL Compilations/sec and SQL Re-Compilations/sec are cumulative since instance startup, so a single reading of cntr_value is meaningless. Everything below assumes you are computing rates from delta samples.

Common causes

One thing that is not a cause: a compilation spike immediately after a restart, DBCC FREEPROCCACHE, a deployment that updates statistics, or a schema change. The plan cache is cold or invalidated and every first execution compiles. This decays on its own.

Quick checks

All read-only. Run them in order; the first three usually identify the cause.

-- 1. Current compilation and batch request rates (delta sample over 10 seconds)
DECLARE @c1 BIGINT, @b1 BIGINT, @c2 BIGINT, @b2 BIGINT, @r1 BIGINT, @r2 BIGINT;
SELECT @c1 = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Compilations/sec' AND object_name LIKE '%SQL Statistics%';
SELECT @b1 = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec' AND object_name LIKE '%SQL Statistics%';
SELECT @r1 = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Re-Compilations/sec' AND object_name LIKE '%SQL Statistics%';
WAITFOR DELAY '00:00:10';
SELECT @c2 = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Compilations/sec' AND object_name LIKE '%SQL Statistics%';
SELECT @b2 = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec' AND object_name LIKE '%SQL Statistics%';
SELECT @r2 = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Re-Compilations/sec' AND object_name LIKE '%SQL Statistics%';
SELECT
    (@c2 - @c1) / 10.0 AS compilations_per_sec,
    (@b2 - @b1) / 10.0 AS batch_requests_per_sec,
    (@r2 - @r1) / 10.0 AS recompilations_per_sec,
    100.0 * (@c2 - @c1) / NULLIF(@b2 - @b1, 0) AS compile_to_batch_pct;

-- 2. Single-use ad-hoc plan bloat (the smoking gun for plan cache pollution)
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';

-- 3. Is memory pressure evicting plans? Check the plan cache clerk and PLE.
SELECT name, pages_kb / 1024 AS size_mb
FROM sys.dm_os_memory_clerks
WHERE name IN ('CACHESTORE_SQLCP', 'MEMORYCLERK_SQLBUFFERPOOL')
ORDER BY pages_kb DESC;

SELECT cntr_value AS ple_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer Node%';  -- per NUMA node, not the averaged Buffer Manager counter

-- 4. Is the CPU actually going to compilation? Check scheduler pressure.
SELECT scheduler_id, runnable_tasks_count, active_workers_count
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
ORDER BY runnable_tasks_count DESC;

-- 5. Confirm CPU pressure from the wait side
SELECT wait_type, wait_time_ms, signal_wait_time_ms,
       CAST(100.0 * signal_wait_time_ms / NULLIF(wait_time_ms, 0) AS DECIMAL(5,2)) AS signal_pct
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('SOS_SCHEDULER_YIELD', 'RESOURCE_SEMAPHORE_QUERY_COMPILE');

RESOURCE_SEMAPHORE_QUERY_COMPILE waits mean even compilation itself is queuing for compile-time memory grants. That is the extreme end of this failure mode.

How to diagnose it

flowchart TD
    A[Compilations/sec vs Batch Requests/sec above 10 percent] --> B{Single-use Adhoc plans in cache?}
    B -->|Yes, thousands, GBs wasted| C[Plan cache pollution: parameterize workload]
    B -->|No| D{PLE dropping / plan cache clerk shrinking?}
    D -->|Yes| E[Memory pressure evicting plans: fix memory first]
    D -->|No| F{Recompiles high relative to compiles?}
    F -->|Yes| G[Stats updates or schema churn]
    F -->|No| H[Find RECOMPILE hints on hot statements]

1. Confirm the ratio and rule out warmup. Run check 1. If the spike began at a deploy, restart, or cache clear and is decaying, you are watching a cold plan cache warm up. Give it time before changing anything.
2. Check for ad-hoc bloat. Run check 2. Thousands of single-use Adhoc plans and hundreds of MB (or GB) of wasted cache is conclusive for non-parameterized SQL. A workable alert threshold: single-use plans over 2 GB or over 50% of the plan cache.
3. Check for eviction. Run check 3. If the CACHESTORE_SQLCP clerk is small or shrinking while PLE is dropping per NUMA node, plans are being evicted under memory pressure. Do not tune the plan cache; fix the memory problem. Compilations are collateral damage.
4. Separate compiles from recompiles. From check 1’s output: if recompilations are high relative to compilations (ratio above ~10%), suspect statistics auto-updates or schema/index changes invalidating plans, often correlated with data load windows.
5. Hunt the RECOMPILE hints. If compiles cluster on a small set of procedures or statements, search the module text for the hint:

-- Find modules using RECOMPILE
SELECT OBJECT_NAME(m.object_id) AS object_name
FROM sys.sql_modules m
WHERE m.definition LIKE '%RECOMPILE%';

6. Quantify the CPU cost. Checks 4 and 5 tell you whether the compilations are actually hurting: elevated runnable_tasks_count, SOS_SCHEDULER_YIELD as a dominant wait, or signal wait time above ~20% of total wait time confirm scheduler pressure. Compilations without CPU pressure are a hygiene issue, not an incident.

Metrics and signals to monitor

Fixes

Parameterize the workload (the real fix for ad-hoc pollution)

The durable fix is application-side: send parameterized queries (sp_executesql with parameters, prepared statements, or stored procedures) instead of concatenating literal values into SQL text. This collapses thousands of query texts into one cached, reusable plan. Nothing server-side fully substitutes for this.

optimize for ad hoc workloads (stop the memory bleed)

This server-level option stores only a small compiled plan stub on the first execution of an ad-hoc statement and caches the full plan only if the same text executes a second time. It cuts the memory wasted on single-use plans, which relieves eviction pressure on everything else in the cache:

-- Enable stub caching for single-use ad-hoc plans
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

Two caveats. First, despite the name, it does not make queries faster; it reduces cache bloat. Second, it does not stop the compilations themselves, since the first execution still compiles. Treat it as a mitigation that buys time and memory while the application is fixed, not as the fix. After enabling it, the existing single-use plans remain until evicted; some teams clear the cache once during a maintenance window to reclaim the memory immediately. DBCC FREEPROCCACHE invalidates every plan on the instance and forces a full recompile storm, so do not run it during load.

Forced parameterization (blunt instrument, use with caveats)

The PARAMETERIZATION FORCED database option makes the engine parameterize most ad-hoc statements automatically, so literal-value queries share plans:

-- Per-database, takes effect for new compilations
ALTER DATABASE [YourDb] SET PARAMETERIZATION FORCED;

This can cut the compilation rate sharply. The tradeoff is real: now one plan serves all parameter values, which is exactly the setup for parameter sniffing. A plan compiled for an atypical value can be catastrophically wrong for typical ones. If you enable it, watch Query Store for plan regressions afterward, and be ready to revert or use plan guides for the exceptions. Test it on the workload before enabling in production.

Fix memory pressure (when eviction is the cause)

If PLE is falling and the plan cache clerk is being squeezed, the compilation problem is downstream of a memory problem. Check max server memory configuration (too high starves the OS and triggers reclaim; too low shrinks everything), look for one query hoarding a large memory grant in sys.dm_exec_query_memory_grants, and check whether the OS itself is under pressure via sys.dm_os_sys_memory. Fixing the memory side stops the evictions, and the recompilations stop with them.

Remove or scope RECOMPILE hints

If specific hot statements carry OPTION (RECOMPILE) and execute thousands of times per second, you are paying full compile cost per execution by design. Alternatives: OPTIMIZE FOR hints, a Query Store forced plan for the known-good shape, or rewriting the query so one plan serves the parameter distribution. Keep RECOMPILE only where plan quality genuinely requires per-execution compilation and the execution rate is low.

Prevention

• Track the ratio, not the raw counter. Alert on Compilations/sec as a percentage of Batch Requests/sec with delta sampling. Raw compile counts are meaningless without workload context.
• Baseline by time of day. Deployments and statistics jobs legitimately spike compiles. A baseline lets you distinguish a deployment spike from a pollution trend.
• Watch single-use plan bloat as a leading indicator. Ad-hoc pollution grows for weeks before the CPU cost becomes an incident. Trend the single-use count and wasted MB.
• Trend PLE per NUMA node. Memory pressure arrives before eviction-driven compilation spikes. The instance-wide average hides single-node pressure.
• Enable Query Store. It gives you historical plan and compile behavior per query, so after a forced parameterization change or hint removal you can verify regressions instead of hoping.
• Review RECOMPILE usage in code review. It should be a deliberate, documented choice per statement, not a copy-pasted default.

How Netdata helps

• Netdata collects SQL Compilations/sec, Re-Compilations/sec, and Batch Requests/sec from sys.dm_os_performance_counters with delta sampling handled for you, so the cumulative-counter trap (reading cntr_value directly) does not produce garbage ratios.
• The compile-to-batch ratio can be charted and alerted on directly, which is the correct alert shape for this symptom rather than either counter alone.
• Per-second CPU, scheduler, and PLE collection on the same dashboard lets you confirm in one view whether a compilation spike is ad-hoc pollution (ratio up, PLE flat) or eviction-driven (ratio up, PLE falling).
• Correlating compile rate with host CPU and memory over days exposes the slow pollution build-up that per-incident DMV queries miss.
• Anomaly detection on the ratio flags deviations from your workload’s normal pattern, catching the change when a new application version starts emitting non-parameterized SQL.

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

Related guides

• See the Microsoft SQL Server operations hub for the broader signal taxonomy, composite failure patterns (including the memory pressure spiral that drives plan eviction), and the monitoring maturity model this article plugs into.