Tempdb Allocation Contention in SQL Server

Play Tempdb Allocation Contention in SQL Server
Sign in to queue


In SQL Server, the concurrent creation of temporary objects (temporary tables and table variables) from many sessions can lead to tempdb allocation contention. This contention occurs on PFS and SGAM pages in tempdb (like PAGELATCH_EX and PAGELATCH_SH waits). It is recommended to create additional data files for tempdb and implement trace flag 1118 to reduce this type of contention. Creating temporary objects, in a stored procedure will lead to temp table caching, this can also help reduce tempdb allocation contention.

After addressing Tempdb Allocation Contention, you will most probably encounter Tempdb Metadata Contention when you are using temporary tables (not when you are using table variables due to Temp Table Caching).

Below video with hands-on example demonstrates the tempdb allocation contention and explains the steps to reduce contention. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.


SQL Server



Right click to download this episode

The Discussion

Add Your 2 Cents