Performance Improvements in SQL 2014(3/4)

Hi dears, so for we have seen about the incremental update statistics and index enhancement on our previous blog post.

Now we will see about, how SQL server 2014 can cache temp tables.  Before we move further into the subject, will see about temp tables in crisp. These are the tables created at the run time and  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. And also we should know one more fact the caching is not per procedure , the caching is per objects.

SQL Server 2014 has given us some room, by which if we make our temp table in a proper way the temp table can be cached. We will see how this is possible.

-- Create a new stored procedure

CREATE PROCEDURE TempTableCaching

AS

BEGIN

-- Create a new temp table

CREATE TABLE #TempTable

(

Col1 INT IDENTITY(1, 1),

Col2 CHAR(4000),

Col3 CHAR(4000)

)

-- Create a unique clustered index on the previous created temp table

CREATE UNIQUE CLUSTERED INDEX idx_c1 ON #TempTable(Col1)

-- Insert 10 dummy records

DECLARE @i INT = 0

WHILE (@i < 10)

BEGIN

INSERT INTO #TempTable VALUES ('Hi', 'Test data')

SET @i += 1

END

END

GO

Now by the code you are creating DDL statement through the (CREATE UNIQUE CLUSTERED INdex , we )have mixed the  DDL Statement with DML statement. Because of this reason SQL Server was not able to Cache the temp table.

To prove, how many times the temp tables are created we will make of use of the dmv sys.dm_os_performance_counters. Just execute the below code and scroll down to the last to check how many times the temp table was created.

DECLARE @table_counter_before_test BIGINT;

SELECT @table_counter_before_test = cntr_value FROMsys.dm_os_performance_counters

WHERE counter_name = 'Temp Tables Creation Rate'

DECLARE @i INT = 0

WHILE (@i < 1000)

BEGIN

EXEC TempTableCaching

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

You will find the below message the temp tables where created

(1 row(s) affected)
Temp tables created during the test: 1000

Now we will see how we can make the procedure in a perfect way, by which SQL Server can cache tthe temp tables.

ALTER PROCEDURE TempTableCaching

AS

BEGIN

-- Create a new temp table

CREATE TABLE #TempTable

(

Col1 INT IDENTITY(1, 1) PRIMARY KEY, -- This creates also a Unique Clustered Index

Col2 CHAR(100) INDEX idx_Col2,

Col3 CHAR(100) INDEX idx_Col3

)

-- Insert 10 dummy records

DECLARE @i INT = 0

WHILE (@i < 10)

BEGIN

INSERT INTO #TempTable VALUES ('Hi', 'Test data')

SET @i += 1

END

END

GO

Now by the above code we have created the indexes, while we create the table itself. Hence we are not mixing the DDL and DML. Just go back and rerun the code, which checks how many times the temp table was created.

(1 row(s) affected)
Temp tables created during the test: 1

Yes the table was created just once. As a conclusion this also means that SQL Server is not able to cache Temp Tables when you create additional Non-Clustered Indexes, because you are then again mixing DDL with DML statements within your Stored Procedure.

But with SQL Server 2014 you can overcome this limitation, because you are now able to create indexes inline within your CREATE TABLE statement