SQL Server high recompilations: stale statistics and schema changes churning plans

CPU is climbing, SOS_SCHEDULER_YIELD is creeping up, batch requests look normal, and your top waits are not obviously I/O or lock related. Check the SQL Statistics counters: if SQL Re-Compilations/sec is running well above 10% of SQL Compilations/sec, you have a plan stability problem, not a plan cache efficiency problem.

Recompilations are not the same as first-time compilations. First-time compilations (or recompilations forced by plan cache eviction under memory pressure) belong in SQL Server high compilations per second. Recompilations mean SQL Server had a cached plan, decided it could no longer trust it, and spent CPU rebuilding it. Each recompile is CPU work, and if the recompiling statement sits inside a multi-statement batch or stored procedure, the cost cascades across dependent statements.

The common triggers are stable and well-known: statistics auto-update crossing the recompilation threshold, schema or index changes, SET option changes per session, explicit OPTION (RECOMPILE) hints, temp table changes, and a handful of rarer cases. The fix is to find which statements are recompiling, classify why with the SQL:StmtRecompile event subclass, and address the underlying cause.

What this means

Since SQL Server 2005, automatic recompiles are statement-level, not batch-level. The optimizer invalidates only the specific statement whose inputs changed, not the whole procedure. That limits blast radius, but it means the symptom is often a long-lived stored procedure whose individual statements keep getting rebuilt on every call.

The four triggers you will see most often in production:

1. Statistics changed (EventSubClass 2). Auto-update statistics fired because a table crossed its recompilation threshold (RT). For permanent tables the threshold is 500 modifications if cardinality was 500 or less at statistics evaluation time, and 500 + 20% of cardinality above that. The recompile itself is usually correct and healthy. The problem is the rate.
2. Schema changed (EventSubClass 1). Indexes were added, dropped, or disabled. DDL during load will recompile every cached plan that touches the affected object.
3. Set option changed (EventSubClass 4). The application is sending connections with different SET ANSI_NULLS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL, LANGUAGE, DATEFORMAT, or similar options. SQL Server treats these as plan-invalidating because they change semantics.
4. Option (recompile) requested (EventSubClass 11). Self-inflicted. Someone added OPTION (RECOMPILE) to a hot statement.

Temp tables get their own subclass (Temp table changed, EventSubClass 5) because temp tables have a lower RT than permanent tables: 6 modifications for tables of 6 rows or fewer, 500 modifications for tables up to 500 rows, and 500 + 20% above that. Heavy temp table use inside a loop is a classic source of steady recompile pressure.

Common causes

Quick checks

All read-only.

-- Check recompile and compilation rates (cumulative counters - compute deltas)
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%';

