Improved Temp Table Caching in SQL Server 2014

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Under some preconditions SQL Server is able to cache Temp Tables. Caching Temp Tables means that SQL Server doesn’t have to recreate them, when you are creating the same Temp Table over and over again. This can improve the throughout of your workload tremendous, because SQL Server doesn’t has to access specialized pages in memory (PFS, GAM, SGAM), which would lead to Latch Contention under a high workload. Paul White has an excellent blog posting which describes that behavior in *more* detail ;-)

One of the requirements of Temp Table Caching is that you can’t mix DML with DDL statements in a Stored Procedure. Imagine the following code:

Here you are creating through a DDL statement (CREATE UNIQUE CLUSTERED INDEX) an index, which means you are mixing DDL with DML statements. For that reason SQL Server is not able to cache your Temp Table. You can prove that behavior by tracking the performance counter Temp Tables Creation Rate from from the DMV sys.dm_os_performance_counters as in the following example:

When you are running that code, SQL Server has to create 1000 individual Temp Tables, as you can see from the output window in SQL Server Management Studio:

It’s very easy to overcome that problem by enforcing the UNIQUE CLUSTERED INDEX with the PRIMARY KEY constraint. In that way you are not mixing DDL with DML statements anymore and SQL Server is finally able to cache your Temp Table.

When you rerun the code from above which tracks the relevant performance counter, you can now see that SQL Server creates the Temp Table only once and reuses it:

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. Look at the following code:

As you can see I have created 2 additional Non-Clustered Indexes on the Temp Table directly during the creation of the Temp Table itself. This again means we are not mixing DDL with DML statements, and SQL Server is again able to cache and reuse your Temp Table:

That’s a very nice addition and positive side effect by defining indexes inline on Temp Tables in SQL Server 2014.

What do you think about that new feature? Please feel free to leave a comment.

Thanks for reading

-Klaus

Do you like this post? Why not share it with the SQL Server community?

Comments

  1. Very good article, like it.

    cheers
    Sunil