Faster Temp Table Caching in SQL Server 2014

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

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

GO

Output

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)

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

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

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

Output

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.

ref-http://www.sqlpassion.at/