A Basic Introduction To SQL Server TEMPDB

TEMPDB database is a very well organised system database, is an integral part of the SQL server engine and is a global resource for all users connected to the SQL server instance.  TEMPDB is very similar then user databases but the differences are that TEMPDB does not persist after a SQL server shutdown. Every time SQL server is restarted TEMPDB is re-created from the model database. The other difference is that only one file group is allowed for TEMPDB data files and log files.

TEMPDB is used for many operations in SQL server namely:

  • The creation of temporary local and global tables
  • Temporary procedures
  • Internal objects
    • Hashed sorts
    • Sorts
    • Hash tables
    • Version store (Online index build version store and common version store)
    • After Triggers
    • Online Index Operations
    • Trigger Virtual tables
    • Query Spills
    • MARS (Multiple Active Result Sets)

There are also a few restrictions that apply to TEMPBDB namely:

  • Auto Shrink operations are not allowed in TEMPB because many of the hidden objects cannot be moved by shrink operations. Hidden TEMPDB objects are Meta data about internal objects.
  • Database Checksum options cannot be enabled.
  • Database snapshots cannot be created on TEMPDB.
  • Only off-line checking of tables is supported.
  • Backup and Restore of TEMPDB database.
  • Adding file groups.
  • Change collation.
  • Dropping the database.
  • Changing the database owner. TEMPDB is owned by DBO.
  • Dropping the guest user from the database.
  • Enabling change data capture.
  • Participating in database mirroring.
  • Removing the primary file group, primary data file, or log file.
  • Renaming the database or primary file group.
  • Setting the database to OFFLINE.
  • Setting the database or primary file group to READ_ONLY.

Microsoft has made numerous architectural changes to TEMPDB since Microsoft SQL Server 2000. The changes were geared towards optimizing TEMPDB and its internal usage.  Since there is only 1 TEMPDB per SQL server engine it can quickly become a bottleneck.  Below are a few changes Microsoft has made, especially since the release of Microsoft SQL Server 2005.

  • Improved caching for temporary objects. SQL Server caches 1 IAM page and 1 data page with the associated query plan. If the same plan is executed multiple times, the temporary table only needs to be created once, this reduces DDL contention. Temp objects are only cached when none of the following conditions are violated:
    • Named constraints are not created.
    • DDL statements are not executed after the temporary table is created for example: creating a non-clustered index on the temporary table.
    • TEMPDB object is not created using dynamic SQL.
  • Updates to internal objects generate no log records.
  • Page allocations on internal objects do not generate log records unless on a sort unit.
  • Inserts in the version store doesn’t generate log records.
  • There is less usage of the UP page latch when allocating pages and extents; this will reduce contention on the FPS, GAM and SGAM.
  • Proportional fill has been optimized to reduce UP latch contention.
  • Deferred drop of TEMPDB objects.  When large objects are being dropped a background task takes care of the operation and the application doesn’t have to wait for the ‘drop’ process to finish.
  • Work table caching is improved.
  • Log pages do not need to be flushed when transactions are committed because SQL logs only ‘undo’ operations of TEMPDB and not ‘redo’ operations.

This was just a basic blog regarding some TEMPDB internals. Hope you enjoyed it.


http://technet.microsoft.com/en-us/library/ms190768 (v=sql.110).aspx



PASS Summit 2012: Tempdb_Performance_and_Manageability byRobert L Davis

Author: Wynand