Temporary Table Caching Explained

SQL Server (since 2005) caches temporary tables and table variables referenced in stored procedures for reuse, reducing contention on tempdb allocation structures and catalogue tables.

A number of things can prevent this caching (none of which are allowed when working with table variables):

• Named constraints (bad idea anyway, since concurrent executions can cause a name collision)
• DDL after creation (though what is considered DDL is interesting)
• Creation using dynamic SQL
• Table created in a different scope
• Procedure executed using WITH RECOMPILE

Temporary objects are often created and destroyed at a high rate in production systems, so caching can be an important optimization.

The temporary object cache is just another SQL Server cache using the general framework, though its entries are a bit more visible than most.

The sys.dm_os_performance_counters DMV exposes a number of counters under the Temporary Tables & Table Variables instance of the Plan Cache object.

The cache is also visible through the other cache DMVs, for example as CACHESTORE_TEMPTABLES in sys.dm_os_memory_cache_counters.

Cached Object Names

The cached objects are visible in tempdb.sys.tables. The cached object name is a single # character followed by the 8-character hexadecimal representation of the object id.

This is different from the names of ordinary temporary tables, where the name is made up of the user-supplied name, a number of underscores, and finally the hex object id.

The following procedure shows a total of nine cache object examples, created using CREATE TABLE #xyz syntax, table variables, and SELECT…INTO:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #T1 (dummy integer NULL);
    CREATE TABLE #T2 (dummy integer NULL);
    CREATE TABLE #T3 (dummy integer NULL);

    DECLARE @T1 AS TABLE (dummy integer NULL);
    DECLARE @T2 AS TABLE (dummy integer NULL);
    DECLARE @T3 AS TABLE (dummy integer NULL);

    SELECT * INTO #T4 FROM #T1;
    SELECT * INTO #T5 FROM @T2;
    SELECT * INTO #T6 FROM #T3;

    WAITFOR DELAY '00:00:01'
END;
GO
DBCC FREEPROCCACHE;
GO
EXECUTE dbo.Demo;
GO
SELECT
    T.*
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE 
        N'#[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]';

Running that script shows nine separate cached objects:

Notice the relationship between the object id and the name. For example -1383692523 = hex AD868715 as a double word signed integer.

Caching is per object not per procedure

If any of the temporary objects in a procedure are not cacheable for any reason, the others may still be cached.

For example, if we modify the test above to create an index on table #T5 as a separate DDL operation, that particular table will not be cached, but the other temporary objects will be:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #T1 (dummy integer NULL);
    CREATE TABLE #T2 (dummy integer NULL);
    CREATE TABLE #T3 (dummy integer NULL);

    DECLARE @T1 AS TABLE (dummy integer NULL);
    DECLARE @T2 AS TABLE (dummy integer NULL);
    DECLARE @T3 AS TABLE (dummy integer NULL);

    SELECT * INTO #T4 FROM #T1;
    SELECT * INTO #T5 FROM @T2;
    SELECT * INTO #T6 FROM #T3;

    -- Prevents caching of #T5
    CREATE INDEX nc1 ON #T5 (dummy);

    WAITFOR DELAY '00:00:01'
END;
GO
DBCC FREEPROCCACHE;
WAITFOR DELAY '00:00:05';
GO
EXECUTE dbo.Demo;
GO
SELECT
    T.*
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE 
        N'#[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]';

Note: Only temporary tables can have indexes added in a separate DDL statement (i.e. after the object is created). Table variables have always supported indexes to enforce primary key and unique constraints — and since SQL Server 2014 have allowed indexes to be declared inline with the create statement — but variables do not allow indexes to be added in a separate CREATE INDEX statement.

Now, there are now only eight cached objects:

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:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #T1 (dummy integer NULL);
    CREATE TABLE #T2 (dummy integer NULL);
    CREATE TABLE #T3 (dummy integer NULL);

    DECLARE @T1 AS TABLE (dummy integer NULL);
    DECLARE @T2 AS TABLE (dummy integer NULL);
    DECLARE @T3 AS TABLE (dummy integer NULL);

    SELECT * INTO #T4 FROM #T1;
    SELECT * INTO #T5 FROM @T2;
    SELECT * INTO #T6 FROM #T3;

    -- None of these prevent caching
    DROP TABLE #T1, #T4, #T6;

    TRUNCATE TABLE #T2;
    TRUNCATE TABLE #T5;

    UPDATE STATISTICS #T3;

    WAITFOR DELAY '00:00:01'
