Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Temporary Table Caching


By:   |   Read Comments (5)   |   Related Tips: More > System Databases

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


Problem

When local or global temporary tables are created or dropped, its metadata are inserted or removed from the tempdb system catalog. If there is a very high rate of workload trying to create and drop temporary tables, this can lead to Data Definition Language (DDL) contention in the tempdb system catalog and throttle the workload throughput.

This tip will describe the condition to allow caching of temporary table and a demonstration to benchmark the performance between a cached vs. non-cached temporary table.

Solution

Caching of a temporary table is a feature available since SQL Server 2005. If certain conditions are met, the temporary table metadata will still remain in the tempdb system catalog when the user request has completed its task. From the user's perspective, the temporary table is no longer accessible as if the temporary table was dropped. But when a user session calls the same routine again that creates the temporary table, SQL Server internally can reuse the temporary table created earlier.

The conditions to allow caching of temporary table are:

  • Named constraints are not created
  • DDL statements that affect the table are not run after the temporary table has been created, such as the CREATE INDEX or CREATE STATISTICS statements
  • Temporary table is not created by using dynamic SQL
  • Temporary table is created inside another object, such as a stored procedure or trigger

Developers could write efficient queries, but because the temporary table caching feature works internally in SQL Server, it is not easily noticeable unless there is an awareness of this built-in feature and the condition to allow caching of a temporary table.

DDL Performance Between Cached vs. Non-Cached Temporary Table in SQL Server

The demonstration will be using a stored procedure which contains DDL only to benchmark the performance difference. This is also an example use case where caching of a temporary table can provide significant performance improvement on workloads that loop to process large amounts of records concurrently in a few batches. This tip is not stating the example is a good or bad design, but the focus is on demonstrating the behavior of temporary table caching.

All the T-SQL written in this tip is executed on SQL Server 2016 Enterprise RTM.

Two stored procedures are created with DDL definitions only as below:

USE tempdb
GO

CREATE PROCEDURE dbo.pr_NewTempTable
AS
BEGIN
 SET NOCOUNT ON;

 CREATE TABLE #T (A INT IDENTITY(1,1), val varchar(20))

 CREATE UNIQUE CLUSTERED INDEX AA ON #T (A)

END
GO

CREATE PROCEDURE dbo.pr_CachedTempTable
AS
BEGIN
 SET NOCOUNT ON;

 CREATE TABLE #T (A INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, val varchar(20))

END
GO

Query SQL Server sys.dm_os_performance_counters DMV to get the Temp Tables Creation Rate counter. SQL Server service was restarted prior to this test so that counter is showing 0.

SELECT * FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Temp Tables Creation Rate' 
AND OBJECT_NAME = 'SQLServer:General Statistics' 
GO 


Query SQL Server sys.dm_os_performance_counters DMV to get the Temp Tables Creation Rate counter value of 0

Open a new query session and execute the stored procedure dbo.pr_NewTempTable 100 times and check the Temp Tables Creation Rate counter again. The counter has incremented by 100 as a result of temp tables created from each of the dbo.pr_NewTempTable executions. The temporary table is not cached in this stored procedure due to DDL statement on the temporary table.

EXEC tempdb.dbo.pr_NewTempTable
GO 100

SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
AND OBJECT_NAME = 'SQLServer:General Statistics'      
GO


Query SQL Server sys.dm_os_performance_counters DMV to get the Temp Tables Creation Rate counter value of 100

Execute the stored procedure dbo.pr_CachedTempTable 100 times and this time SQL Server will show the Temp Tables Creation Rate has only incremented by one, because the temp table was cached and reused.

EXEC tempdb.dbo.pr_CachedTempTable
GO 100

SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Temp Tables Creation Rate'
AND OBJECT_NAME = 'SQLServer:General Statistics'      
GO


Query SQL Server sys.dm_os_performance_counters DMV to get the Temp Tables Creation Rate counter value of 101

Assume stored procedure pr_CachedTempTable is executed concurrently at the same time in three different query sessions, then the Temp Tables Creation Rate counter will increase by three. If at any point in time the concurrent execution of pr_CachedTempTable is equal or less than three, then you will not see the counter Temp Tables Creation Rate increase. So, SQL Server will automatically create additional cached temporary tables to cater for concurrent requests.

To illustrate tempdb DDL contention, we will open three new query sessions and start the execution of dbo.pr_NewTempTable 10,000 times under each session around the same time. The same test is repeated for dbo.pr_CachedTempTable and the ad-hoc query below.

-- Ad-Hoc Query
SET NOCOUNT ON;
DECLARE @i int = 1

WHILE @i <= 10000
BEGIN
 CREATE TABLE #T (A INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, val varchar(20))
 DROP TABLE #T

 SET @i += 1
END

Below is a screenshot of dbo.pr_NewTempTable execution across three query sessions. All three sessions execution were started around the same time to simulate concurrent execution.

dbo.pr_NewTempTable execution across three query sessions

