Common causes for query execution plan recompilation

When a query plan is recompiled, it means the query optimizer generates a new execution plan for a query. Recompilations happen when the current plan is deemed outdated or incorrect due to various changes in the database environment. The reasons for recompilation can be found using the sys.dm_exec_query_stats, sys.dm_exec_cached_plans, and sys.dm_exec_plan_attributes system views, or in the actual execution plan.

Below are the common causes for recompilation

Ø  Schema Changes: If the schema of a referenced object (such as a table or view) changes, SQL Server will recompile the query to generate a new plan.

Ø  Statistics Changes: If the statistics for a table or index are updated (either automatically or manually), SQL Server may decide to recompile a query that depends on those statistics.

Ø  Data Changes: When a significant portion of the data in a table changes, SQL Server may recompile the query because the original plan might no longer be efficient.

Ø  Parameter Sniffing: SQL Server creates a query plan based on the initial parameter values used in a stored procedure. If subsequent executions with different parameter values perform poorly, the query might be recompiled.

Ø  Plan Cache Pressure: SQL Server uses memory to store execution plans. When there is memory pressure or too many cached plans, SQL Server might evict old plans and force recompilation for new queries.

Ø  SET Options Changes: SQL Server will recompile a query if certain SET options (like SET ANSI_NULLS, SET ARITHABORT, etc.) change between executions. These options affect how SQL Server optimizes and executes queries.

Ø  Plan Guides: Changes to a plan guide can trigger a recompilation.

Ø  Deferred Compilation: In some cases, SQL Server may delay the compilation of a query until certain run-time conditions are met. Once those conditions are met, SQL Server recompiles the query.

Ø  Table Structure Dependency: Queries that depend on temporary tables, table variables, or table-valued parameters might recompile when the table structure changes or is accessed in a way that the optimizer did not expect.

Below is the query to get the list of plan recompilation

SELECT map_key,        map_value FROM   sys.dm_xe_map_values AS dxmv WHERE  dxmv.NAME = N'statement_recompile_cause'

 

map_key	map_value
1	Schema changed
2	Statistics changed
3	Deferred compile
4	Set option change
5	Temp table changed
6	Remote row set changed
7	For browse permissions changed
8	Query notification environment changed
9	Partition View changed
10	Cursor options changed
11	Option (recompile) requested
12	Parameterized plan flushed
13	Test plan linearization
14	Plan affecting database version changed
15	Query Store plan forcing policy changed
16	Query Store plan forcing failed
17	Query Store missing the plan
18	Interleaved execution required recompilation
19	Not a recompile
20	Multi-plan statement required compilation of alternative query plan
21	Query Store hints changed
22	Query Store hints application failed
23	Query Store recompiling to capture cursor query
24	Recompiling to clean up the multiplan dispatcher plan

Below query will provide the information about reason for Statement Recompilation

SELECT st.text AS QueryText,        qs.execution_count,        qs.plan_generation_num,        qs.total_worker_time,        qs.total_logical_reads,        qs.statement_start_offset,        qs.statement_end_offset,        pa.attribute,        pa.value FROM   sys.dm_exec_query_stats AS qs        CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS st        CROSS apply sys.Dm_exec_plan_attributes(qs.plan_handle) AS pa WHERE  pa.is_cache_key = 1        AND pa.attribute = 'StatementRecompileReason';