When creating temporary tables within stored procedures with indexes, there is an opportunity for performance gains. SQL Server will cache the DDL of temporary tables as long as there are no DDL changes after the creation. As confusing as that may sound, it will make more sense by looking at the code. The following are two procedures, one to create a non-cached table and the other cached.
CREATE PROCEDURE dbo.NoncachedTempTable
AS
/****************************************************************************************
Object Description:
Create a noncached temp table and enter 10000 random numbers then drop
Revision History:
Date Name Label/PTS Description
----------- --------------- ---------- ----------------------------------------
05/21/2017 Dan Andrews PTS: 00000 Initial Release
****************************************************************************************/
BEGIN
CREATE TABLE #TempTable(ID INT IDENTITY
, randomVal INT);
CREATE UNIQUE CLUSTERED INDEX idx_NoncachedTempTable ON #TempTable(ID);
-- Itzik Ben Gan's Row_Number/CTE solution with Random Number Generator
WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
INSERT INTO #TempTable (randomVal)
SELECT ROUND(10000 * RAND(CONVERT( VARBINARY , NEWID())) , 0)
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Nbrs) D(n)
WHERE n <= 10000;
DROP TABLE #TempTable
END;
GO
CREATE PROCEDURE dbo.CachedTempTable
AS
/****************************************************************************************
Object Description:
Create a cached temp table and enter 10000 random numbers then drop
Revision History:
Date Name Label/PTS Description
----------- --------------- ---------- ----------------------------------------
05/21/2017 Dan Andrews PTS: 00000 Initial Release
****************************************************************************************/
BEGIN
CREATE TABLE #TempTable(ID INT IDENTITY
PRIMARY KEY CLUSTERED
, randomVal INT);
-- Itzik Ben Gan's Row_Number/CTE solution with Random Number Generator
WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
INSERT INTO #TempTable (randomVal)
SELECT ROUND(10000 * RAND(CONVERT( VARBINARY , NEWID())) , 0)
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Nbrs) D(n)
WHERE n <= 10000;
DROP TABLE #TempTable
END;
GO
Run the following query for the test:
DBCC FREEPROCCACHE;
SELECT
domcc.name
, domcc.type
, domcc.entries_count
, dopc.OBJECT_NAME
, dopc.counter_name
, dopc.cntr_value
FROM
sys.dm_os_memory_cache_counters AS domcc
, sys.dm_os_performance_counters AS dopc
WHERE
domcc.type = N'CACHESTORE_TEMPTABLES'
AND
dopc.counter_name = 'Temp Tables Creation Rate'
AND
dopc.OBJECT_NAME = 'SQLServer:General Statistics';
DBCC FREEPROCCACHE;
GO
EXEC NoncachedTempTable;
GO 100
SELECT
domcc.name
, domcc.type
, domcc.entries_count
, dopc.OBJECT_NAME
, dopc.counter_name
, dopc.cntr_value
FROM
sys.dm_os_memory_cache_counters AS domcc
, sys.dm_os_performance_counters AS dopc
WHERE
domcc.type = N'CACHESTORE_TEMPTABLES'
AND
dopc.counter_name = 'Temp Tables Creation Rate'
AND
dopc.OBJECT_NAME = 'SQLServer:General Statistics';
DBCC FREEPROCCACHE;
GO
EXEC CachedTempTable;
GO 100
SELECT
domcc.name
, domcc.type
, domcc.entries_count
, dopc.OBJECT_NAME
, dopc.counter_name
, dopc.cntr_value
FROM
sys.dm_os_memory_cache_counters AS domcc
, sys.dm_os_performance_counters AS dopc
WHERE
domcc.type = N'CACHESTORE_TEMPTABLES'
AND
dopc.counter_name = 'Temp Tables Creation Rate'
AND
dopc.OBJECT_NAME = 'SQLServer:General Statistics';
DBCC FREEPROCCACHE;
GO
The results of which are:
We can see that on this server, there were 133 temp tables to start with (new VM). After the first batch of testing, there were 100 temp tables created and none of them were cached. This is logical because we ran the proc 100 times, it created 100 temp tables. The next batch also ran 100 times, but only one temp table was created and it was cached for the other executions. It didn’t have to allocate space for this table.