tempdb system database is a global resource that is available to all users connected to the instance of SQL Server or connected to SQL Database.
tempdb is used to hold:
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 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.
Each internal object uses a minimum of nine pages; an IAM page and an eight-page extent. For more information about pages and extents, see Pages and Extents.
Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored in
tempdband are scoped to the database level. Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same Azure SQL database. User sessions from other Azure SQL databases cannot access global temporary tables. For more information, see Database scoped global temporary tables (Azure SQL Database). Azure SQL Database Managed Instance) supports the same temporary objects as does SQL Server. For Azure SQL Database single databases and elastic pools, only master database and
tempdbdatabase apply. For more information, see What is an Azure SQL Database server. For a discussion of
tempdbin the context of Azure SQL Database single databases and elastic pools, see tempdb Database in Azure SQL Database single databases and elastic pools. For Azure SQL Database Managed Instance, all system databases apply.
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:
- 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.
tempdb are minimally logged so that transactions can 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
Physical Properties of tempdb in SQL Server
The following table lists the initial configuration values of the
tempdb data and log files in SQL Server, 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 eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight 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.
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 in SQL Server
To move the
tempdb data and log files, see Move System Databases.
Database Options for tempdb in SQL Server
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|
|Database Availability Options||ONLINE
|PAGE_VERIFY||CHECKSUM for new installations of SQL Server.
NONE for upgrades of SQL Server.
|Service Broker Options||ENABLE_BROKER||Yes|
For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).
tempdb database in SQL Database
tempdb sizes for DTU-based service tiers
|Premium Elastic Pools (all DTU configurations)||13.9||12||166.7|
|Standard Elastic Pools (S0-S2)||13.9||12||166.7|
|Standard Elastic Pools (S3 and above)||32||12||384|
|Basic Elastic Pools (all DTU configurations)||13.9||12||166.7|
tempdb sizes for vCore-based service tiers
The following operations cannot be performed on the
- Adding filegroups
- Backing up or restoring the database
- Changing collation. The default collation is the server collation
- Changing the database owner.
tempdbis 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
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 is not recommended as some routine operations require the use of
Optimizing tempdb performance in SQL Server
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 auto growing
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. Preallocation prevents
tempdb from expanding too frequently, which affects 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
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 may have to constantly expand and 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 grows to a maximum size of 2 TB.' END, growth AS 'GrowthValue', 'GrowthIncrement' = CASE WHEN growth = 0 THEN 'Size is fixed.' 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
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.
tempdb database on disks that differ from those that are used by user databases.
Performance improvements in tempdb for SQL Server
Starting with SQL Server 2016 (13.x),
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 to reduce the number of UP (update) latches that are used.
- Logging overhead for
tempdbis reduced to reduce disk I/O bandwidth consumption on the
- Setup adds multiple
tempdbdata 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 adds as many
tempdbdata files as the logical processor count or eight, whichever is lower.
- When there are multiple
tempdbdata files, all files autogrow at same time and by the same amount depending on growth settings. Trace flag 1117 is no longer required.
- All allocations in
tempdbuse 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.
For more information on performance improvements in
tempdb, see the following blog article:
Memory-Optimized tempdb Metadata
Metadata contention in
tempdb has historically been a bottleneck to scalability for many workloads running on SQL Server. SQL Server 2019 (15.x) introduces a new feature that is part of the In-Memory Database feature family, memory-optimized tempdb metadata, which effectively removes this bottleneck and unlocks a new level of scalability for tempdb-heavy workloads. In SQL Server 2019 (15.x), the system tables involved in managing temporary table metadata can be moved into latch-free non-durable memory-optimized tables.
Watch this 7-minute video for an overview of how and when to use memory-optimized tempdb metadata:
In order to opt-in to this new feature, use the following script:
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON
This configuration change requires a restart of the service to take effect.
There are some limitations with this implementation that are important to note:
Toggling the feature on and off is not dynamic. Because of the intrinsic changes that need to be made to the structure of
tempdb, a restart is required to either enable or disable the feature.
A single transaction may not access memory-optimized tables in more than one database. This means that any transactions that involve a memory-optimized table in a user database will not be able to access
tempdbsystem views in the same transaction. If you attempt to access
tempdbsystem views in the same transaction as a memory-optimized table in a user database, you will receive the following error:
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
BEGIN TRAN SELECT * FROM tempdb.sys.tables -----> Creates a user In-Memory OLTP Transaction on tempdb INSERT INTO <user database>.<schema>.<mem-optimized table> VALUES (1) ----> Attempts to create user In-Memory OLTP transaction but will fail COMMIT TRAN
Queries against memory-optimized tables do not support locking and isolation hints, so queries against memory-optimized
tempdbcatalog views will not honor locking and isolation hints. As with other system catalog views in SQL Server, all transactions against system views will be in READ COMMITTED (or in this case READ COMMITTED SNAPSHOT) isolation.
Columnstore indexes cannot be created on temporary tables when memory-optimized tempdb metadata is enabled.
Due to the limitation on columnstore indexes, use of the sp_estimate_data_compression_savings system stored procedure with the COLUMNSTORE or COLUMNSTORE_ARCHIVE data compression parameter is not supported when memory-optimized tempdb metadata is enabled.
These limitations only apply when referencing
tempdb system views, you will be able to create a temp table in the same transaction as you access a memory-optimized table in a user database if desired.
You can verify whether or not
tempdb is memory-optimized by using the following T-SQL command:
If the server fails to start for any reason after enabling memory-optimized tempdb metadata, you can bypass the feature by starting the SQL Server with minimal configuration using the -f startup option. This will enable you to disable the feature and then restart SQL Server in normal mode.
Capacity Planning for tempdb in SQL Server
Determining the appropriate size for
tempdb in a SQL Server production environment depends on many factors. As described previously in this article, 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
- Execute individual queries or workload trace files and monitor
- Execute index maintenance operations, such as rebuilding indexes and monitor
- 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
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;;