Excessive Plan Compilation
Category: CPU · Also known as: High Number of SQL compilations/sec
SQL Server is compiling a lot of new plans - CPU spent building plans instead of running queries, usually from unparameterized ad hoc SQL.
What this finding means
Section titled “What this finding means”SQL Server compiled new plans at a much higher rate than normal. Compilation is CPU spent deciding how to run a query rather than running it, and at high rates it becomes a meaningful share of processor time as well as a source of plan cache churn.
Why it matters
Section titled “Why it matters”A compile storm wastes CPU on work that should have been done once and reused thousands of times. It also inflates the plan cache with single-use plans, which pushes out the plans that do get reused, creating a second round of compilation. The cause is nearly always a fixable application pattern rather than a database limit.
How WISdom detects it
Section titled “How WISdom detects it”The SQL Statistics SQL 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 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 compiles per second against the History max. The number that gives it meaning is the ratio to batch requests per second, available on the Performance Metrics screen: healthy instances reuse plans, so compiles should be a small fraction of batches. A ratio approaching one means essentially nothing is being reused.
What normally causes it
Section titled “What normally causes it”- Unparameterized ad hoc SQL, where literal values are concatenated into the statement so every distinct value produces a distinct plan.
- A plan cache under memory pressure evicting plans that are then rebuilt.
- Frequent DDL or temp-table churn invalidating cached plans.
- An ORM emitting structurally different SQL for what is logically the same query.
What this finding is not
Section titled “What this finding is not”- Not the same as recompilation. Compilation builds a plan for something not in cache; recompilation discards a valid cached plan and rebuilds it. Different counters, different causes.
- Not always bad. A spike right after a service restart or a failover is expected while the cache warms.
- Not fixed by adding CPU. More processor makes the waste faster, not smaller.
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. Parameterize the statements
Section titled “1. Parameterize the statements”Unparameterized ad hoc SQL builds a new plan for every value. Parameterize at the app tier and the compilation rate collapses.
2. Turn on optimize for ad hoc workloads
Section titled “2. Turn on optimize for ad hoc workloads”Stops single-use plans filling the cache. Instance-level, low risk, and useful when the application cannot be changed.
3. Force parameterization on the database
Section titled “3. Force parameterization on the database”Blunt but effective when the code is untouchable. Test it: it can produce worse plans where the data is skewed.