Deep into TEMP DB...

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

• Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
• Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
• Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
• Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged.

In SQL Server, tempdb performance is improved in the following ways:

• Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
• Allocation page latching protocol is improved. This reduces the number of UP (update) latches that are used.
• Logging overhead for tempdb is reduced. This reduces disk I/O bandwidth consumption on the tempdb log file.
• Setup adds multiple tempdb data files during a new instance installation. This task can be accomplished with the new UI input control on theDatabase Engine Configuration section and a command line parameter /SQLTEMPDBFILECOUNT. By default, setup will add as many tempdb files as the CPU count or 8, whichever is lower.
• When there are multiple tempdb data files, all files will autogrow at same time and by the same amount depending on growth settings. Trace flag 1117 is no longer required.
• All allocations in tempdb use uniform extents. Trace flag 1118 is no longer required.
• For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

The tempdb database is similar to the operating system paging file. It’s used to hold temporary objects created by users, temporary objects needed by the database engine, and row-version information. The tempdb database is created each time SQL Server is restarted.