SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Check out http://www.sqlvideo.com for additional hands-on videos.

Subscribe to our newsletter: https://newsletter.sqlworkshops.com

Ramesh Meyyappan’s SQL Server Performance Tuning Blog

Ramesh Meyyappan (www.sqlworkshops.com) is a SQL Server specialist with expertise in Performance Tuning. Ramesh worked at Microsoft Corporation from 1994 to 2004. Nearly half of that time he worked in Redmond in the development teams - specifically as Program Manager in the SQL Server Development Team responsible for optimizing SQL Server product for SAP. Ramesh now offers onsite and offsite consulting and workshops independently as well as by partnering with various Microsoft Subsidiaries. LinkedIn Profile: http://de.linkedin.com/in/rmeyyappan.

Comments

Leave a comment on the original post [blog.sqlworkshops.com, opens in a new window]

Loading comments...