Tempdb Allocation Contention in SQL Server

Not able to access YouTube? Watch this video on MSDN Channel 9!

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.

Attend our upcoming SQL Server Performance Tuning Hands-on Workshop in Munich! Or check out other workshop locations ~

Subscribe to our Newsletter