How to cache Temp Tables in SQL Server

 

    Under some conditions, SQL Server caches the Temp tables. If you can create these specific conditions for SQL Server, you will force SQL Server to cache the temp tables.This can be a big win for your query performance if you create the same temp table over and over again. I am going to use sys.dm_os_performance_counters DMV to demo this concept.

   First of all, we need to create the temp tables in a stored procedure for caching to work. Second requirement is, we can not use DML (Data Manipulation Language) with DDL (Data Definition Language) statements in a stored procedure Let's look at the following example to understand this better.

    This stored procedure creates a temp table that contains an index. By doing that, we are mixing DML with DDL in this example which will cause SQL Server to not cache this temp table. To demo this, I am going to query the sys.dm_os_performance_counters table.

      I have run this query before I run my stored procedure, so you can see the count of Temp Tables before and after. Now I am going to run the stored procedure 10 times, which should create and drop the same temp table 10 times.

    As you can see, counter is increased to 16. There is no caching happening in this stored procedure, SQL Server dropped and re-create this temp table 10 times. To fix this problem, all we need to do is replace the UNIQUE CLUSTERED INDEX line with the PRIMARY KEY constraint. Let's re-write our stored procedure.

      I have removed the INDEX and add PRIMARY KEY constraint to the definition of the table. In this way, we are not mixing the DML and DDL. I am going to run the new stored procedure which should create and drop the temp table 10 times again. Let's see if this temp table is getting cached. First, here is the current count for the temp tables in the system.

Next I am going to run my new stored procedure and check the count of the temp tables in the system again.

   My new stored procedure created/dropped the temp table 10 times again, but SQL Server cached the temp table this time and temp table got created only once in the system. This is an impressive trick and It can save you a lot  of performance. But wait, this is great but that means you can't create indexes in temp tables too. If you have SQL Server 2014 or later versions you can have indexes and SQL Server still can cache the Temp tables. In the newer versions starting with 2014, you can create indexes inline within your CREATE TABLE statement. That means you are not mixing DML and DDL. Here is an example.

      As you can see, I am creating two indexes (myIndex1, myIndex2) in my table. They are inline just like PRIMARY KEY constraint. This stored procedure creates 10 temp table just like others. Let's see if SQL Server can cache this table. Here is my current count of temp tables first.

Now I am ready to run my new stored procedure with indexes.

     

     Ladies and Gentleman It looks like we have a winner here. SQL Server did cached the temp table with Indexes. I am very happy with this result, Little thing can mean a lot and here is a great example for that. This should give us a great performance gain.