A stored procedure can run the same text yet receive a new plan on the next call. Understanding why queries recompile means finding the event and its cause before treating every compile as waste.
Separate Compilation from Recompilation
SQL Server compiles a statement when it needs a plan. A recompile happens when a cached statement plan cannot be reused or the engine deliberately chooses a new one. Recompilation has CPU cost, but it can also prevent a stale plan from making a much larger mistake. Count it in context of frequency and query cost.
I do not begin by disabling features that trigger recompiles. I ask which statement recompiles, how frequently, and whether users feel the cost. A procedure with one statement that recompiles after a data load can be healthy. A tiny statement compiling on every call can waste CPU. The pattern matters more than the word recompile in a dashboard.
Know the Common Reasons Why Queries Recompile
Schema changes invalidate plans that depend on changed objects. Statistics updates can change estimates and trigger new plans. Different SET options can create separate plan-cache entries. Temporary table changes and deferred compilation can also bring a statement back to the optimizer. OPTION (RECOMPILE) explicitly asks for a fresh plan on each execution.
I compare the cause with the change calendar. A burst after a deployment is different from constant recompilation in a hot procedure. Do not assume a statistic update caused a regression just because both occurred near each other. Capture the event cause and query text, then inspect the resulting plan. Timing is a clue, not a verdict.
Check Cached Plan Counts
sys.dm_exec_query_stats includes plan-generation and execution information for cached statements. Its rows disappear when plans leave the cache or the instance restarts. It can point to candidates but cannot provide complete history. Filter carefully and avoid presenting a cache snapshot as a lifetime count.
The query lists statements with generation counts in the current cache. I use it to decide which text merits a targeted Extended Events capture. Query Store can provide a longer plan history when enabled. The cache alone cannot explain the cause code for each recompile.
SELECT TOP (50)
qs.plan_generation_num,
qs.execution_count,
qs.total_worker_time,
t.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.plan_generation_num DESC;Capture Why Queries Recompile with XE
The sqlserver.sql_statement_recompile event reports statement-level recompilations. Its recompile_cause field identifies the reason. Use a targeted event file when you need durable evidence and a narrow predicate when the instance is busy. Include database and SQL text actions only as needed, since text can contain sensitive literals.
I start with a short test session on a representative environment. The example creates an event file target at a placeholder Windows path. Make the directory writable by the SQL Server service account and set retention before enabling it broadly. Inspect actual event volume after a known workload run.
CREATE EVENT SESSION [RecompileWatch] ON SERVER
ADD EVENT sqlserver.sql_statement_recompile
(
ACTION(sqlserver.sql_text, sqlserver.database_name)
)
ADD TARGET package0.event_file
(
SET filename = N'D:\SQLXE\RecompileWatch.xel',
max_file_size = 64,
max_rollover_files = 4
);
Read Captured Events
sys.fn_xe_file_target_read_file reads the event file. XML event data contains timestamp, statement information, and cause. Start with the raw XML for a few events so you understand its shape on your version. Then extract specific fields into an operational report. Keep the query limited during active troubleshooting.
I compare the cause with the statement and time. A cause named statistics changed can be expected after maintenance. A repeated temp-table cause in a high-frequency path deserves a closer look. One event does not show whether the new plan was better or worse. Pair it with Query Store or actual plan evidence.
SELECT TOP (100)
CAST(event_data AS xml) AS event_xml
FROM sys.fn_xe_file_target_read_file
(N'D:\SQLXE\RecompileWatch*.xel', NULL, NULL, NULL)
ORDER BY file_name, file_offset;Investigate SET Options
Different connection settings can produce separate plans for the same apparent query text. Compare the application’s driver and SSMS session before saying the server behaves inconsistently. An ad hoc query from SSMS can compile under settings unlike the application’s connection. This is especially relevant when a test plan looks good but the deployed path does not.
I reproduce using the real client or a connection configured the same way. I record SET options alongside the plan. Changing global defaults to make SSMS match is rarely the first fix. The question is why the application uses a particular option and whether it is supported for the operation. A plan cache full of near-duplicates can point to inconsistent client setup.
Review Temporary Tables
Temporary table cardinality and schema can change during a procedure. SQL Server can defer compilation or recompile statements that depend on the table to use current information. That can be beneficial when row counts vary widely. If the statement recompiles excessively, examine temp table creation, indexes, statistics, and query shape before forcing reuse.
I have seen a temp table blamed because its name appeared in the cause. The plan after recompile was actually better for the current batch size. Measure compile cost against execution cost. A tiny compile saving is a poor trade for a plan that reads far more rows. Keep the real workload in the test.
Use Hints Only with a Reason
OPTION (RECOMPILE) can help a parameter-sensitive statement use current values, but it trades plan reuse for compile work. It can be appropriate for a costly statement run infrequently and poor for a tiny statement run constantly. Procedure-wide WITH RECOMPILE has a broader effect than a statement hint. Choose the narrowest tested scope.
I add a hint after confirming the cause and comparing representative parameters. A hint should carry a comment or change record explaining the problem it solved. Revisit it after upgrades and data growth. A forever hint with no owner becomes the next plan mystery.
Explain Why Queries Recompile with Evidence
Record the statement, recompile cause, frequency during a measured window, plan effect, and user impact. If a schema deployment caused a one-time burst, document it and move on. If a hot path recompiles continuously, test a focused change and measure CPU and duration. Do not label every recompile a defect.
Which statement is consuming meaningful time in compilation rather than useful execution? Find that one first. The XE event tells you why queries recompile. The business decision is whether a change improves the workload without locking it into a worse plan later.
Related reading on this blog: sys.dm_xe_map_values: Reasons for Statement Recompilation and Understanding WITH RECOMPILE in Stored Procedures.
A recompile is not automatically wasted work, it is a plan refresh whose cost needs context.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
