Understanding SQL Recompilations

What Are Recompilations?

The SQL Recompilations Per Second metric tracks how many times SQL Server recompiles an execution plan for queries every second. Unlike compilations, which occur the first time a query is processed, recompilations happen when SQL Server determines that an existing execution plan is no longer valid and needs to be rebuilt. Frequent recompilations can introduce significant overhead and degrade performance.

What to Look For in the Chart

  • Low Recompilation Rates: A low, steady rate of SQL Recompilations Per Second indicates that SQL Server is efficiently using cached execution plans. Ideally, recompilations should occur only when necessary, such as when query parameters change significantly.
  • Spikes in Recompilations: Sudden spikes in recompilations suggest that the database may be encountering frequent changes in data or query plans, resulting in high system overhead as execution plans are recreated frequently.
  • Correlation with SQL Compilations and Batch Requests Per Second: Comparing SQL Recompilations Per Second to SQL Compilations Per Second can reveal potential inefficiencies. High recompilations alongside high compilations and lower-than-expected Batch Requests Per Second may indicate that queries are not reusing execution plans as they should. Monitoring these metrics together can provide insight into overall query performance and execution plan efficiency.

Why It Matters

High recompilation rates consume valuable CPU resources and can slow down query execution, particularly under heavy workloads. Recompilations often result from non-parameterized queries or changes in statistics or schema, which force SQL Server to generate new execution plans. Monitoring and addressing high recompilation rates can significantly improve SQL Server’s performance by reducing CPU load and optimizing query execution.

Related Pages:

Getting Help from the Stedman Solutions Team

We are ready to help. The team at Stedman Solutions is here to help with your SQL Server needs. Get help today by contacting Stedman Solutions through the free 30 minute consultation form.