There is overhead associated with creating objects in tempdb.  Sometimes this overhead surfaces as pagelatch contention – most commonly in PFS, GAM, or SGAM pages.  Tuning to alleviate PFS, GAM, or SGAM contention isn’t so much the topic I’ll be writing about; however, this will help alleviate the pressure on tempdb.

What is tempdb caching?

When a temp table is created DDL is executed.  There is overhead in this process.   When the temp table goes out of scope SQL Server drops the table. If a temp table is created within the context of a stored procedure, SQL Server does something a bit different.  Rather than drop the table, it leaves an empty copy of the table behind.  This empty copy is reused in subsequent executions of the stored procedure.

What is required for tempdb caching to occur?

Tempdb caching occurs automatically as long as you follow a couple  rules.

1. Caching will only occur for temp tables created in a stored procedure.  This does not apply to adhoc queries even if they are parameterized.
2. Caching will not occur if DDL is performed on the temp table following its initial creation.
3. A drop of a temp table (despite it being DDL) will not prevent caching.  Nor does a truncate.

How to view cached temp tables

Cached temp tables can be spotted by querying sys.obects in tempdb.

SELECT * FROM tempdb.sys.objects WHERE name LIKE '#%' and type='U'

You can see which are cached (and not currently in use) by the name. When a temp table goes into use the name changes.

The name of a temp table not in use will have a will have a # followed by a hex value (#A8BA2F14).  A temp table in use will have a # followed by the name of the table as it is defined in the stored procedure (#mytestable_____…).  This doesn’t differentiate between a cached temp table in use and a non cached temp table in use.

In the example above there are two cached temp tables not in use and two in use.  So what happens if more than one call to the stored procedure is made concurrently?  More temp tables are cached – in the screenshot above this is what is occurring in rows 2 and 4.  Additionally, the create_date column indicates when the temp table was first cached.  Each time the cached temp table is used, the modify_date column is updated.

Something important to note.  When a temp table that is not cached goes out of scope it will not disappear immediately.  There is usually a few second delay before the temp table disappears from sys.objects.

Examples of what will and won’t cache

The most basic example of a stored procedure that will cache temp tables is as follows:
CREATE PROCEDURE [dbo].[usp_thisCachesTemp] AS create table #mytesttable(id int,
name nvarchar(40))

Here’s a change which will cause this to not cache:

CREATE PROCEDURE [dbo].[usp_thisDoesntCacheTemp] AS
create table #mytesttable(id int not null,
name nvarchar(40))
ALTER TABLE #mytesttable ADD primary key clustered (id);

The addition of a primary key to the table after it has been defined causes this to not cache.  If we include the definition of the primary key in the initial creation of the table this will cache:

CREATE PROCEDURE [dbo].[usp_thisCachesTempAlso] AS
create table #mytesttable(id int,
name nvarchar(40), PRIMARY KEY(id))

If you need more than one index on the temp table it would generally require more than one DDL statement to do this.  In the past it wasn’t possible to creating a caching temp table in this scenario; however, in SQL 2014 a new feature was added to DDL syntax.  Starting in SQL 2014 you can define more than one index in a create table command.
CREATE PROCEDURE [dbo].[usp_thisCachesTempWithTwoIndexes] AS
create table #mytesttable(id int,
name nvarchar(40),
INDEX ix_name NONCLUSTERED (name),
PRIMARY KEY(id))

As a result of being able to define multiple indexes in a single CREATE table command you can now create multiple indexes and have a temp table that will cache.

Parting thoughts

Does temp table caching help all workloads?  No.  I’ve generally seen it help highly transactional workloads with lots of concurrent activity.  After tuning tempdb by increasing files there is sometimes still too much concurrent activity to avoid some bottlenecks.  What I presented here are some simple coding practices to keep in mind that can help reduce the chance of tempdb contention.