When you create a temp table, you might think that it is exclusive to your session. However, it’s not. Other sessions can reuse your table structure and statistics.
Temp tables in Stored procs: Here is what happens when you repeatedly call a stored procedure which uses temp tables. The table statistics from one table of similar structure gets re-used for the next session. How do you solve this problem?
First, you could execute the stored procedure with the WITH RECOMPILE option to produce better plans.
Second, you could add OPTION(RECOMPILE) at the end of the select query. Second option is a better one than the first since the WITH RECOMPILE causes the entire stored procedure to recompile. Whereas OPTION(RECOMPILE) only recompiles the select statement.
Third, to handle the parameter sniffing problem is to add UPDATE STATISTICS command to the stored procedure instead of OPTION(RECOMPILE). The statistics we want to update are created during compilation or recompilation of the SELECT statement, suggesting the UPDATE STATISTICS should come after the SELECT. Still we dont get accurate statistics.
Finally, we add both UPDATE STATISTICS as well as OPTION(RECOMPILE). This finally takes care of the parameter sniffing issues we were seeing earlier. It’s a success!
This goes to show that Cardinality estimation is a tough problem – try using the statistics histograms yourself to predict how the values in the temp table will join to the histogram on the same column for the much larger table. You may find the task of matching values to values using RANGE_ROWS, EQ_ROWS and so on is really not an easy one at all.
Temporary table Caching Internals:
Key point to note is that CREATE TABLE and DROP TABLE do not create and drop temp tables in a stored procedure, if the temporary object can be cached. Instead, the temporary object is renamed to an internal form when DROP TABLE is executed, and renamed back to the same user-visible name when the corresponding CREATE TABLE is encountered on the next execution.
In addition, any statistics that were auto-created on the temporary table are also cached. This means that statistics from a previous execution remain when the procedure is called again.
CREATE OR ALTER PROCEDURE dbo.TempTableDemo
AS
BEGIN
CREATE TABLE #Temp (col1 integer NULL);
SELECT ObjectID =
OBJECT_ID(N'tempdb..#Temp');
DROP TABLE #Temp;
END;
GO
EXECUTE dbo.TempTableDemo;
EXECUTE dbo.TempTableDemo;
EXECUTE dbo.TempTableDemo;
GO
Example output:
This is quite different from how a permanent table behaves. With a permanent table, CREATE and DROP definitely create and drop a table on each execution of the procedure. The table is new each time, new statistics and plans are created on each execution.
It allows temporary tables an opportunity to cache plans and temporary objects for reuse, which is not possible with permanent table.
Recompilation Thresholds
Cached query plans include a Recompilation Threshold (RT) for statistics considered interesting by the optimizer while compiling the plan.
The query plan also records the cardinality of tables and indexed view referenced by the plan, at the time the plan is compiled.
Generally, if the column modification counter (colmodctr) for interesting statistics change by RT or more, the query recompiles.
Similarly, if the table cardinality has changed by RT or more compared with the value stored at the time the cached plan was created, the query plan also recompiles.
The value of RT depends on the type of table (permanent or temporary) and the number of rows (n) in the table or indexed view at the time of compilation.
The following summary of RT values has been taken from a Microsoft technical article.
- Permanent table
- If n <= 500, RT = 500
- If n > 500, RT = 500 + 0.20 *n
- Temporary table
- If n < 6, RT = 6
- If n >= 6 and n <= 500, RT = 500
- If n > 500, RT = 500 + 0.20 * n
- Table variable
- RT does not exist, Therefore, recompilations do no happen because of change in cardinalities of table variables.
Generally, although queries referencing permanent tables recompile when interesting statistics modification counters change by RT or more, this is not the case for cached temporary tables in stored procedures, which only recompile when table cardinality changes by RT or more, unless OPTION(RECOMPILE) is used, when statistics counters come back into play.
This could be a bug in SQL server, but on the other hand, it is reasonable that a cached temporary table should not cause a recompilation simply because the procedure is called a number of times with the same parameter, with the number of modifications accumulating on each call to eventually pass RT.
Summary
Temporary tables in stored procs have couple of unexpected behaviors:
- Temporary objects may be cached across executions in spite of using CREATE and DROP statements
- Statistics associated with a cached temporary object are also cached
- Statistics can be incorrect compared with the contents of a temporary table
- A TRUNCATE table at the end of the procs increases 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 executions
- Manual UPDATE STATISTICS is not enough to force a recompilation
These above behaviors are not intentional and they have been this way for a long time and not very likely to be changed in the foreseeable future.
Are you likely to be affected by these issues?
A temporary table containing wide range of numbers of rows returns per execution is not likely to be affected. If the recompilation threshold is passed, chances are you will get good statistics and any cached plan with recompile without intervention.
For a cached plan, when the temp table contains 20,000 rows the default threshold is 500 + 0.2 * 20,000 = 4500 rows. This means the next execution with more than 24,500 rows or less than 15,500 rows will pass the threshold test.
Avoiding temporary table caching removes the risk of stale statistics, but a cached plan may still be used unless the RECOMPILE query hint is used. Avoiding temporary table caching will also tend to increase contention on tempdb, and can be difficult to enforce in practice.
Procedures that routinely store relatively small result sets and use cached temporary tables are probably most at risk. This is a common scenario where temporary tables are often used for smaller sets.
General preference should be still to opt for simpler relations queries and, where necessary, for temporary tables over table variables as a starting point.
Database Blog 