Below are the execution duration captured for the execution of dbo.pr_NewTempTable, dbo.pr_CachedTempTable and the ad-hoc query. DDL performance for cached temporary tables obviously has a noticeable shorter duration compared to a temporary table which is not cached.

 

Run#1

Run#2 Run#3
EXEC tempdb.dbo.pr_NewTempTable 61 seconds 53 seconds 58 seconds
EXEC tempdb.dbo.pr_CachedTempTable 20 seconds 21 seconds 22 seconds
Ad-hoc Query 65 seconds 55 seconds 65 seconds

Summary

DDL contention occurs when a high number of threads trying to access and update the SQL Server tempdb system catalog due to creation and dropping of temporary tables. This should not be confused with tempdb DML contention which typically relates to contention in PFS, SGAM or GAM pages.

In the demonstration, the temporary table pr_NewTempTable was unable to cache the temporary table, because it was mixing DDL with DML. By replacing the DDL "CREATE UNIQUE CLUSTERED INDEX" with the "PRIMARY KEY" constraint, the DDL becomes an inline statement and SQL Server was able to cache the temporary table.

However, not all DDL prevents the caching of a temporary table. An exception is the "DROP TABLE" DDL which still allows a temporary table to be cached. This means if there was an explicit "DROP TABLE #T" specified in pr_CachedTempTable, SQL Server would still be able to cache and reuse temporary table #T.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Simon Liew Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Masterís Degree in Distributed Computing.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, September 01, 2016 - 9:13:47 AM - Simon Liew Back To Top

Hi Thomas,

I did a simple test and in 2 scenarios where temp table is cached and is not cached, the temp table statistics was based on the first execution. So, parameter sniffing issue can be present in both cases.

Below are the steps to test 1 scenario on temp table which is not cached but can be easily repeated for cached temp table. Both scenarios indicate the same behaviour.

 

Step 1

SET NOCOUNT ON

CREATE TABLE T (val VARCHAR(20) DEFAULT 'aaa')

GO

INSERT INTO T DEFAULT VALUES

GO 1000

INSERT INTO T VALUES ('b')

GO 2

 

CREATE PROCEDURE dbo.pr_NewTempTable

@val varchar(20)

AS

BEGIN

 SET NOCOUNT ON;

 CREATE TABLE #T (A INT IDENTITY(1,1), val varchar(20))

 CREATE UNIQUE CLUSTERED INDEX AA ON #T (A)

 INSERT INTO #T (val) SELECT val FROM T

  SELECT * FROM #T WHERE val = @val

END

GO

 

Step 2

Turn on Show Execution plan

Exec dbo.pr_NewTempTable 'aaa'

Exec dbo.pr_NewTempTable 'B'

 

You'll see that the temp table statistics on the "Estimated Number of Rows" is based on the first execution even when the creation of temp table increment by 1 for each execution. So, workload involved really needs to be reviewed because the issue could be a combination of any scenario. 

Aside from this, I just wanted to state that parameter sniffing is often misunderstood. Parameter sniffing works similarly to procedure cache. It is a good feature to have because it allows SQL Server to use the input parameter to get the statistics histogram for optimization rather than using density vector. There's pros and cons to keep sniffing or disable sniffing by obfuscation the parameter with a variable. Then again, similar to procedure cache, caching a proc plan is a good until the query plan is deem "unsafe" because it should've been recompile each and everytime. 

My 2 cents.


Thursday, September 01, 2016 - 3:12:06 AM - Thomas Franz Back To Top

you should mention that the temp table caching could lead to parameter sniffing problems.

When the number of records in the #temptable differs highly (sometimes just one row, sometimes 100k) it creates - when the caching "occurs" only one execution plan that could lead to 100k nested lookups when the the first procedure call was with a parameter that leads to only one row in the temp table.


Friday, August 19, 2016 - 7:44:48 AM - Scott Crosby Back To Top

Interesting article, Simon. The following 2 MSDN Channel 9 videos on this topic are also good to watch:
https://channel9.msdn.com/Series/SQL-Workshops/Tempdb-Metadata-Contention-in-SQL-Server-Table-Variable-Vs-Temporary-Table
https://channel9.msdn.com/Series/SQL-Workshops/Temp-Table-Caching-in-SQL-Server


Saturday, August 13, 2016 - 9:21:02 AM - Simon Liew Back To Top

Hi Hank,

CTE is not stored in tempdb as an object. It only refers to the underlying tables similar to a derived table. If you're seeing CTE uses tempdb in query plan, it might be the internal objects (i.e. eager spool, etc) and internal objects are not stored in metadata tables. So, CTE would not cause DDL contention in tempdb.

Could you be referring to table variable instead?


Friday, August 12, 2016 - 8:05:20 PM - Hank Freeman Back To Top

 Just curios how does the use of CTE complicate the above discovery and the of the Tempdb catalog.

I was under the impression that CTE uses Tempdb for storage just like a Temp table...

 end.


Learn more about SQL Server tools