A technical SQL Server blog from New Zealand. See also my articles on SQLperformance.com

Temporary Table Caching Explained

SQL Server 2005 onward 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 WITH RECOMPILE

Temporary objects are often created and destroyed at a high rate in production systems, so caching temporary objects can be an important optimization. The temporary object cache is just another SQL Server cache (using the general framework) though it’s 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 usual cache DMVs, for example as CACHESTORE_TEMPTABLES in sys.dm_os_memory_cache_counters.

Cached Object Names

The cached objects themselves are visible in tempdb.sys.tables, named with a single # character followed by the 8-character hexadecimal representation of the object id. This is different from the names of ordinary temporary tables, which have the user-supplied name followed by a bunch of underscores and an id.

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

CREATEPROCEDURE dbo.Demo

AS

BEGIN

CREATETABLE #T1 (dummyintNULL);

CREATETABLE #T2 (dummyintNULL);

CREATETABLE #T3 (dummyintNULL);

DECLARE @T1 ASTABLE (dummyintNULL);

DECLARE @T2 ASTABLE (dummyintNULL);

DECLARE @T3 ASTABLE (dummyintNULL);

SELECT * INTO #T4 FROM #T1;

SELECT * INTO #T5 FROM @T2;

SELECT * INTO #T6 FROM #T3;

WAITFOR DELAY '00:00:01'

END;

GO

DBCC FREEPROCCACHE;

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]';

The results show nine separate cached objects:

Notice the relationship between object id and the name e.g. –1383692523 = hex AD868715.

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. So, for example, if we modify the test above to create an index on table #T5, that particular table will not be cached, but the other temporary objects will be:

CREATEPROCEDURE dbo.Demo

AS

BEGIN

CREATETABLE #T1 (dummyintNULL);

CREATETABLE #T2 (dummyintNULL);

CREATETABLE #T3 (dummyintNULL);

DECLARE @T1 ASTABLE (dummyintNULL);

DECLARE @T2 ASTABLE (dummyintNULL);

DECLARE @T3 ASTABLE (dummyintNULL);

SELECT * INTO #T4 FROM #T1;

SELECT * INTO #T5 FROM @T2;

SELECT * INTO #T6 FROM #T3;

-- Prevents caching of #T5

CREATEINDEX nc1 ON #T5 (dummy);

WAITFOR DELAY '00:00:01'

END;

GO

DBCC FREEPROCCACHE;

WAITFOR DELAY '00:00:05';

GO

EXECUTE dbo.Demo;

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 whether you explicitly drop a temporary table at the end of a procedure or not).

CREATEPROCEDURE dbo.Demo

AS

BEGIN

CREATETABLE #T1 (dummyintNULL);

CREATETABLE #T2 (dummyintNULL);

CREATETABLE #T3 (dummyintNULL);

DECLARE @T1 ASTABLE (dummyintNULL);

DECLARE @T2 ASTABLE (dummyintNULL);

DECLARE @T3 ASTABLE (dummyintNULL);

SELECT * INTO #T4 FROM #T1;

SELECT * INTO #T5 FROM @T2;

SELECT * INTO #T6 FROM #T3;

-- Does not prevent caching

DROPTABLE #T1, #T4, #T6;

TRUNCATETABLE #T2;

TRUNCATETABLE #T5;

UPDATESTATISTICS #T3;

WAITFOR DELAY '00:00:01'

END;

GO

DBCC FREEPROCCACHE;

WAITFOR DELAY '00:00:05';

GO

EXECUTE dbo.Demo;

There are nine cached objects again:

Concurrent executions

If a 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 (image reproduced from the plan caching white paper):

The runtime contents of a temporary object (table or variable) are obviously 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 Adam Machanic’s SQL Query Stress tool running 200 threads. This is the reason for the 1 second delay in the procedure – to make sure the procedure runs for a little while so new execution contexts are generated for each execution rather than reused. The contents of tempdb after that test were as follows:

Statistics on cached temporary objects

Any auto-stats created are linked to the cached temporary table:

CREATEPROCEDURE dbo.Demo

AS

BEGIN

