Microsoft® SQL Server 2012 Unleashed

Temporary Table Performance Tips

All users in SQL Server share the same tempdb database for work tables and temporary tables, regardless of the database in which they are working. This makes tempdb a potential performance bottleneck in any multiuser system. The primary bottleneck in tempdb is disk I/O, but locking or latch contention may also occur between processes on the tempdb system catalogs.

SQL Server 2012 alleviates the disk I/O problem by logging just enough information to allow rollback of transactions without logging all the additional information that would be necessary to recover those transactions. The recovery information is needed only when recovering a database at system startup or when restoring from a backup. Because tempdb ...