-- Top statements by recompile count, using plan_generation_num
SELECT TOP (20)
    qs.sql_handle,
    qs.plan_handle,
    qs.plan_generation_num,
    qs.execution_count,
    qs.last_execution_time,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        (CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.plan_generation_num > 1
ORDER BY qs.plan_generation_num DESC;

-- Confirm CPU pressure is real and not just OS utilization
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    CAST(100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('SOS_SCHEDULER_YIELD', 'RESOURCE_SEMAPHORE_QUERY_COMPILE', 'CXPACKET', 'CXCONSUMER')
  AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- Verify plan cache is not simultaneously being evicted by memory pressure
SELECT
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS plan_cache_mb,
    SUM(CASE WHEN usecounts = 1 AND objtype = 'Adhoc' THEN 1 ELSE 0 END) AS single_use_plans,
    COUNT(*) AS total_plans
FROM sys.dm_exec_cached_plans;

-- SQL Server build (relevant for the SQL 2019 Reduce Recompilations / MARS issue)
SELECT @@VERSION AS sql_version;

How to diagnose it

1. Confirm the rate is actually abnormal. Take two samples of the SQL Re-Compilations/sec and SQL Compilations/sec counters and compute the rate. The threshold to investigate is recompiles greater than 10% of compilations sustained, with corresponding CPU pressure. Also compare SQL Compilations/sec against Batch Requests/sec; if compilations themselves are more than 10-20% of batch requests, you have a plan cache problem too and should read SQL Server high compilations per second first.

2. Identify which statements recompile. Use sys.dm_exec_query_stats.plan_generation_num. Values greater than 1 mean the plan has been regenerated since it was first cached. Sort by plan_generation_num DESC to find the worst offenders, then map them back to object names via sys.dm_exec_sql_text. Note: plan_generation_num only reflects currently cached plans. If memory pressure has evicted the plan, you need Query Store for persistent history.

3. Classify the cause via SQL:StmtRecompile. Do not use the deprecated SP:Recompile event class; Microsoft’s documented guidance is to trace SQL:StmtRecompile. Capture it through Extended Events and read the EventSubClass column: 1=Schema changed, 2=Statistics changed, 3=Deferred compile, 4=Set option changed, 5=Temp table changed, 6=Remote rowset changed, 7=For Browse permissions changed, 8=Query notification environment changed, 9=Partition view changed, 10=Cursor options changed, 11=Option (recompile) requested.

4. Correlate with deployment windows and auto-stats events. If EventSubClass 1 dominates, line up the recompile spikes with DDL change records. If EventSubClass 2 dominates, look for SQL Server has encountered N occurrence(s) of autostats entries in the error log around the same time, and identify which table’s RT is being tripped.

5. Rule out the SQL 2019 Reduce Recompilations bug. On SQL Server 2019 RTM through CU4 with Multiple Active Result Sets (MARS) connections enabled, the Reduce Recompilations feature (on by default) could cause blocking where the blocker and blockee shared the same session ID, plus excessive temp table recompiles. This was fixed in CU5 (KB4555232). If your build is below CU5 and you use MARS, treat that as the prime suspect before deeper tuning.

flowchart TD
    A[Recompiles/sec greater than 10% of Compilations/sec] --> B{Top EventSubClass?}
    B -->|2 Statistics changed| C[Auto-update stats crossing RT]
    B -->|1 Schema changed| D[DDL during load]
    B -->|4 Set option changed| E[Session SET option drift]
    B -->|11 Option recompile| F[Self-inflicted RECOMPILE hint]
    B -->|5 Temp table changed| G[Temp table churn in loops]
    B -->|Other| H[Deferred / remote / cursor / partition]
    C --> I[Update stats off-peak, batch recompile]
    D --> J[Move DDL to maintenance window]
    E --> K[Standardize SET options in app]
    F --> L[Remove or scope the hint]
    G --> M[KEEP PLAN or refactor temp table use]
    H --> N[Investigate specific statement]

Metrics and signals to monitor

Fixes

Stale statistics driving auto-update recompiles

This is the most common cause and usually the healthiest one: the optimizer is doing the right thing by recompiling when stats change. The fix is to take control of when the update happens.

• Update statistics manually during a maintenance window so the auto-update threshold is not tripped during peak load.
• Consider AUTO_UPDATE_STATISTICS_ASYNC ON for the database so queries do not wait for the stats update; the current plan runs, and the next compilation picks up the new stats.
• For specific volatile tables, evaluate whether the cost of the recompile is genuinely lower than the cost of a bad plan. If the recompile is the lesser evil, leave it alone.

Schema and index changes during load

Every DDL change against an object invalidates cached plans that reference it. Moving CREATE INDEX, ALTER INDEX ... REBUILD, statistics rebuilds, and schema migrations to a maintenance window eliminates the cascading recompiles. If you must do online operations, expect the recompile cost and schedule for it.

Per-session SET option drift

One of the most underdiagnosed causes. Two connections from the same application that differ in ANSI_NULL_DFLT_ON, QUOTED_IDENTIFIER, LANGUAGE, or DATEFORMAT can produce different cached plans, or force recompiles when the second connection reuses a plan compiled under different options. Standardize SET options at connection time in the data access layer. Verify with sys.dm_exec_sessions joined to your application’s connection profile.

Overused OPTION (RECOMPILE)

OPTION (RECOMPILE) generates a new plan and discards it after execution; it does not replace a cached plan. It is the right tool for genuinely parameter-sensitive queries where cached plans regress badly. It is the wrong tool as a blanket fix for “this stored procedure is sometimes slow.” Audit Query Store and sys.dm_exec_query_stats for the hint, and remove it from statements that do not need per-invocation recompilation. Reserve it for queries where parameter distribution varies by orders of magnitude.

Temp table churn

Inside loops and frequently called procedures, temp tables recompile at their own (lower) RT. Options:

• Use KEEP PLAN on the statement to raise the temp table RT to permanent-table thresholds.
• Replace the temp table with a table variable where the cardinality really is small and fixed. Table variables have no statistics and no RT, so they do not trigger statistics-based recompiles, but the optimizer estimates 1 row. On SQL Server 2019+, table variable deferred compilation may change this behavior, so verify the estimate you actually get. The tradeoff is only acceptable for genuinely small sets.
• Review temp table caching conditions. Certain patterns, including data definition language changes to the temp table after creation, can prevent the internal temp table caching optimization and force recreation on each execution.
• On SQL Server 2019 CU5 and later, the Reduce Recompilations feature does a better job of caching temp tables whose schema has not changed.

SQL 2019 specific: Reduce Recompilations and MARS

If you are on SQL Server 2019 RTM through CU4 and use MARS connections, apply CU5 or later. KB4555232 fixed blocking that occurred with the same session ID on both sides of the block.

Do not reach for KEEPFIXED PLAN reflexively

KEEPFIXED PLAN disables recompiles due to statistics changes. The plan still recompiles on schema changes or sp_recompile. This is useful for a narrow class of stable-cardinality queries, but it can mask a real problem: the plan stays in cache long after the data distribution has shifted, and you trade a recompile problem for a parameter-sniffing problem. Use it deliberately, not as a default.

Prevention

• Run a deployment-aware baseline. Capture SQL Re-Compilations/sec and the ratio to compilations before and after each release. Most recompile incidents track back to a specific change.
• Standardize SET options in the data access layer. Do not let individual developers or reports set ANSI_NULLS differently.
• Pre-size and pre-update statistics. Update stats in maintenance windows for known volatile tables rather than letting auto-update fire during peak.
• Audit OPTION (RECOMPILE) in code review. Treat it like a NOLOCK: sometimes correct, often a band-aid.
• Apply SQL Server cumulative updates. The SQL 2019 Reduce Recompilations bug is a clear example of a version-specific regression that bit operators who skipped CUs.
• Track plan_generation_num over time. Persist snapshots of the top recompiling statements so you can detect drift before it becomes an incident.
• Enable Query Store. SQL 2016 and later, on by default for new databases in SQL 2022. It gives you plan history across restarts, which is essential for post-incident forensics when cached plans have been evicted.

How Netdata helps

• Per-second SQL Compilations/sec and SQL Re-Compilations/sec with deltas already computed, so you do not have to maintain a delta sampler yourself. The ratio is visible as a derived metric.
• Correlation with Batch Requests/sec, CPU utilization, SOS_SCHEDULER_YIELD waits, and PLE in a single view. Recompile problems almost always announce themselves as a coordinated move across these signals, and per-second collection catches the cascade forming.
• ML-based anomaly detection on the recompilation ratio. Steady-state workloads have a normal ratio. A drift in that ratio is flagged without you having to set static thresholds that ignore workload seasonality.
• Deployment window correlation. If you ship DDL during a release, the resulting recompile spike lines up with deployment annotations, making root cause attribution fast.
• Plan cache and memory pressure context. Recompiles caused by memory pressure evicting plans look different from true plan stability problems. Per-second PLE, lazy writer activity, and plan cache size let you distinguish the two.

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

Related guides

• SQL Server blocking chains: finding the head blocker before workers run out
• SQL Server buffer cache hit ratio low: when the working set no longer fits in memory
• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server Error 1205: transaction was deadlocked and chosen as the deadlock victim
• SQL Server Error 701: there is insufficient system memory to run this query
• SQL Server Error 823 and 824: I/O and logical consistency errors
• SQL Server Error 825: read-retry succeeded and the disk is failing
• SQL Server Error 9002: the transaction log for the database is full
• SQL Server high compilations per second: plan cache pollution and CPU burn
• How Microsoft SQL Server actually works in production: a mental model for operators