CREATETABLE #T1 (dummyintNULL);

DECLARE @dummyint;

-- Trigger auto-stats

SELECT @dummy = dummy

FROM #T1

WHEREdummy > 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];

In the case of 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: auto-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 explicitly linked to the object id of the cached temporary object that caused the auto-stats 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, for the simple reason that 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 renamed to the normal 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:

USE tempdb;

GO

CREATEPROCEDURE dbo.Demo

AS

BEGIN

CREATETABLE #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%';

DROPTABLE #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 (click to enlarge):

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 to the user-visible name:

The 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 seems to be 5 seconds or less on current SQL Server versions, 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') ISNOTNULL

DROPPROCEDURE dbo.Demo;

GO

CREATEPROCEDURE dbo.Demo

AS

BEGIN

CREATETABLE #Demo (i int);

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'

ANDCONVERT(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';

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'MSSQL%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');

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

On SQL Server 2008 and later, we can evict a particular plan handle from cache to show that this removes the cached temporary table:

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'

ANDCONVERT(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.[BeginTime],

fd.[EndTime]

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.[BeginTime],

fd.[EndTime]

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 we issued to truncate the log:

Then we see that the cached object still exists at this point (though the DMVs now show zero cached temporary objects):

After an up-to-five-second delay, the transaction log contains:

Notice the system transaction named ‘droptemp’ is performed by system SPID 14, and instead of the renaming we saw earlier, all references to the cached object are deleted from the system tables.

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 ASTABLE (dummyintNULL);

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 already mentioned, table variables in a stored procedure can be cached just like temporary tables:

CREATEPROCEDURE dbo.Demo

AS

BEGIN

DECLARE @T ASTABLE (dummyintNULL);

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

Now, though, we 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

CREATEPROCEDURE dbo.Demo

AS

BEGIN

DECLARE @T ASTABLE (dummyintNULL);

INSERT @T 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;

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 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, CREATE STATISTICS). Table variables are also 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; disallowed by table variables, but which does not affect 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 my last post). They 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 behaviour!) 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.

Thanks Paul. Great stuff. I've learned a lot from these two posts about which I never thought this deep before.

I was reading "The runtime contents of a temporary object (table or variable) are obviously specific to a particular execution, so it makes sense for the cached object to be associated with execution contexts rather than the parent plan" and trying to simulate this by simply making a little change to your script. Below is the updated script. When I ran it in one query window and ran "EXECUTE dbo.Demo;" in another query window simultaneously. The result is perfect. Different exectutions don't interfere with each other's data in the temporary table and there is just one #demo table. However, after I put the insert statement into a transaction and let it wait for 5 seconds and did the same test again. I have two temporary tables. I can't think of any reason why another #Demo is created. It's like the 2nd query window created a new #Demo rather than using the exsiting cached one from 1st query window. Am I missing any points here?

I am just trying to get my head more confused ^_^. My apology if my question doesn't make sense or demonstrate I've completedly understood your posts.

I'm not sure I follow exactly what you did, but I'm going to take a crack at it anyway. I uncommented the delay in your procedure and ran it in two sessions, ensuring the executions overlapped. In that case, as expected, two execution contexts were required (derived from the one cached plan) so two cached tables are seen. If I have misunderstood your test, please feel free to correct me!

Thanks, appreciated. As I understood cached object is "linked" to the plan of proc, to have multiple cached tables we should have multiple plans for one proc, and it is possible for example if there are different set options. But due to different params... I took a short look at our dbs to find out, if there are any plans, with the same cache keys, but multiple for one proc - and found nothing. But I didn't do deep investigations, so have no confirmation or disproof. If you find something, you are welcome to share it.

It might be possible given that when a query plan is removed from cache, those tempdb tables are cleaned up.

So SQL Server knows how to map a table to a query plan, is that possible for us end users? It would help me go beyond number of temp tables and help me identify which queries are creating temp tables most often.

Have you ever come across an issue (which might be temp table caching related), when SQL Server issues "Invalid column name" exception in case two stored procs are involved in a query, each of which creates a temp table with the same name but different columns?

"A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against."