Caching of Temporary Objects

SQL Server 2005 introduced quite a few enhancements related to caching of temporary objects. You can find details about those and other enhancements related to tempdb in the paper: Working with tempdb in SQL Server 2005, which I strongly recommend reading.

One of the enhancements that I find particularly interesting is that SQL Server may cache temporary objects (e.g., temporary tables, table variables) across invocations of a routine (e.g., stored procedure, trigger, UDF). It can keep the metadata representing the temporary object, as well as a couple of pages (data page and IAM page) in cache, preventing the need to allocate and deallocate those every time you execute the routine. For routines that are invoked very frequently, this can save time and reduce contention on system catalog tables and allocation pages.

You need to be aware though that in certain situations SQL Server won’t cache temporary objects. One such case is when you apply DDL to a temporary table after it was created. This one is similar to a cause for a recompile and seems quite reasonable. Another case is when you use named constraints as part of the definition of the temporary object. I find this case rather peculiar, but tests do prove that it’s true.

Next, I’ll demonstrate the caching behavior of temporary objects and situations that prevent such caching.

First, run the following code to create a procedure called TestCaching that creates a temporary table, populates it with a few rows, and then queries it:

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('dbo.TestCaching', 'P') IS NOT NULL

  DROP PROC dbo.TestCaching;

GO

CREATE PROC dbo.TestCaching

AS

CREATE TABLE #T1(n INT, filler CHAR(2000));

INSERT INTO #T1 VALUES

  (1, 'a'),

  (2, 'a'),

  (3, 'a');

SELECT n, filler FROM #T1;

GO

Before executing the stored procedure, run the following code to see whether you currently have any temporary objects in tempdb:

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

You should get an empty set as output, unless there are temporary objects unrelated to our procedure.

Execute the procedure:

EXEC dbo.TestCaching;

Look for temporary objects in tempdb:

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

Even though the procedure already terminated, the aforementioned information for the temporary table was kept, and subsequent invocations of the procedure can reuse it. You should get an entry in the output for the temporary table that was created by this procedure. I got a table called #117F9D94.

Before the next test, mark the proc for recompile by running the following code:

EXEC sp_recompile 'dbo.TestCaching';

Look again for temporary objects in tempdb:

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

You should get an empty set back.

The next example will demonstrate that DDL applied to the temporary object prevents caching it. Run the following code to alter the procedure, creating an index on the table after creating the table itself:

ALTER PROC dbo.TestCaching

AS

CREATE TABLE #T1(n INT, filler CHAR(2000));

CREATE UNIQUE INDEX idx1 ON #T1(n);

INSERT INTO #T1 VALUES

  (1, 'a'),

  (2, 'a'),

  (3, 'a');

SELECT n, filler FROM #T1;

GO

Execute the procedure:

EXEC dbo.TestCaching;

Query temporary objects:

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

You should get an empty set back. To avoid the problem, use a UNIQUE constraint as part of the table definition instead of creating an index explicitly, like so:

ALTER PROC dbo.TestCaching

AS

CREATE TABLE #T1(n INT UNIQUE, filler CHAR(2000));

INSERT INTO #T1 VALUES

  (1, 'a'),

  (2, 'a'),

  (3, 'a');

SELECT n, filler FROM #T1;

GO

Execute the procedure again:

EXEC dbo.TestCaching;

Query temporary objects:

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

This time the output should indicate that the temporary table was kept. I got a table called #1367E606 in the output.

As for an example that naming a constraint prevents caching the temporary object, run the following code to alter the previous procedure by naming the constraint:

ALTER PROC dbo.TestCaching

AS

CREATE TABLE #T1(n INT CONSTRAINT UNQ_#T1_n UNIQUE, filler CHAR(2000));

INSERT INTO #T1 VALUES

  (1, 'a'),

  (2, 'a'),

  (3, 'a');

SELECT n, filler FROM #T1;

GO

Execute the procedure:

EXEC dbo.TestCaching;

Query temporary objects:

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';

This time you should get an empty set back (might take a few seconds before you see the entry removed).

So if you want to allow caching of temporary objects, you need to avoid applying DDL post creation of the object, and if you define constraints, don’t name them!

Cheers,

BG