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.
Check out www.sqlvideo.com for additional hands-on videos.
Subscribe to our newsletter: https://newsletter.sqlworkshops.com