Monday, August 24, 2009

A Quick Intorduction to tempdb

TempDB is one of system databases to manage the SQL Server instance. Following is a quick introduction to tempdb:

  • SQL Server uses tempdb to store internal objects such as the intermediate results of a query
  • tempdb does not persist after SQL Server shuts down
  • Each time SQL Server restarts, tempdb is copied from the model database
  • Only one file group in tempdb is allowed for data and one file group for logs
  • Auto grow is temporary for tempdb (unlike other types of databases). It is reset when SQL Server restarts
  • . In a user database, transactions have the ACID attributes: atomicity, concurrency, isolation, and durability. In tempdb, transactions lose the durability attribute
  • Auto shrink is not allowed for tempdb
  • The database CHECKSUM option cannot be enabled.
  • A database snapshot cannot be created on tempdb.
  • DBCC CHECKALLOC and DBCC CHECKCATALOG are not supported.
  • Only offline checking for DBCC CHECKTABLE is performed. This means that a TAB-S lock is needed. There are internal consistency checks that occur when tempdb is in use. If these checks fail, the user connection is broken and the tempdb space used by the connection is freed.

Which objects occupy tempdb space

The following types of objects can occupy tempdb space:

  • Internal objects
  • Version stores
  • User objects

Internal objects are used:

  • To store intermediate runs for sort.
  • To store intermediate results for hash joins and hash aggregates.
  • To store XML variables or other large object (LOB) data type variables. The LOB data type includes all of the large object types: text, image, ntext, varchar(max), varbinary(max), and all others.
  • By queries that need a spool to store intermediate results.
  • By keyset cursors to store the keys.
  • By static cursors to store a query result.
  • By Service Broker to store messages in transit.
  • By INSTEAD OF triggers to store data for internal processing.

Must remember that:

  • Updates to internal objects do not generate log records
  • Each internal object occupies at least nine pages (one IAM page and eight data pages) in tempdb

Version store

  • Version stores are used to store row versions generated by transactions for features such as snapshot isolation, triggers, MARS (multiple active result sets), and online index build
  • Inserts into the version store do not generate log records
  • INSTEAD OF triggers do not generate versions

User objects

  • Operations on user objects in tempdb are mostly logged.
  • Bulk copy program (BCP), bulk insert, SELECT INTO, and index rebuild operations are bulk logged. This is exactly the same as in other databases with the recovery model set to simple

tempdb Space Requirements

It is difficult to estimate the tempdb space requirement for an application.

But it is recommend that you always have a safety factor of about 20% more space

No comments:

Post a Comment

All suggestions are welcome