Database Reference
In-Depth Information
to go through a recompile. Knowing the different conditions that result in recompilation helps you evaluate the
cause of a recompilation and determine how to avoid recompiling when it isn't necessary. Statement recompilation
occurs for the following reasons:
•
The schema of regular tables, temporary tables, or views referred to in the stored procedure
statement have changed. Schema changes include changes to the metadata of the table or the
indexes on the table.
•
Bindings (such as defaults) to the columns of regular or temporary tables have changed.
•
Statistics on the table indexes or columns have changed, either automatically or manually.
•
An object did not exist when the stored procedure was compiled, but it was created during
execution. This is called
deferred object resolution,
which is the cause of the preceding
recompilation.
•
SET
options have changed.
The execution plan was aged and deallocated.
•
•
An explicit call was made to the
sp_recompile
system stored procedure.
RECOMPILE
hint.
You can see these changes in Extended Events. The cause is indicated by the
recompile_cause
data column value
for the
sql_statement_recompile
event, as shown in Table
17-2
.
•
There was an explicit use of the
Table 17-2.
Recompile Cause
Data Column Reflecting Causes of Recompilation
Description
Schema or bindings to regular table or view changed
Statistics changed
Object did not exist in the stored procedure plan but was created during execution
SET
options changed
Schema or bindings to temporary table changed
Schema or bindings of remote rowset changed
FOR BROWSE
permissions changed
Query notification environment changed
MPI view changed
Cursor options changed
WITH RECOMPILE
option invoked
Let's look at some of the reasons listed in Table
17-2
for recompilation in more detail and discuss what you can
do to avoid them.
