Excessive Plan Recompilation
Category: CPU · Also known as: High Number of SQL re-compilations/sec
Existing plans are being thrown away and rebuilt repeatedly - wasted CPU, usually from stale statistics, RECOMPILE hints, or schema changes.
What this finding means
Section titled “What this finding means”Cached plans that were still valid were discarded and rebuilt at a much higher rate than normal. Unlike a first compilation, every recompile is work that has already been done once, so it is pure overhead paid repeatedly for the same statement.
Why it matters
Section titled “Why it matters”Recompilation is more insidious than compilation because the statements involved usually look fine in isolation and the cost is spread thinly across every call. It commonly traces back to a correctness fix from years earlier, a RECOMPILE hint added to work around a bad plan, which is still in place long after the underlying problem was solved.
How WISdom detects it
Section titled “How WISdom detects it”The SQL Statistics SQL Re-Compilations/sec performance counter is read from sys.dm_os_performance_counters each minute and compared to the four-week maximum for this minute-of-day. This rule is always evaluated and returns a value every minute.
| Collected from | Performance counter SQL Statistics: SQL Re-Compilations/sec (sys.dm_os_performance_counters). |
| Compared with | Max value during 15 minutes before and 15 minutes after of the same minute in the past 4 weeks |
| Related screen | Performance Metrics |
How it is scored
Section titled “How it is scored”This rule is always evaluated, so it reports a value every minute whether or not it is elevated. It contributes an incident score of 2 when the value exceeds the four-week maximum for this minute-of-day.
How to read the numbers
Section titled “How to read the numbers”Current recompiles per second against the History max. Read it alongside the compilation counter: high recompiles with normal compiles points at statistics or hints, while both elevated together usually points at cache pressure or schema churn affecting everything.
What normally causes it
Section titled “What normally causes it”- Statistics crossing the modification threshold and invalidating plans, the most common cause by a wide margin.
- An explicit RECOMPILE hint at statement or procedure level, often left over from an old fix.
- Schema changes, temp table creation, or index changes inside the workload window.
- SET option changes between calls, which produce a different plan cache key.
What this finding is not
Section titled “What this finding is not”- Not the same as compilation. Check both counters before choosing a fix; they point in different directions.
- Not always removable. Some statements genuinely need RECOMPILE because a single plan cannot serve wildly different parameter values. The question is whether this one still does.
- Not a statistics problem by default. Updating statistics is the first thing to try, not the conclusion. If recompiles persist afterwards, look at hints and SET options.
What are some possible resolutions?
Section titled “What are some possible resolutions?”The options below are ranked. Option 1 fixes the cause where it lives and costs nothing; option 3 spends money or escalates, so try it last.
1. Update statistics on the hot objects
Section titled “1. Update statistics on the hot objects”Stale statistics are the most common recompile trigger. Update them on the objects this workload actually touches.
2. Remove unnecessary RECOMPILE hints
Section titled “2. Remove unnecessary RECOMPILE hints”A RECOMPILE hint left in from an old fix makes every single call pay for compilation. Check whether it is still earning its place.
3. Move schema churn out of the window
Section titled “3. Move schema churn out of the window”DDL, temp-table churn, and SET option changes all force recompiles. Shift that work out of peak hours.