Temp Table Caching: Why Some Temp Tables Get Reused

A procedure can create the same temporary table repeatedly without paying every setup cost again. Temp table caching preserves reusable object structures. It doesn't preserve the previous caller's rows or guarantee that the next estimate fits new data.

Temp Table Caching Keeps the Object, Not Its Rows

Temporary object caching lets SQL Server retain eligible temporary table structures after their creating procedure finishes. A later execution can reuse that structure. The caller's data isn't retained as business rows for the next execution.

This is an engine optimization for object setup and allocation work. It is separate from plan caching, even though both can affect the same stored procedure.

I explain the empty-container idea before discussing performance. A cached table isn't an application cache of last week's results. It still needs to be populated for the current request.

Its scope rules also remain intact. One session doesn't gain access to another session's local temporary data. Keep these distinctions clear so a useful storage optimization doesn't become an accidental design assumption in procedure logic.

Create the Table Inside the Procedure

Temporary tables created inside stored procedures are candidates for caching. Keep the table definition stable and declare supported indexes with the initial definition where appropriate. The sample uses an unnamed primary key.

Run it in a disposable database with permission to create procedures. The synthetic values demonstrate scope and execution. They don't establish measured allocation savings on your instance.

CREATE PROCEDURE starts its own batch. GO separates the test calls from that definition. Execute the procedure repeatedly under representative workload when evaluating caching.

A few calls show the code works logically, but they don't provide a meaningful contention measurement. The plan and object caches have separate lifecycles, so preserve the surrounding settings and workload conditions in the test notes.

CREATE PROCEDURE dbo.TempCacheDemo
    @MinimumId int
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #Work(ItemId int NOT NULL PRIMARY KEY, Amount decimal(19,4) NOT NULL);
    INSERT #Work VALUES (1,10),(2,20),(3,30);
    SELECT ItemId,Amount FROM #Work WHERE ItemId >= @MinimumId;
END;
GO
EXEC dbo.TempCacheDemo @MinimumId = 1;
EXEC dbo.TempCacheDemo @MinimumId = 2;

Avoid What Blocks Temp Table Caching

DDL performed after creation can prevent caching for a temporary table. Creating or altering its structure later adds that barrier. Named constraints are another blocker.

Temporary objects created inside dynamic SQL or sp_executesql aren't cached through this stored-procedure mechanism. Those choices can still be necessary. Understand the trade instead of rewriting working SQL solely to qualify for an optimization.

I review post-creation index statements before suggesting a cache-friendly change. A supported inline index definition can avoid separate DDL. The resulting index still needs to serve the workload.

Don't preserve a poor index merely to retain caching. A deliberate table change can cost setup work while saving much more during joins. Compare the complete procedure rather than one internal feature.

Read the Counters as Measurements

The General Statistics performance object includes Temp Tables Creation Rate and Temp Tables For Destruction. The first is a rate counter reported through performance infrastructure. A raw cntr_value in sys.dm_os_performance_counters isn't always a ready-to-read per-second number.

Sample rate counters over an interval and account for counter type. The destruction value reports objects waiting for cleanup, not a count of cache hits.

The following query lists values and their types without altering the system. Instance names differ for named instances, so the object filter matches the suffix. The column is padded nchar, so the pattern needs a trailing wildcard too. Without it, my named instance returned no rows. Save the timestamp with each sample.

Don't claim that one snapshot proves caching is enabled for a particular table. The counters describe aggregate activity. Other procedures and sessions can contribute to the same values during your test.

SELECT SYSUTCDATETIME() AS SampleTime,counter_name,cntr_value,cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%:General Statistics%'
  AND counter_name IN (N'Temp Tables Creation Rate',N'Temp Tables For Destruction');

On my test instance, I sampled the counters, called the demo procedure ten more times, and sampled again. The raw creation value didn’t move, so the procedure reused its cached #Work table instead of creating a new one.

Compare Two Controlled Procedure Shapes

To investigate a suspected blocker, create separate procedure versions in a test database. Keep the same input and logical result. Change only the relevant table-definition step.

Compare setup activity and the procedure's full resource use under repeated execution. Don't compare an eligible version with an unrelated query and attribute every difference to object reuse. The experiment needs a narrow variable.

Inspect tempdb waits and application throughput when allocation contention is the actual concern. Caching can reduce repeated setup work, but it doesn't remove tempdb data writes or sorting requirements. A procedure loading large detail sets still performs that load.

A reusable tray helps with preparation. It doesn't carry the dishes to the table or wash the data for you.

Review Statistics on the Reused Object

Statistics associated with cached temporary objects can survive in ways that influence later estimates. The next request can load a very different distribution. Normal statistics update and recompilation rules still apply, but don't assume every execution begins with perfectly tailored statistics.

Read estimated and actual rows for the statements consuming the temporary table. A caching benefit can coexist with an estimation problem.

Use representative small, large, and skewed inputs during testing. Consider an explicit statistics update or targeted statement recompile only when the plan evidence supports it. Those steps have their own cost.

Don't add them reflexively to every procedure. Keep the chosen remedy attached to the unstable estimate and the workload pattern it addresses. Temp table caching and estimate accuracy require separate evidence.

Preserve Scope and Error Handling

Local temporary tables created by the procedure follow their usual lifetime and visibility rules. Nested calls and dynamic SQL introduce additional scope considerations. A caching discussion doesn't change those rules.

Keep cleanup and error behavior correct regardless of whether the engine reuses an object. The procedure should produce the intended result under either physical path without relying on a hidden cached state.

What changes most between calls, the amount of data or its distribution? That answer directs the statistics review. Also check concurrent execution, not only sequential calls from one window.

The optimization has to help the workload under its real session pattern. A benchmark consisting of one procedure call at a time can miss the allocation pressure that motivated the investigation.

Use Temp Table Caching as a Supported Optimization

Keep temporary definitions stable where the workload permits. Avoid named constraints and unnecessary later DDL when they add no value. Retain necessary dynamic behavior when it serves the application.

Then compare representative execution with counters, plans, and resource evidence. Temp table caching is useful when reducing setup work matters. It isn't a reason to compromise the result or conceal a poor estimate.

Record the procedure versions and test conditions with your findings. Revisit after changing temporary indexes or moving creation into dynamic SQL. Those edits can alter eligibility without changing the displayed result.

The procedure needs an operational review at that boundary. Reusable storage is helpful. A correct and predictable query remains the reason for the temporary table.

Related reading on this blog: Dropping Temp Table in Stored Procedure: SQL in Sixty Seconds #124 and Temp Table vs Table Variable: Cardinality Estimation.

A cached temp table is not cached business data, it is reusable storage for the next execution.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.