Query Recompilation - SQL Server Query Performance Tuning - page 320

Schema or Bindings Changes
When the schema or bindings to a view, regular table, or temporary table change, the existing query's execution plan
becomes invalid. The query must be recompiled before executing any statement that refers to a modified object. SQL
Server automatically detects this situation and recompiles the stored procedure.

■ i talk about recompilation due to schema changes in more detail in the “Benefits and Drawbacks of
recompilation” section.

Note

Statistics Changes
SQL Server keeps track of the number of changes to the table. If the number of changes exceeds the recompilation
threshold (RT) value, then SQL Server automatically updates the statistics when the table is referred to in the
statement, as you saw in Chapter 12. When the condition for the automatic update of statistics is detected, SQL Server
automatically marks the statement for recompile, along with the statistics update.
The RT is determined by a formula that depends on the table being a permanent table or a temporary table
(not a table variable) and how many rows are in the table. Table 17-3 shows the basic formula so that you can determine
when you can expect to see a statement recompile because of data changes.

Table 17-3. Formula for Determining Data Changes

Type of Table Formula

If number of rows (n) <= 500, RT = 500
If n > 500, RT = 500 + .2 * n
Or
Proportional after 25,000 rows when trace flag 2371 is set

Permanent table

If n < 6, RT = 6
If 6 <= n <= 500, RT = 500
If n > 500, RT = 500 + .2 * n
Or
Proportional after 25,000 rows when trace flag 2317 is set

Temporary table

To understand how statistics changes can cause recompilation, consider the following example. The stored
procedure is executed the first time with only one row in the table. Before the second execution of the stored
procedure, a large number of rows are added to the table.

■ please ensure that the AUTO_UPDATE_STATISTICS setting for the database is ON . you can determine the
AUTO_UPDATE_STATISTICS setting by executing the following query:

Note

SELECT DATABASEPROPERTYEX('AdventureWorks2012', 'IsAutoUpdateStatistics');