9

I've been building stored procedures and can't find any documentation on how the SQL Server optimizer handles #temp tables.

If I have 4 joins to a #temp table where that #temp table is created DURING the stored procedure execution, and the #temp table may contain zero to many records, how is it estimated?

Is it estimated to be as large as some of the tables in the join, since there are no statistics at the time of CREATE/ALTER? Is it estimated to be tiny? Is it estimated to be like other #temporary tables?

Notice, this is assuming no re-compile at execution time and that the original is used.

2 Answers 2

13

SQL Server generally builds a complete set of execution plans for the statements in a stored procedure before the procedure begins executing.

That approach is slightly modified when temporary tables are present:

  • Compilation of each statement that references a temporary table is deferred until the statement is actually executed.
  • SQL Server has supported statement-level recompilation since the 2005 release. Only the statement(s) affected are recompiled; the remainder of the procedure's plans are unaffected.
  • Deferred compilation means SQL Server knows the run-time cardinality of the temporary table during optimization.
  • SQL Server may also automatically create statistics to support the query being compiled. This happens during compilation, and technically results in a recompilation.
  • The execution plan built therefore benefits from accurate cardinality information and auto-created statistics objects (if that option is enabled).

From the documentation on Recompiling Execution Plans:

In SQL Server versions prior to 2005, whenever a statement within a batch causes recompilation, the entire batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, was recompiled. Starting with SQL Server 2005 (9.x), only the statement inside the batch that triggers recompilation is recompiled. Also, there are additional types of recompilations in SQL Server 2005 (9.x) and later because of its expanded feature set.

Statement-level recompilation benefits performance because, in most cases, a small number of statements causes recompilations and their associated penalties, in terms of CPU time and locks. These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

and from Table Variable Deferred Compilation:

With table variable deferred compilation, compilation of a statement that references a table variable is deferred until the first actual execution of the statement. This deferred compilation behavior is identical to the behavior of temporary tables.


That's the basic outline, but not the whole story.

As already pointed out in another answer, temporary tables in stored procedures may also be cached for reuse. This caching can improve performance and scalability significantly, but it does complicate the conditions for plan recompilation and how statistical information is maintained.

I describe these additional complexities for the interested (and advanced) reader in Temporary Table Caching Explained and Temporary Table Caching in Stored Procedures.

0
8

Roughly speaking, cardinality estimates for statistics creation on #temp tables follows that for regular, non-temp tables. Having said that, as Paul White points out here, temp tables inside stored procedures can suffer from cached statistics values.

From the page above, the salient takeaway is:

Temporary tables in stored procedures have a number of unexpected characteristics:

  • Temporary objects may be cached across executions, despite explicit CREATE and DROP statements
  • Statistics associated with a cached temporary object are also cached
  • Statistics may be 100% wrong compared with the current contents of a temporary table
  • An implicit TRUNCATE TABLE at the end of the procedure doubles the expected impact on column modification counters
  • It is possible to update cached statistics before the statement that caused them to be created
  • OPTION (RECOMPILE) changes the way column modifications are carried over between procedure calls
  • Manual UPDATE STATISTICS is not enough to force a recompilation

I would suggest taking a look at the post to thoroughly understand how you might be affected by temp table caching.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.