Temporary table caching in SQL Server

Caching of Temporary table is an interesting topic but not appreciated/recognized one in SQL Server due to several reasons.

First off, Let us see whats caching of temporary tables? – Caching temporary objects like temp tables/table variables will improve the performance of execution by eliminating the
recreation of objects.There are certain operations which will prevent the caching of temporary objects are below.

1. DDL commands once the object is created
2. Recompilation of Procedure associated
3. Dynamic SQL is used to create the objects
4. Named Constraints etc

Today, we are going to see how DDL commands is preventing the caching of temp objects.

Lets Create a Procedure and create a temp object:


--Create a procedure 
create Procedure Test_TempCaching
as
Begin
	create Table #Temp (EmpId int, FirstName varchar(50), LastName Varchar(50))
	create clustered index ix_test on #Temp(EmpId)
	Insert into #Temp Values(1,'SQL','Server'),(2, 'Biz','Talk')
End

We are going to see the temp tables creation rate using the below query:


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'

The above will result a cumulative information on the number of times the temp objects created.

For the first time, when we create the procedure and executing the procedure, it will create a plan for the procedure and temp object is being created and cached.
If you look at the procedure, A temp table is created and later the object has been modified with DDL to add an index on the temp table.

Every time, the procedure gets executed, you can see the value gets changed,
Use the below query multiple times, and see the difference.


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'
EXEC  Test_TempCaching
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'


The above behavior shows that, if there is any DDL applied after the temp object, then the object will become obsolete and evict from the cache(later) and create a new object. You may find a performance issue if the procedure is executing very frequently in your application.

Now, Let us modify the procedure to comine the DDL command inline at the creation of temporary table as below:


ALTER Procedure Test_TempCaching
as
Begin
	create Table #Temp (EmpId int, FirstName varchar(50), LastName Varchar(50), Primary Key(EmpID))
	Insert into #Temp Values(1,'SQL','Server'),(2, 'Biz','Talk')

End

If you execute for the first time, you can execute the value gets changed. This is because, for the first time, the plan from the cache will be deleted once the procedure altered and a new plan will be created for the procedure. But for subsequent execution, the cntr_value never gets changed as the object has already been cached.


SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'
EXEC  Test_TempCaching
SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Temp Tables Creation Rate'

Conclusion:
If you see more high temp tables creation rate in your application, you may find the procedures and see if you can take advantage of the above scenario.
There is a caveat – statistics with temp objects(I am not covering here),you may need to verify and confirm the changes thoroughly to make sure you do not have any other performance issues.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s