Recompile execution plans

An ad hoc execution plan is inserted and isn’t referenced again before memory pressure

exists. Since ad hoc plans are initialized with a current cost of zero, when the SQL Server

Database Engine examines the execution plan, it will see the zero current cost and

remove the plan from the plan cache. The ad hoc execution plan remains in the plan

cache with a zero current cost when memory pressure doesn’t exist.

To manually remove a single plan or all plans from the cache, use

DBCC FREEPROCCACHE.

DBCC FREESYSTEMCACHE

can also be used to clear any cache, including plan cache. Starting

with SQL Server 2016 (13.x), the

to clear the procedure (plan) cache for the database in scope.

A change in some configuration settings via

sp_configure

and

reconfigure

will also cause plans

to be removed from plan cache. You can find the list of these configuration settings in the

Remarks section of the

DBCC FREEPROCCACHE

article. A configuration change like this will log

the following informational message in the error log:

Certain changes in a database can cause an execution plan to be either inefficient or invalid,

based on the new state of the database. SQL Server detects the changes that invalidate an

execution plan and marks the plan as not valid. A new plan must then be recompiled for the

next connection that executes the query. The conditions that invalidate a plan include the

following:

Changes made to a table or view referenced by the query (

and

).

Changes made to a single procedure, which would drop all plans for that procedure from

the cache (

).

Changes to any indexes used by the execution plan.

Updates on statistics used by the execution plan, generated either explicitly from a

statement, such as

, or generated automatically.

Dropping an index used by the execution plan.

An explicit call to.

Large numbers of changes to keys (generated by

or

statements from

other users that modify a table referenced by the query).

For tables with triggers, if the number of rows in the inserted or deleted tables grows

significantly.

Executing a stored procedure using the

option.

Most recompilations are required either for statement correctness or to obtain potentially

faster query execution plans.

In SQL Server versions prior to 2005, whenever a statement within a batch causes

recompilation, the entire batch, whether submitted through a stored procedure, trigger, ad hoc

batch, or prepared statement, was recompiled. Starting with SQL Server 2005 (9.x), only the

statement inside the batch that triggers recompilation is recompiled. Also, there are additional

types of recompilations in SQL Server 2005 (9.x) and later because of its expanded feature set.

Statement-level recompilation benefits performance because, in most cases, a small number of

statements causes recompilations and their associated penalties, in terms of CPU time and

locks. These penalties are therefore avoided for the other statements in the batch that don’t

have to be recompiled.

The

extended event (XEvent) reports statement-level recompilations.

This XEvent occurs when a statement-level recompilation is required by any kind of batch. This

includes stored procedures, triggers, ad hoc batches and queries. Batches can be submitted

through several interfaces, including

, dynamic SQL, Prepare methods or Execute

methods.

The

column of

XEvent contains an integer code that

indicates the reason for the recompilation. The following table contains the possible reasons:

Schema changed

Statistics changed

Deferred compile

SET option changed

Temporary table changed

Remote rowset changed

permission changed

Query notification environment changed

Partitioned view changed

Cursor options changed

SP:Recompile

here

requested

Parameterized plan flushed

Plan affecting database version changed

Query Store plan forcing policy changed

Query Store plan forcing failed

Query Store missing the plan

７

Note

In SQL Server versions where XEvents aren’t available, then the SQL Server Profiler

trace event can be used for the same purpose of reporting statement-level

recompilations.

The trace event

also reports statement-level recompilations, and this

trace event can also be used to track and debug recompilations.

Whereas

generates only for stored procedures and triggers,

generates for stored procedures, triggers, ad hoc batches, batches that

are executed by using

, prepared queries, and dynamic SQL. The

EventSubClass

column of

and

contains an integer code

that indicates the reason for the recompilation. The codes are described.

７

Note

When the

database option is set to

, queries are recompiled

when they target tables or indexed views whose statistics have been updated or whose

cardinalities have changed significantly since the last execution.

This behavior applies to standard user-defined tables, temporary tables, and the inserted

and deleted tables created by DML triggers. If query performance is affected by excessive

recompilations, consider changing this setting to. When the

database option is set to

, no recompilations occur based on statistics or cardinality

changes, with the exception of the inserted and deleted tables that are created by DML

triggers. Because these tables are created in

, the recompilation of

queries that access them depends on the setting of

in.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

has encountered %d occurrence(s) of cachestore flush for the '%s'
cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

ALTER TABLE

ALTER VIEW

ALTER PROCEDURE

UPDATE STATISTICS

sp_recompile

INSERT

DELETE

WITH RECOMPILE

sql_statement_recompile

sp_executesql

recompile_cause

sql_statement_recompile

FOR BROWSE

OPTION (RECOMPILE)

SQL:StmtRecompile

SP:Recompile

SQL:StmtRecompile

sp_executesql

SP:Recompile

SQL:StmtRecompile

AUTO_UPDATE_STATISTICS

ON

OFF

AUTO_UPDATE_STATISTICS

OFF

INSTEAD OF

tempdb

AUTO_UPDATE_STATISTICS

tempdb