Database Reference
In-Depth Information
The stored procedure has interleaved DDL and DML statements. Figure
17-13
shows the Extended Events output of
this code.
Figure 17-13.
Extended Events output showing recompilation because of DDL and DML interleaving
You can see that the statements are recompiled four times.
The execution plan generated for a query when it is first executed doesn't contain any
•
information about local temporary tables. Therefore, the first generated plan can never be
used to access the temporary table using a DML statement.
The second recompilation comes from the changes encountered in the data contained within
•
the table as it gets loaded.
The third recompilation is because of a schema change in the first temporary table
•
(
#MyTempTable
). The creation of the index on
#MyTempTable
invalidates the existing plan,
causing a recompilation when the table is accessed again. If this index had been created
before the first recompilation, then the existing plan would have remained valid for the
second
SELECT
statement, too. Therefore, you can avoid this recompilation by putting
the
CREATE INDEX
DDL statement above all DML statements referring to the table.
The fourth recompilation generates a plan to include the processing strategy for
#t2
. The
existing plan has no information about
#t2
and therefore can't be used to access
#t2
using the
third
SELECT
statement. If the
CREATE TABLE
DDL statement for
#t2
had been placed before all
the DML statements that could cause a recompilation, then the first recompilation itself would
have included the information on
#t2
, avoiding the third recompilation.
•
