Non-indexed temporary tables in warehouse stored procs

The definitive work on this by Paul White explains in tremendous detail some surprising facts about the caching of temporary tables and their statistics.

I’m going to summarise some of these points and outline some general recommendations I like my team to follow.  This goes a little beyond Paul’s advice, possibly because he thought this corollary too obvious to mention, or because he wanted to retain applicability to OLTP workloads with a high volume of concurrent transactions.

A number of database developers are increasingly focusing on warehouse applications, and this post is directed at you!  The distinction (for the purpose of this post) is that warehouse stored procs typically take many seconds (or minutes) to run, and each proc is only run a few times a day (ok, possibly hundreds, but certainly not hundreds of thousands).

I’m going to ignore table variables and indexed temp tables for now, and focus on non-indexed temp tables as they have the more remarkable behaviour that deserves special attention. The issue is that (under certain conditions) the temp table and its statistics are cached when the stored proc is run. If the cardinality of the temp table on a subsequent run is similar to the previous run, the cached stats may be reused on subsequent statements that reference the table, even though the data distribution may have materially changed.  This can occasionally cause performance problems, and these are typically difficult for most teams to diagnose.

Paul White endorses the approach of updating statistics on the table immediately after populating it, and then adding “OPTION (RECOMPILE)” to any statements referencing the temp table. While this will work, there are a few problems with this approach:

• Cost of unnecessarily recompiles
• The query plan is not retained in the cache for performance analysis
• The workaround is mixed in with the business logic (and that hint must be applied on every query referencing the table)

Another approach is, at the end of the proc, to truncate the temp table, and then add an index to it.  The truncate is itself fast, and merely there to speed the index creation.  The index prevents the temp table being cached, and sets behaviour to match most developers’ expectations. The query optimizer may still reuse a plan from the previous run, but this is what we expect for any statement in the proc. For each temp table this requires two statements (truncate, add index), both of which are fast, and separated from the core business logic. For warehouses, this option is generally preferred by my team.