THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul White: Page Free Space

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:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #T1 (dummy int NULL);
    CREATE TABLE #T2 (dummy int NULL);
    CREATE TABLE #T3 (dummy int NULL);
 
    DECLARE @T1 AS TABLE (dummy int NULL);
    DECLARE @T2 AS TABLE (dummy int NULL);
    DECLARE @T3 AS TABLE (dummy int 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;
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:

Cached temporary 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:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #T1 (dummy int NULL);
    CREATE TABLE #T2 (dummy int NULL);
    CREATE TABLE #T3 (dummy int NULL);
 
    DECLARE @T1 AS TABLE (dummy int NULL);
    DECLARE @T2 AS TABLE (dummy int NULL);
    DECLARE @T3 AS TABLE (dummy int 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;

There are now only eight cached objects:

Only some objects cached

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

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #T1 (dummy int NULL);
    CREATE TABLE #T2 (dummy int NULL);
    CREATE TABLE #T3 (dummy int NULL);
 
    DECLARE @T1 AS TABLE (dummy int NULL);
    DECLARE @T2 AS TABLE (dummy int NULL);
    DECLARE @T3 AS TABLE (dummy int NULL);
    
    SELECT * INTO #T4 FROM #T1;
    SELECT * INTO #T5 FROM @T2;
    SELECT * INTO #T6 FROM #T3;
 
    -- Does not 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;

There are nine cached objects again:

DROP TABLE is not DDL

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):

Cached Plans and Execution Contexts

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:

2000 cached temporary tables

Statistics on cached temporary objects

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

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #T1 (dummy int NULL);
 
    DECLARE @dummy int;
 
    -- Trigger auto-stats
    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];

Cached temporary object statistics

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
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:

Name remapping

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):

Transaction Log for First Execution

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:

Transaction Log 2

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') IS NOT NULL
    DROP PROCEDURE dbo.Demo;
GO
CREATE PROCEDURE dbo.Demo
AS
BEGIN
    CREATE TABLE #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'
    AND CONVERT(integer, depa.value) = OBJECT_ID(N'dbo.Demo', N'P');

Example output:

Link to the cached plan

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

Cache Counters DMV

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

Perf Counters 1

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

Perf Counters 2

On SQL Server 2008 and later, we can evict a particular plan handle from cache to show that this 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:

Cached Temp 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:

Empty Transaction Log

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

Cached Object Still Exists

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

Asynchronous Log Activity

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 AS TABLE (dummy int 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:

Table Variable Output

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:

CREATE PROCEDURE dbo.Demo
AS
BEGIN
    DECLARE @T AS TABLE (dummy int 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

Now, though, we see the same object id and create date each time:

Cached Table Variables

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 int NULL);
 
    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;

Table Variable Transaction Log

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:

Temporary Table Transaction Log

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.

© 2012 Paul White
Twitter: @SQL_Kiwi
Email: SQLkiwi@gmail.com

image454

Published Friday, August 17, 2012 11:51 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Roji Thomas said:

Thanks Paul. Excellent as usual.

August 16, 2012 8:33 PM
 

tobi said:

This is such a thorough treatment of the subject, just like the last post. It's the final word.

August 17, 2012 7:26 AM
 

Paul Randal said:

More good stuff - better go update my post... :-)

August 22, 2012 5:02 PM
 

Patrick Ge said:

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.

[code moved to http://pastebin.com/5jpYWLDM]

August 22, 2012 9:30 PM
 

Paul White said:

Hi Patrick,

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!

Paul

August 22, 2012 11:17 PM
 

SomewhereSomehow said:

Hi, Paul! Thanks for the post.

I saw no words about parameters issue, have you done any inverstigations of that, was it approved or disproved?

August 28, 2012 3:32 AM
 

Paul White said:

Hi Dima,

Sorry I forgot all about that.  I'll take a look at the thread and get back to you.

Paul

August 28, 2012 4:16 AM
 

SomewhereSomehow said:

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.

August 28, 2012 5:59 AM
 

Jason Yousef said:

Thanks Paul, great article...I'm overwhelmed!!

October 1, 2012 8:35 AM
 

mjswart said:

I would love to be able to match a query plan to a tempdb table.

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.

select * from sys.dm_os_performance_counters

where instance_name like '%Temporary Tables & Table Variables%'

or counter_name like '%temp table%'

September 10, 2013 11:11 AM
 

Eugene Karpovich said:

Hi Paul, thank you for the great article.

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?

The repro script is below:

USE tempdb

GO

SET NOCOUNT ON

GO

IF EXISTS( SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID( N'dbo.p_inner'))

 DROP PROCEDURE dbo.p_inner

GO

CREATE PROCEDURE dbo.p_inner

AS

BEGIN

 SELECT the_string = '' INTO #temp

 SELECT the_string FROM #temp

END

GO

---------------------------------------

IF EXISTS( SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID( N'dbo.p_outer'))

 DROP PROCEDURE dbo.p_outer

GO

CREATE PROCEDURE dbo.p_outer

AS

BEGIN

 SELECT the_date = GETDATE() INTO #temp

 EXEC p_inner

END

GO

---------------------------------------

EXEC dbo.p_outer    -- ***** this statement raises the "Invalid column name 'the_string'." exception

GO

---------------------------------------

July 13, 2014 4:58 PM
 

Paul White said:

Hi Eugene,

Yes, I am familiar with that behaviour, which is weird but apparently "by design". Books Online CREATE TABLE (http://msdn.microsoft.com/en-us/library/ms174979.aspx) contains this statement:

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

The scoping rules for #tables are a bit of a mess, frankly.

July 13, 2014 7:40 PM
 

Eugene Karpovich said:

Thank you for the information, Paul. Apparently, I have missed that piece of the documentation. However, this is weird behavior I agree.

July 13, 2014 8:25 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement