Inside Microsoft® SQL Server™ 2005

tempdb Considerations

Remember that temporary tables and table variables are physically stored in tempdb. SQL Server also stores data in tempdb for many implicit activities that take place behind the scenes. Examples for such activities include: spooling data as part of an execution plan of a query, sorting, and maintaining row versions (SQL Server 2005). You realize that tempdb can become a bottleneck, and you should give it focused tuning attention so that it will accommodate the workload against your server.

Here are some important points you should consider when tuning tempdb:

• In systems where tempdb is heavily used (explicitly or implicitly), consider placing tempdb on its own disk array, and not on the same drives where other databases are ...