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:

• Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005; and
• Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

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

Comments

• Anonymous
  January 01, 2003
  Binh Cao's customer left a comment on yesterday's post on global temp tables. Here's the money quote:

• Anonymous
  January 01, 2003
  One of the great things about my involvement with the SQL Ranger community is that I occasionally here

• Anonymous
  January 01, 2003
  PingBack from http://blogs.technet.com/wardpond/archive/2006/08/17/447577.aspx

• Anonymous
  August 17, 2006
  Fantastic - thanks for clarifying that, Ward. You know what? I had not even noticed that SP:Recompile was deprecated so thank you for showing me the light :)  I had read (and still have a printed copy of) the "Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005" article before but it was well over a year ago. The funny thing is that the article says to us SP:Recompile to see the recompiles so that's what I was going from, I guess. Oh well, it was wrriten 1.5 years before the RTM  :)
  
  One thing you might mention with respect to using temp tables and/or permanent table creation in your stored procs is to be careful to include such DDL at the beginning of your proc, not interleaved in the code logic. Do a search in that article for "Recompilations due to mixing DDL and DML" and you'll read up on what I was referring to in an earlier post/response.
  
  Thanks, Ward - good posts.