END;
GO
DBCC FREEPROCCACHE;
WAITFOR DELAY '00:00:05';
GO
EXECUTE dbo.Demo;
GO
SELECT
    T.*
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE 
        N'#[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]';

All nine objects are cached again:

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:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #T1 (dummy int NULL);
    INSERT #T1 (dummy) VALUES (1);

    DECLARE @dummy int;

    -- Trigger auto-stats creation
    SELECT @dummy = dummy 
    FROM #T1
    WHERE dummy > 0;

    WAITFOR DELAY '00:00:01'
END;
GO
DBCC FREEPROCCACHE;
WAITFOR DELAY '00:00:05';
GO
EXECUTE dbo.Demo;
GO
SELECT
    T.[name],
    T.[object_id],
    S.[name],
    S.auto_created
FROM tempdb.sys.tables AS T
JOIN tempdb.sys.stats AS S ON
    S.[object_id] = T.[object_id];

A typical result set is:

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:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #Demo (i int);

    SELECT 
        T.[name],
        T.[object_id],
        T.[type_desc],
        T.create_date
    FROM sys.tables AS T
    WHERE
        T.[name] LIKE N'#Demo%';

    DROP TABLE #Demo;

    SELECT 
        T.[name],
        T.[object_id],
        T.[type_desc],
        T.create_date
    FROM sys.tables AS T
    WHERE
        T.[name] LIKE N'#________';
END;
GO
DBCC FREEPROCCACHE;
WAITFOR DELAY '00:00:05';
GO
CHECKPOINT;
EXECUTE dbo.Demo;
GO
SELECT
    FD.[Current LSN],
    FD.Operation,
    FD.AllocUnitName,
    FD.[Transaction Name],
    FD.[Transaction ID],
    CONVERT(sysname, SUBSTRING(FD.[RowLog Contents 0], 3, 256)),
    CONVERT(sysname, SUBSTRING(FD.[RowLog Contents 1], 3, 256))
FROM sys.fn_dblog(NULL, NULL) AS FD;

The first time it is run the first part of the output is:

Notice that the object ids are the same, and the object has familiar external name while in scope, but is renamed after the DROP TABLE statement.

The transaction log entries displayed are:

The highlighted section shows the table being renamed in the internal catalogue tables from the user-visible name to the internal name.

On the second run, there is an extra renaming log entry in the CREATE TABLE system transaction, as the object is renamed from the internal form back to the user-visible name:

This demonstration shows that CREATE TABLE and DROP TABLE for a cached temporary object are replaced by renaming operations.

Cached Object Scope

The cached object is scoped to the query plan that references it.

If the plan is evicted from cache for any reason (perhaps by ALTER or DROP PROCEDURE, or an explicit DBCC FREEPROCCACHE command), a background thread removes the tempdb object.

This is not synchronous to the command that causes the eviction. The delay appears to be 5 seconds or less, and is performed by a system process id.

The following code shows the link between the cached temporary table and the cached plans for the stored procedure:

USE tempdb;
GO
IF OBJECT_ID(N'dbo.Demo', N'P') IS NOT NULL
    DROP PROCEDURE dbo.Demo;
GO
CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #Demo (i integer NULL);
END;
GO
DBCC FREEPROCCACHE;
WAITFOR DELAY '00:00:05';
GO
EXECUTE dbo.Demo;
GO
SELECT
    T.[name],
    T.[object_id],
    T.[type_desc],
    T.create_date
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE N'#________';
GO
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_plan_attributes(DECP.plan_handle) AS DEPA
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    DECP.cacheobjtype = N'Compiled Plan'
    AND DECP.objtype = N'Proc'
    AND DEPA.attribute = N'objectid'
    AND CONVERT(integer, DEPA.[value]) = OBJECT_ID(N'dbo.Demo', N'P');

Example output:

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:

SELECT
    DOMCC.[name],
    DOMCC.[type],
    DOMCC.entries_count,
    DOMCC.entries_in_use_count
FROM sys.dm_os_memory_cache_counters AS DOMCC
WHERE 
    DOMCC.[type] = N'CACHESTORE_TEMPTABLES';

Example output:

Another way is to check the performance counters (also accessible via DMV):

SELECT
    DOPC.[object_name], 
    DOPC.counter_name, 
    DOPC.cntr_value
