This article covers SQL Server 2005/2008, and focuses on the most common ways to work with TEMPDB, not including every potential option.

 What Objects are Stored in TEMPDB?

TEMPDB is used to store three different categories of temporary data:

I.                        User Objects

II.                        Internal Objects

III.                        Version Stores

I.                   User Objects

• Local and global temporary tables (and indexes if created)

• User-defined tables and indexes

• Table variables

II.                Internal Objects

• Work tables for DBCC CHECKDB and DBCC CHECKTABLE.
• Work tables for hash operations, such as joins andaggregations.
• Work tables for processing static or keyset cursors.
• Work tables for processing Service Broker objects.
• Work files needed for many GROUP BY, ORDER BY, UNION,
• SORT, and SELECT DISTINCT operations.
• Work files for sorts that result from creating or rebuilding
• indexes (SORT_IN_TEMPDB).
• ·         The version store is a collection of pages used to store row level versioning of data.
• There are two types of version stores:

III.             Version Stores

1. I.         Common Version Store: Examples include:

–      Triggers.

–      Snapshot isolation and read-committed snapshot isolation.

–       MARS (when multiple active result sets are used).

1. II.      Online-Index-Build Version Store:

–      Used for online index builds or rebuilds. EE edition only.

Some Important definitions related to TempDB

Snapshot Isolation: Specifies that data read by any statement in a transaction will be transactionally consistent with the data that existed at the start of the transaction. Data modifications made by other transactions after the start of the transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction gets a  snapshot of the committed data as it existed at the start of the transaction.

• Read-Committed Snapshot Isolation: Row versioning is used to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the

data from updates by other transactions.

• Multiple Active Results Sets (MARS): Allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection.

Some more Important concepts related to TemDb (That makes TempDb different in nature from other databases on SQL Server)

• TEMPDB is cleared every time the SQL Server service is stopped and restarted.*
• By default, TEMPDB creates an MDF file of 8MB and an LDF file of 1MB.
• By default, autogrowth is set to grow by 10% with an unlimited MDF file size, and a 2TB maximum LDF file size.
• Each SQL Server instance may have only one TEMPDB, although TEMPDB may have multiple physical files within a single filegroup.
• Many TEMPDB database options can’t be changed (e.g. Database Read-Only, Auto Close, Auto Shrink).
• TEMPDB only uses the simple recovery model.
• TEMPDB may not be backed up, restored, be mirrored, have database snapshots made of it, or have many DBCCcommands run against it.

• TEMPDB may not be dropped, detached, or attached.

• TEMPDB logging works differently from regular logging.
• Operations are minimally logged, as redo information is not included, which reduces TEMPDB transaction log activity. The log is truncated constantly during the automatic checkpoint process, and should not grow significantly, although it can grow with long-running transactions, or if disk I/O is bottlenecked.
• If a TEMPDB log file grows wildly:
  • Check for long-running transactions (and kill them if necessary).
  • Check for I/O bottlenecks (and fix them if possible).
  • Manually running a checkpoint can often temporally reduce a wildly growing log file if bottle-necked disk I/O is the problem.

 TempDb Execution plans concepts

• When a query execution plan is cached, user-created temporary objects in TEMPDB that are required by the plan are often cached as well. This is called temporary object reuse.
• Obviously, not the entire object is cached, just a portion of it. When a temporary object is cached, up to nine pages are cached for reuse.
• This improves the performance of the next execution of the query as the object already partially exists, but this also takes up space in TEMPDB.
• If the system is low on memory, the Database Engine removes the execution plan and drops the cached temporary objects

Further more, The SQL Server system database, tempdb, has undergone a number of changes in SQL Server 2005. There are new tempdb usages and internal optimizations in SQL Server 2005; tempdb architecture is mostly unchanged since SQL Server 2000.

The tempdb system database is very similar to a user database. The main difference is that data in tempdb does not persist after SQL Server shuts down.

Each time SQL Server restarts, tempdb is copied from the model database. It inherits certain database configuration options, such as ALLOW_SNAPSHOT_ISOLATION, from the model database.

Only one file group in tempdb is allowed for data and one file group for logs. You can configure the size of the files. When auto grow is enabled (which is the default), the file grows until the disk volume is full. When the server restarts, the tempdb file size is reset to the configured value (the default is 8 MB). Auto grow is temporary for tempdb (unlike other types of databases). It is reset when SQL Server restarts.

Users can explicitly create and use tables in tempdb. Transactions can be used to modify data in the tables. Transactions can be rolled back. However, there is no need to REDO them because the contents of tempdb do not persist across SQL Server restarts. Because the transaction log does not need to be flushed, transactions are committed faster in tempdb than in user databases. In a user database, transactions have the ACID attributes: atomicity, concurrency, isolation, and durability. In tempdb, transactions lose the durability attribute.

SQL Server uses tempdb to store internal objects such as the intermediate results of a query. Most of these internal operations on tempdb do not generate log records because there is no need to roll back. So, these operations are faster.

For more details please refer to this link : http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Thanks,
Web Editor