Episode
Tempdb Metadata Contention in SQL Server - Table Variable Vs Temporary Table
In SQL Server, the concurrent creation of temporary tables from many sessions can lead to tempdb metadata contention. Tempdb metadata contention does not affect the concurrent creation of table variables.
When SQL Server creates temporary tables, it has to update metadata information in the system based tables, like sys.sysschobjs (like PAGELATCH_EX and PAGELATCH_SH waits). This overhead is not there for table variables. Tempdb metadata management overhead leads to faster table variable declaration than temporary table creation, which is demonstrated in the below video. The tempdb metadata management overhead associated with temporary table is due to the temporary table having different scope than table variable where it is limited to the batch of stored procedure. Concurrent creation of temporary tables from many sessions will lead to concurrent update of metadata information in the system based tables, which leads to tempdb metadata contention.
Below video with hands-on example demonstrates the tempdb metadata 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.
In SQL Server, the concurrent creation of temporary tables from many sessions can lead to tempdb metadata contention. Tempdb metadata contention does not affect the concurrent creation of table variables.
When SQL Server creates temporary tables, it has to update metadata information in the system based tables, like sys.sysschobjs (like PAGELATCH_EX and PAGELATCH_SH waits). This overhead is not there for table variables. Tempdb metadata management overhead leads to faster table variable declaration than temporary table creation, which is demonstrated in the below video. The tempdb metadata management overhead associated with temporary table is due to the temporary table having different scope than table variable where it is limited to the batch of stored procedure. Concurrent creation of temporary tables from many sessions will lead to concurrent update of metadata information in the system based tables, which leads to tempdb metadata contention.
Below video with hands-on example demonstrates the tempdb metadata 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.
Have feedback? Submit an issue here.