Scott's Answer, Completed: Temp Tables and Recompiles In SQL Server 2005

When I posted yesterday on this topic (first broached here), I'd found some good information pertinent to SQL Server 2000 in Tom Davidson's MSDN article. However, at that point I hadn't had much luck finding the equivalent information for SQL Server 2005.

I dropped an email to Tom, and he very helpfully pointed me towards two TechNet articles of interest:

I'm still absorbing the content of these articles, but at this point I can say that, in a general sense, there is in SQL Server 2005 a mechanism similar to that found in SQL Server 2000.

Here's the money quote from the first article:

Recompilation threshold (RT)

The recompilation threshold for a table partly determines the frequency with which queries that refer to the table recompile. RT depends on the table type (permanent versus temporary), and the number of rows in the table (cardinality) when a query plan is compiled. The recompilation thresholds for all of the tables referenced in a batch are stored with the query plans of that batch.

Permanent table:

If n <= 500, RT = 500.
If n > 500, RT = 500 + (0.20 * n)

Temporary table:

If n < 6, RT = 6
If 6 <= n <= 500, RT = 500
If n > 500, RT = 500 + (0.20 * n)

Table variable:

RT does not exist. Therefore, recompilations do not happen because of changes in cardinalities of table variables.

So, the mere presence of a temp table declaration is not enough to force a recompile, but a significant cardinality change could provoke one. On the other hand, table variables never provoke a recompile of their own volition.

Thanks again to Scott for his questions, as well as to Tom Davidson for pointing me at the pertinent SQL Server 2005 literature.

     -wp