Database Reference
In-Depth Information
Figure 17-8.
Effect of statistics change on the stored procedure recompilation
In Figure
17-8
, you can see that to execute the
SELECT
statement during the second execution of the stored
procedure, a recompilation was required. From the value of
recompile_cause (Statistics Changed)
, you can
understand that the recompilation was because of the statistics change. As part of creating the new plan, the statistics
are automatically updated, as indicated by the
Auto Stats
event, which occurred after the call for a recompile of the
statement. You can also verify the automatic update of the statistics using the
DBCC SHOW_STATISTICS
statement, as
explained in Chapter 12.
Deferred Object Resolution
Queries often dynamically create and subsequently access database objects. When such a query is executed for the
first time, the first execution plan won't contain the information about the objects to be created during runtime. Thus,
in the first execution plan, the processing strategy for those objects is deferred until the runtime of the query. When a
DML statement (within the query) referring to one of those objects is executed, the query is recompiled to generate a
new plan containing the processing strategy for the object.
Both a regular table and a local temporary table can be created within a stored procedure to hold intermediate
result sets. The recompilation of the statement because of deferred object resolution behaves differently for a regular
table when compared to a local temporary table, as explained in the following section.
