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 contention may also occur between processes on the tempdb system catalogs.

SQL Server 2008 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 is rebuilt ...

Get Microsoft® SQL Server 2008 R2 Unleashed now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.