Introduction
This article explains how to optimize Temporary Table caching in SQL Server 2014. We will first get an explanation of what a Temp Table is. SQL Server provides Temporary Tables that helps the developer in a good way. These tables may be created at runtime and will do every kind of operations that traditional tables can do. But, supported the table types, the scope is limited. These tables are created within the "tempdb" database. To identify a table as a Temporary Table within the SQL statement, prefix the table name with the "#" character.
Now let's move on to this articles topic of how to improve Temporary Table caching in SQL Server 2014.
Due to some preconditions SQL Server is in a position to cache Temporary Tables. Caching tables implies that SQL Server doesn't need to recreate them, when you are creating a similar Temporary Table over and over. This can improve the throughout tremendously, as a result of SQL Server not accessing specialized pages in memory (PFS, GAM, SGAM), that might result in Latch competition during high utilization. One of the necessities of Temporary Table Caching is that you just can't combine DML with DDL statements in a Stored Procedure.
Here I write a Stored Procedure, that have both DDL and DML statements.
In the example given below, you are using a DDL statement (CREATE UNIQUE CLUSTERED INDEX) to make an association in an index, which suggests you're mixing DDL with DML statements. For that reason SQL Server isn't ready to cache your Temporary Table.
Example
CREATE PROCEDURE BuildTempTable
AS
BEGIN
--Create a New Temp table
CREATE TABLE #TempTable
(
Column1 INT IDENTITY(1,1),
Column2 VARCHAR(50),
Column3 CHAR(500)
)
-- Create a unique clustered index on the previous created temp table
CREATE UNIQUE CLUSTERED INDEX index1 ON #TempTable(Column1)
-- insert 15 records
DECLARE @i INT=0
WHILE(@i<15)
BEGIN
INSERT INTO #TempTable values('Sharad','Delhi')
SET @i += 1
END
END
GO
Output
![exec-stored-procedure.jpg]()
You can prove that behavior (SQL Server isn't ready to cache your Temporary Table) by pursuing the performance counter Temporary Tables Creation Rate from from the DMV sys.dm_os_performance_counters as within the following example:
Example
DECLARE @Table_Counter_Before_Test BIGINT;
SELECT @Table_Counter_Before_Test = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
DECLARE @i INT = 0
WHILE (@i < 1000)
BEGIN
EXEC BuildTempTable
SET @i += 1
END
DECLARE @Table_Counter_After_Test BIGINT;
SELECT @Table_Counter_After_Test = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
PRINT 'Temp tables created during the test: ' + CONVERT(VARCHAR(100), @Table_Counter_After_Test - @Table_Counter_Before_Test)
GO
When you are running that code, SQL Server must produce a thousand individual Temporary Tables, as you'll be able to see from the output window in SQL Server Management Studio.
Output
![table-creation-rate-in-sql.jpg]()
It's terribly simple to beat that drawback by imposing the UNIQUE CLUSTERED INDEX with the PRIMARY KEY constraint. By the help of giving the example SQL Server is finally able to cache the Temporary Table.
Example
ALTER PROCEDURE BuildTempTable
AS
BEGIN
CREATE TABLE #TempTable
(
Column1 INT IDENTITY(1,1) PRIMARY KEY, -- CREATES A unique clustered index
Column2 VARCHAR(50),
Column3 CHAR(500)
)
DECLARE @i INT =0
WHILE(@I<=15)
BEGIN
INSERT INTO #TempTable VALUES('Sharad','Delhi')
SET @i += 1
END
END
GO
Output
![exec-stored-procedure.jpg]()
Now, let's again test "performance counter Temporary Tables Creation Rate", and this time, SQL Server creates the Temporary Table only once and reuses it.
![table-creation-rate1-in-sql.jpg]()
ref-http://www.sqlpassion.at/