FROM sys.dm_os_performance_counters AS DOPC
WHERE
    DOPC.[object_name] LIKE N'%:Plan Cache%'
    AND DOPC.instance_name = N'Temporary Tables & Table Variables'
    AND DOPC.counter_name IN (N'Cache Object Counts', N'Cache Objects in use');

Output:

This second example shows 200 cached objects from a different run:

On SQL Server 2008 and later, we can evict a particular plan from cache by plan handle. This also removes the cached temporary table:

DBCC FREEPROCCACHE
    (0x05000200CC7BDE0940E16D8C000000000000000000000000);

As mentioned, there can be a delay of up to 5 seconds before the cached object is removed after the DBCC statement completes (though the DMVs reflect the cache changes immediately).

The more comprehensive test below shows all these things combined:

-- Cache a temporary object
EXECUTE dbo.Demo;
GO
-- Show the cached table
SELECT
    T.[name],
    T.[object_id],
    T.[type_desc],
    T.create_date
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE N'#________';

DECLARE @plan_handle varbinary(64);

-- Find the plan handle
SELECT
    @plan_handle = DECP.plan_handle
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_plan_attributes(DECP.plan_handle) AS DEPA
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    DECP.cacheobjtype = N'Compiled Plan'
    AND DECP.objtype = N'Proc'
    AND DEPA.attribute = N'objectid'
    AND CONVERT(integer, DEPA.[value]) = OBJECT_ID(N'dbo.Demo', N'P');

-- Truncate the log
CHECKPOINT;

-- Evict the plan
DBCC FREEPROCCACHE(@plan_handle);

-- Show log entries
SELECT
    FD.[Current LSN],
    FD.SPID,
    FD.Operation,
    FD.Context,
    FD.AllocUnitName,
    FD.[Transaction Name],
    FD.[Transaction ID],
    FD.[Begin Time],
    FD.[End Time]
FROM sys.fn_dblog(NULL,NULL) AS FD;

-- Cached object not dropped yet
SELECT
    T.[name],
    T.[object_id],
    T.[type_desc],
    T.create_date
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE N'#________';

WAITFOR DELAY '00:00:05';

-- Show cleanup
SELECT
    FD.[Current LSN],
    FD.SPID,
    FD.Operation,
    FD.Context,
    FD.AllocUnitName,
    FD.[Transaction Name],
    FD.[Transaction ID],
    FD.[Begin Time],
    FD.[End Time]
FROM sys.fn_dblog(NULL,NULL) AS FD;

-- Gone!
SELECT
    T.[name],
    T.[object_id],
    T.[type_desc],
    T.create_date
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE N'#________';

The first result set shows the cached temporary object:

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:

DECLARE @T AS table (dummy integer NULL);

SELECT
    T.[name],
    T.[object_id],
    T.[type_desc],
    T.create_date
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE N'#________';
GO 5

The batch runs five times and produces output like this:

Notice that the object id is different each time (and so, therefore, is the name).

As previously mentioned, table variables in a stored procedure can be cached just like temporary tables:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    DECLARE @T AS table (dummy integer NULL);

    SELECT
        T.[name],
        T.[object_id],
        T.[type_desc],
        T.create_date
    FROM tempdb.sys.tables AS T
    WHERE
        T.[name] LIKE N'#________';
END
GO
EXECUTE dbo.Demo;
GO 5

Note we now see the same object id and create date each time:

Once a table variable is cached, the transaction log records for a simple procedure are quite interesting:

DBCC FREEPROCCACHE;
WAITFOR DELAY '00:00:05';
GO
CREATE PROCEDURE dbo.Demo
AS
BEGIN
    DECLARE @T AS table (dummy integer NULL);
    INSERT @T (dummy) VALUES (1);
END;
GO
-- Cache the table variable
EXECUTE dbo.Demo;
GO
SELECT
    T.[name],
    T.[object_id],
    T.[type_desc],
    T.create_date
FROM tempdb.sys.tables AS T
WHERE
    T.[name] LIKE N'#________';
GO
CHECKPOINT;
GO
EXECUTE dbo.Demo;
GO
SELECT
    FD.[Current LSN],
    FD.Operation,
    FD.AllocUnitName,
    FD.[Transaction Name],
    FD.[Transaction ID]
FROM sys.fn_dblog(NULL, NULL) AS FD;

Output:

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). Note table variables do allow indexes, but only as part of the initial declaration.

• 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.

© Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi