Importance of Caching Temporary Tables in SQL Server
What a difference!
Why is it necessary to cache temporary tables?
When there are create/alter/drop(DDL) operations running, all these operations need to acquire a PAGELATCH_EX on system object pages. Since whenever DDL operations run, they need to check and insert metadata into system objects, needing a latch on system objects.
Under normal circumstances, when there a certain number of these DDL operations, they can easily acquire a latch.
But when there is a high level of activity with many operations running concurrently, it causes contention on system objects, causing metadata contention, which has an impact on performance.
In order to reduce this contention, it is a good practice to cache the temporary objects, so number of PAGELATCH_EXs are reduced.
This contention does not usually occur on user databases since there are only a very few create/alter/drop user tables operations happening when compared to temp tables on TempDB.
How to cache the temporary objects?
Don’t alter temp table DDL statements after table creation
Create indexes inline with table definition
Create temp objects within SP, trigger, function
Table size must be smaller than 8 MB
Following above rules help tables to get cached so they can be reused.
Here is an example of how caching helps:
Create a stored procedure to create a temp table with 3 columns and an index inline with the table definition.
Code borrowed from Klaus Aschenbrenner and modified - link below in references
Let’s check for the following in the query so the temp table can be cached.
Temp table DDL is not altered after its creation.
Table is wrapped inside the stored procedure.
This helps with temp tables to get cached when stored procedure is executed
Run the perfmon counter query from SSMS to find out how many temp tables get created when above SP is run in a loop for 10 times where each iteration executes for 100 times.
From the results in screenshot, there is only 1 temp table created and for all the iterations, that same table has been reused since it was able to cache it.
Let’s alter the temp table DDL by creating an index outside of the table definition, which means it won’t get cached and can’t be reused.
Run the perfmon counters again to see how many temp tables are created after the temp table DDL is being altered as part of the SP. (Modification of SP has no effect, but modification of temp table after its creation will prevent the table from caching).
Here you observe that a temp table is created for each iteration, almost 1000 new temp tables. This is because it was not able to cache the temp table. Imagine the number of PAGELATCH_EX it must have waited on to create the tables every time and the amount of resources used. In earlier case, it is noted that only one temp table is created and it is being reused for all iterations as it was able to cache that one temp table. So it is important to write queries that can help cache the temporary objects which reduces contention on TempDB.
Summary: Temporary objects must be cached so it can help reduce PAGELATCH contention on TempDB. Regardless of the version, follow the rules to cache the temporary objects:
Don’t alter temp table DDL statements after table creation
Create indexes inline with table definition
Create temp objects within SP, trigger, function
Table size must be smaller than 8 MB
Note: This helps with server level performance, but if individual query level performance is a big deal for you, then you don’t have to worry about caching.
Resources:
https://www.sqlservercentral.com/blogs/improved-temp-table-caching-in-sql-server-2014
Pro SQL Server Internals by Dmitri Korotkevitch