Apparently, DROP TABLE is not DDL

Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS.

None of these things prevent temporary table caching, so it does not matter (from a caching perspective) whether you explicitly drop a temporary table at the end of a procedure or not. Either way, the temporary table can still be cached:

Concurrent executions

If the same stored procedure is executed concurrently, multiple separate cached objects may be created in tempdb.

There is one cached plan for the procedure, but one cached temporary object per execution context derived from that cached plan.

Recall that execution contexts are relatively lightweight instances of a cached plan, populated with execution-specific data such as temporary object ids and parameter values. The following image is reproduced from the SQL Server Technical Article Plan Caching and Recompilation in SQL Server 2012:

The runtime contents of a temporary object (table or variable) are specific to a particular execution, so it makes sense for the cached object to be associated with execution contexts rather than the parent plan.

There may also be more than one cached plan for a procedure in cache (for example due to compilations with different SET options) and each parent plan will have its own collection of execution contexts, so there can be one cached tempdb object per execution context per plan.

There does not appear to be a fixed limit on the number of these cached objects. I was able to quickly create 2,000 of them using the test procedures above and the SQL Query Stress tool running 200 threads.

This is the reason for the one-second delay in the procedure. It ensures the procedure runs for long enough that new execution contexts are generated for each execution rather than being reused.

The contents of tempdb after that 200-thread test were as follows:

Statistics on cached temporary objects

Any automatically-created stats are linked to the cached temporary table.

Ensure AUTO_CREATE_STATISTICS is ON for tempdb before running the following demonstration code:

In case there are multiple execution contexts, you might be wondering if each of the tempdb objects can have auto-created statistics associated with them.

The answer is no. Automatically-created stats are used to compile the parent plan, and execution contexts are derived from that same plan as needed.

The metadata looks a little odd though: The statistics are linked to the object id of the cached temporary object that caused the automatic statistics to be created. Other cached tables for the same plan have different ids, and so do not link to the sys.stats entry.

Statistics created using an explicit CREATE STATISTICS statement are not linked to a cached temporary object, because CREATE STATISTICS is considered DDL, and prevents caching from occurring in the first place.

Drop and Create in Detail

The first time a procedure containing a cacheable temporary object is executed, the temporary object is created as normal, then renamed to the hexadecimal internal form described previously when the object is dropped (explicitly or implicitly at the end of the procedure).

On subsequent executions, the cached object is again renamed to the regular user-visible name when ‘created’ in the procedure, and renamed back to the internal form when it is ‘dropped’.

The following script demonstrates the creation and renaming of cached temporary objects:

Aside from checking for suitably-named objects in tempdb, there are a number of ways to see how many cached temporary objects (tables and variables) exist, and how many are in use by executing code. One way is to query the sys.dm_os_memory_cache_counters DMV:

The transaction log entries immediately after evicting the plan from cache show no activity aside from the CHECKPOINT issued to truncate the log:

The cached object still exists at this point (though the DMVs now show zero cached temporary objects):

After a delay of up to five seconds, the transaction log contains:

Notice the system transaction named droptemp is performed by system SPID 14. Instead of the renaming we saw earlier, all references to the cached object are deleted from the system tables now that the parent cached plan no longer exists.

More about Table Variables

You might recognise the internal hexadecimal name for cached temporary objects — the same is used for table variables outside a stored procedure:

Notice the reference to the internal name #628FA481 and the clean up activity.

The same procedure with a temporary table instead of a table variable generates a bit more work for the server:

Many of the entries are similar to the table variable case, with extra steps to rename the cached object when the CREATE TABLE and the implicit DROP TABLE at the end of the procedure are executed.

Clearly, some efforts have been made to make table variables more efficient than temporary tables, while sharing many features in common at quite a low level.

Another interesting thing, as I mentioned right at the start of this post, is that table variables disallow just about all of the actions that prevent caching of a temporary table:

Table variables do not allow named constraints, or DDL that affects caching (e.g. CREATE INDEX and CREATE STATISTICS).

Table variables are scoped more tightly than temporary tables. While we can create a temporary table in one procedure, and refer to it in another, the same thing cannot be done with table variables.

For the same scoping reasons, table variables cannot be defined using dynamic SQL and referenced outside that context.

One oddity is TRUNCATE TABLE. It is disallowed by table variables, but it does not prevent temporary table caching.

Anyway, the restrictions mean that table variables can always be cached, and don’t allow some of the crazy things that are possible with temporary tables (particularly as regards scoping, but also the cached statistics issue I described in Temporary Tables in Stored Procedures).

Table variables also have the potential to perform better (no hidden renaming) at least in some high-volume circumstances.

If only we were able to create statistics (with intuitive behaviours!) and indexes after creation, table variables might well make the old Sybase ‘non-shareable temporary tables’ finally redundant. Until then, we are left having to choose one or the other as best we can.