tempdb Database

THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.
  • Internal objects that are created by the SQL Server Database Engine. These include:

    • Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
    • Work files for hash join or hash aggregate operations.
    • Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.

    Note

    Each internal object uses a minimum of nine pages; one IAM page and one eight-page extent. For more information about pages and extents, see Pages and Extents.

  • Version stores, which are a collection of data pages that hold the data rows that are required to support the features that use row versioning. There are two version stores: a common version store and an online-index-build version store. The version stores contain the following:

    • 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. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Physical Properties of tempdb

The following table lists the initial configuration values of the tempdb data and log files, which are based on the defaults for the Model database. The sizes of these files may vary slightly for different editions of SQL Server.

File Logical name Physical name Initial size File growth
Primary data tempdev tempdb.mdf 8 megabytes Autogrow by 64 MB until the disk is full
Secondary data files* temp# tempdb_mssql_#.ndf 8 megabytes Autogrow by 64 MB until the disk is full
Log templog templog.ldf 8 megabytes Autogrow by 64 megabytes to a maximum of 2 terabytes

* The number of files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.

Note

The default value for the number of data files is based on the general guidelines in KB 2154845.

Moving the tempdb Data and Log Files

To move the tempdb data and log files, see Move System Databases.

Database Options

The following table lists the default value for each database option in the tempdb database and whether the option can be modified. To view the current settings for these options, use the sys.databases catalog view.

Database option Default value Can be modified
ALLOW_SNAPSHOT_ISOLATION OFF Yes
ANSI_NULL_DEFAULT OFF Yes
ANSI_NULLS OFF Yes
ANSI_PADDING OFF Yes
ANSI_WARNINGS OFF Yes
ARITHABORT OFF Yes
AUTO_CLOSE OFF No
AUTO_CREATE_STATISTICS ON Yes
AUTO_SHRINK OFF No
AUTO_UPDATE_STATISTICS ON Yes
AUTO_UPDATE_STATISTICS_ASYNC OFF Yes
CHANGE_TRACKING OFF No
CONCAT_NULL_YIELDS_NULL OFF Yes
CURSOR_CLOSE_ON_COMMIT OFF Yes
CURSOR_DEFAULT GLOBAL Yes
Database Availability Options ONLINE

MULTI_USER

READ_WRITE
No

No

No
DATE_CORRELATION_OPTIMIZATION OFF Yes
DB_CHAINING ON No
ENCRYPTION OFF No
MIXED_PAGE_ALLOCATION OFF No
NUMERIC_ROUNDABORT OFF Yes
PAGE_VERIFY CHECKSUM for new installations of SQL Server.

NONE for upgrades of SQL Server.
Yes
PARAMETERIZATION SIMPLE Yes
QUOTED_IDENTIFIER OFF Yes
READ_COMMITTED_SNAPSHOT OFF No
RECOVERY SIMPLE No
RECURSIVE_TRIGGERS OFF Yes
Service Broker Options ENABLE_BROKER Yes
TRUSTWORTHY OFF No

For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

Restrictions

The following operations cannot be performed on the tempdb database:

  • Adding filegroups.
  • Backing up or restoring the database.
  • Changing collation. The default collation is the server collation.
  • Changing the database owner. tempdb is owned by sa.
  • Creating a database snapshot.
  • Dropping the database.
  • Dropping the guest user from the database.
  • Enabling change data capture.
  • Participating in database mirroring.
  • Removing the primary filegroup, primary data file, or log file.
  • Renaming the database or primary filegroup.
  • Running DBCC CHECKALLOC.
  • Running DBCC CHECKCATALOG.
  • Setting the database to OFFLINE.
  • Setting the database or primary filegroup to READ_ONLY.

Permissions

Any user can create temporary objects in tempdb. Users can only access their own objects, unless they receive additional permissions. It is possible to revoke the connect permission to tempdb to prevent a user from using tempdb, but this is not recommended as some routine operations require the use of tempdb.

Optimizing tempdb performance

The size and physical placement of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server.

If possible, use database instant file initialization to improve the performance of data file grow operations.

Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. This prevents tempdb from expanding too frequently, which can affect performance. The tempdb database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.

Data files should be of equal size within each filegroup, as SQL Server uses a proportional-fill algorithm that favors allocations in files with more free space. Dividing tempdb into multiple data files of equal size provides a high degree of parallel efficiency in operations that use tempdb.

Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance.

To check current tempdb size and growth parameters, use the following query:

SELECT name AS FileName, 
   size*1.0/128 AS FileSizeinMB,
   CASE max_size 
       WHEN 0 THEN 'Autogrowth is off.'
       WHEN -1 THEN 'Autogrowth is on.'
       ELSE 'Log file will grow to a maximum size of 2 TB.'
   END,
   growth AS 'GrowthValue',
   'GrowthIncrement' = 
       CASE
           WHEN growth = 0 THEN 'Size is fixed and will not grow.'
           WHEN growth > 0 AND is_percent_growth = 0 
               THEN 'Growth value is in 8-KB pages.'
           ELSE 'Growth value is a percentage.'
       END
FROM tempdb.sys.database_files;
GO

Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks. Individual or groups of tempdb data files do not necessarily need to be on different disks or spindles unless you are also encountering I/O bottlenecks.

Put the tempdb database on disks that differ from those that are used by user databases.

Performance improvements in tempdb

Starting with SQL Server 2016, tempdb performance is further optimized in the following ways:

  • Temporary tables and table variables are 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 the Database Engine Configuration section and a command line parameter /SQLTEMPDBFILECOUNT. By default, setup will add as many tempdb data files as the logical processor 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.

Capacity Planning for tempdb

Determining the appropriate size for tempdb in a production environment depends on many factors. As described previously in this topic, these factors include the existing workload and the SQL Server features that are used. We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

  • Set autogrow on for tempdb.
  • Execute individual queries or workload trace files and monitor tempdb space use.
  • Execute index maintenance operations, such as rebuilding indexes and monitor tempdb space.
  • Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.

How to Monitor tempdb use

Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used in the tempdb files:

-- Determining the Amount of Free Space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages], 
 (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
-- Determining the Amount Space Used by the Version Store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
 (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;
-- Determining the Amount of Space Used by Internal Objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
 (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;
-- Determining the Amount of Space Used by User Objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
 (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb. For more information, see the next section.

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id, 
 SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
 SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
FROM sys.dm_db_task_space_usage 
GROUP BY session_id;
-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
 R1.internal_objects_alloc_page_count 
 + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
 R1.internal_objects_dealloc_page_count 
 + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1 
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count, 
 R1.internal_objects_dealloc_page_count;;

SORT_IN_TEMPDB Option For Indexes
System Databases
sys.databases (Transact-SQL)
sys.master_files (Transact-SQL)
Move Database Files

See Also

Working with tempdb in SQL Server 2005
Troubleshooting Insufficient Disk Space in tempdb