Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
This article describes the tempdb
system database, a global resource available to all users connected to a Database Engine instance in SQL Server, Azure SQL Database, or Azure SQL Managed Instance.
The tempdb
system database is a global resource that holds:
User objects that are explicitly created. They include:
User objects that can be created in a user database can also be created in tempdb
, however they are created without a durability guarantee, and are dropped when the Database Engine instance restarts.
Internal objects that the database engine creates. They include:
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.
Version stores, which are collections of data pages that hold the data rows that support row versioning. There are two types: a common version store and an online index build version store. The version stores contain:
READ COMMITTED
or SNAPSHOT
isolation transactions.AFTER
triggers.Operations within tempdb
are minimally logged. tempdb
is re-created every time the Database Engine is started so that the system always starts with an empty tempdb
database. Temporary stored procedures and local temporary tables are dropped automatically when the session that created them disconnects.
tempdb
never has anything to be saved from one uptime period of the Database Engine to another. Backup and restore operations are not allowed on tempdb
.
The following table lists the initial configuration values of the tempdb
data and log files in SQL Server. The values are based on the defaults for the model
database. The sizes of these files might 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 |
All tempdb
data files should always have the same initial size and growth parameters.
Depending on the version of the Database Engine, its configuration, and the workload, tempdb
might require multiple data files to mitigate allocation contention.
The recommended total number of data files depends on the number of logical processors on the machine. As general guidance:
tempdb
allocation contention is still observed, increase the number of data files by multiples of four until the contention decreases to acceptable levels, or make changes to the workload.For more information, see Recommendations to reduce allocation contention in SQL Server tempdb database.
To check current size and growth parameters for tempdb
, use the sys.database_files catalog view in tempdb
.
To move the tempdb
data and log files, see Move system databases.
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 |
---|---|---|
ACCELERATED_DATABASE_RECOVERY |
OFF |
No |
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 |
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN) |
OFF |
No |
CHANGE_TRACKING |
OFF |
No |
COMPATIBILITY_LEVEL |
Depends on the Database Engine version. For more information, see ALTER DATABASE (Transact-SQL) compatibility level. |
Yes |
CONCAT_NULL_YIELDS_NULL |
OFF |
Yes |
CONTAINMENT |
NONE |
No |
CURSOR_CLOSE_ON_COMMIT |
OFF |
Yes |
CURSOR_DEFAULT |
GLOBAL |
Yes |
Database state | ONLINE |
No |
Database update | READ_WRITE |
No |
Database user access | MULTI_USER |
No |
DATE_CORRELATION_OPTIMIZATION |
OFF |
Yes |
DB_CHAINING |
ON |
No |
DELAYED_DURABILITY |
DISABLED Regardless of this option, delayed durability is always enabled on tempdb . |
Yes |
ENCRYPTION |
OFF |
No |
MIXED_PAGE_ALLOCATION |
OFF |
No |
NUMERIC_ROUNDABORT |
OFF |
Yes |
PAGE_VERIFY |
CHECKSUM for new installations of SQL ServerExisting PAGE_VERIFY value might be retained when an instance of SQL Server is upgraded in place. |
Yes |
PARAMETERIZATION |
SIMPLE |
Yes |
QUOTED_IDENTIFIER |
OFF |
Yes |
READ_COMMITTED_SNAPSHOT |
OFF |
No |
RECOVERY |
SIMPLE |
No |
RECURSIVE_TRIGGERS |
OFF |
Yes |
Service Broker | ENABLE_BROKER |
Yes |
TARGET_RECOVERY_TIME |
60 | Yes |
TEMPORAL_HISTORY_RETENTION |
ON |
Yes |
TRUSTWORTHY |
OFF |
No |
For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).
In Azure SQL Database, some aspects of tempdb
behavior and configuration are different from SQL Server.
For single databases, each database on a logical server has its own tempdb
. In an elastic pool, tempdb
is a shared resource for all databases in the same pool but temporary objects created by one database are not visible to other databases in the same elastic pool.
Objects in tempdb
, including catalog views and dynamic management views (DMVs), are accessible via a cross-database reference to the tempdb
database. For example, you can query the sys.database_files view:
SELECT file_id,
type_desc,
name,
size,
max_size,
growth
FROM tempdb.sys.database_files;
Global temporary tables in Azure SQL Database are database-scoped. For more information, see Database scoped global temporary tables in Azure SQL Database.
To learn more about tempdb
sizes in Azure SQL Database, review:
In Azure SQL Managed Instance, some aspects of tempdb
behavior and default configuration are different from SQL Server.
You can configure the number of tempdb
files, their growth increments, and their maximum size. For more information on configuring tempdb
settings in Azure SQL Managed Instance, see Configure tempdb settings for Azure SQL Managed Instance.
Azure SQL Managed Instance supports temporary objects in the same way as SQL Server, where all global temporary tables and global temporary stored procedures are accessible by all user sessions within the same SQL managed instance.
To learn more about tempdb
sizes in Azure SQL Managed Instance, review resource limits.
To learn more about tempdb
sizes in SQL database in Microsoft Fabric, review the resource limits section in Feature comparison: Azure SQL Database and SQL database in Microsoft Fabric.
Similarly to Azure SQL Database, global temporary tables in SQL database in Microsoft Fabric are database-scoped. For more information, see Database scoped global temporary tables in Azure SQL Database.
The following operations can't be performed on the tempdb
database:
tempdb
is owned by sa.DBCC CHECKALLOC
.DBCC CHECKCATALOG
.OFFLINE
.READ_ONLY
.Any user can create temporary objects in tempdb
.
Users can access only their own non-temporary objects in tempdb
, unless they receive additional permissions.
It's possible to revoke the CONNECT
permission on tempdb
to prevent a database user or role from using tempdb
. This isn't recommended because many operations require the use of tempdb
.
The size and physical placement of tempdb
files can affect performance. For example, if the initial size of tempdb
is too small, time and resources might be taken up to autogrow tempdb
to the size required to support the workload every time the Database Engine instance is restarted.
tempdb
files by setting the file size to a value large enough to accommodate the typical workload in the environment. Preallocation prevents tempdb
from autogrowing too often, which can negatively affect performance.tempdb
database should be set to autogrow to provide space during unplanned growth events.tempdb
into multiple data files of equal size can improve efficiency of operations that use tempdb
.
To check current size and growth parameters for tempdb
, use the following query:
SELECT name AS file_name,
type_desc AS file_type,
size * 8.0 / 1024 AS size_mb,
max_size * 8.0 / 1024 AS max_size_mb,
CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
CASE WHEN growth = 0 THEN growth
WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
WHEN growth > 0 AND is_percent_growth = 1 THEN growth
END
AS growth_increment_value,
CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
WHEN growth > 0 AND is_percent_growth = 0 THEN 'Megabytes'
WHEN growth > 0 AND is_percent_growth = 1 THEN 'Percent'
END
AS growth_increment_value_unit
FROM tempdb.sys.database_files;
Put the tempdb
database on a fast I/O subsystem. Individual data files or groups of tempdb
data files don't necessarily need to be on different disks unless you're encountering disk-level I/O bottlenecks.
If there is I/O contention between tempdb
and user databases, put tempdb
files on disks that differ from the disks that user databases use.
Note
To improve performance, delayed durability is always enabled on tempdb
even if the database option DELAYED_DURABILITY
is set to DISABLED
. Because tempdb
is recreated at startup, it doesn't go through a recovery process and doesn't provide a durability guarantee.
UP
(update) latches that are used.tempdb
is reduced to reduce disk I/O bandwidth consumption on the tempdb
log file.tempdb
data files during a new instance installation. Review the recommendations and configure your tempdb
in the Database Engine Configuration page of SQL Setup, or use the command-line parameter /SQLTEMPDBFILECOUNT
. By default, SQL Setup adds as many tempdb
data files as the number of logical processors or eight, whichever is lower.tempdb
data files, all files autogrow at the same time and by the same amount, depending on growth settings. Trace flag 1117 is no longer required. For more information, read -T1117 and -T1118 changes for TEMPDB and user databases.tempdb
use uniform extents. Trace flag 1118 is no longer required. For more information on performance improvements in tempdb
, see the blog article TEMPDB - Files and Trace Flags and Updates, Oh My!.AUTOGROW_ALL_FILES
property is always turned on for the PRIMARY
filegroup.tempdb
file allocation. SQL Setup warns customers if the initial file size is set to a value greater than 1 GB and if instant file initialization is not enabled, preventing instance startup delays.tempdb
sizing based on the version store usage requirement per database.tempdb
utilization.FILE_FLAG_WRITE_THROUGH
option when opening tempdb
files to allow for maximum disk throughput. Since tempdb
is recreated on startup, this option isn't needed to provide data durability. For more information on FILE_FLAG_WRITE_THROUGH
, see Logging and data storage algorithms that extend data reliability in SQL Server.tempdb
.tempdb
. This improvement changes the concurrency management of PFS page updates so that they can be updated under a shared latch, rather than an exclusive latch. This behavior is on by default in all databases (including tempdb
) starting with SQL Server 2019 (15.x). For more information on PFS pages, read Under the covers: GAM, SGAM, and PFS pages.tempdb
data files, based on the number of logical cores (with up to eight data files). This doesn't apply to in-place minor or major version upgrades. Each tempdb
data file is 8 MB, with an auto growth of 64 MB. This behavior is similar to the default SQL Server installation on Windows.tempdb
. For more information on GAM and SGAM pages, read Under the covers: GAM, SGAM, and PFS pages. For more information, watch System Page Latch Concurrency Enhancements (Ep. 6) | Data Exposed.Temporary object metadata contention has historically been a bottleneck to scalability for many SQL Server workloads. To address that, SQL Server 2019 (15.x) introduced a feature that's part of the in-memory database feature family: Memory-optimized TempDB metadata.
Enabling the Memory-optimized TempDB metadata feature removes this bottleneck for workloads previously limited by temporary object metadata contention inside tempdb
. Starting with SQL Server 2019 (15.x), the system tables involved in managing temporary object metadata can become latch-free, non-durable, memory-optimized tables.
Tip
Because of current limitations, we recommend enabling Memory-optimized TempDB metadata only when object metadata contention occurs and significantly impacts your workloads.
The following diagnostic query returns one or more rows if temporary object metadata contention is occurring. Each row represents a system table, and returns the number of sessions contending for access to that table at the time when this diagnostic query is executed.
SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;
Watch this seven-minute video for an overview of how and when to use Memory-optimized TempDB metadata feature:
Note
Currently, the Memory-optimized TempDB metadata feature is not available in Azure SQL Database, SQL database in Microsoft Fabric, and Azure SQL Managed Instance.
The following sections include steps to enable, configure, verify, and disable the Memory-optimized TempDB metadata feature.
To enable this feature, use the following script:
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
For more information, see ALTER SERVER. This configuration change requires a restart of the service to take effect.
You can verify whether or not tempdb
is memory-optimized by using the following T-SQL command:
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
If the returned value is 1 and a restart has occurred after enabling the feature, then the feature is enabled.
If the server fails to start for any reason after you enable Memory-optimized TempDB metadata, you can bypass the feature by starting the Database Engine instance with minimal configuration using the -f
startup option. You can then disable the feature and remove the -f
option to restart the Database Engine in normal mode.
To protect the server from potential out-of-memory conditions, we recommend that you bind tempdb
to a resource governor resource pool that limits the memory consumed by Memory-optimized TempDB metadata. The following sample script creates a resource pool and sets its maximum memory to 20%, enables resource governor, and binds tempdb
to the resource pool.
This example uses 20% as the memory limit for demonstration purposes. The optimal value in your environment might be larger or smaller depending on your workload, and can change over time if the workload changes.
CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);
ALTER RESOURCE GOVERNOR RECONFIGURE;
ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON
(RESOURCE_POOL = 'tempdb_resource_pool');
This change also requires a service restart to take effect, even if Memory-optimized TempDB metadata is already enabled.
To verify that tempdb
is bound to a resource pool and to monitor memory usage statistics for the pool, use the following query:
WITH resource_pool AS
(
SELECT p.pool_id,
p.name,
p.max_memory_percent,
dp.max_memory_kb,
dp.target_memory_kb,
dp.used_memory_kb,
dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
rp.name AS resource_pool_name,
rp.max_memory_percent,
rp.max_memory_kb,
rp.target_memory_kb,
rp.used_memory_kb,
rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';
To remove the resource pool binding while keeping Memory-optimized TempDB metadata enabled, execute the following command and restart the service:
ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
To disable Memory-optimized TempDB metadata, execute the following command and restart the service:
ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;
Enabling or disabling the Memory-optimized TempDB metadata feature requires a restart.
In certain cases, you might observe high memory usage by the MEMORYCLERK_XTP
memory clerk causing out-of-memory errors in your workload.
To see memory usage by the MEMORYCLERK_XTP
clerk relative to all other memory clerks and relative to the target server memory, execute the following query:
SELECT SUM(IIF(type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
SUM(IIF(type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
FROM sys.dm_os_memory_clerks
CROSS JOIN sys.dm_os_sys_info;
If MEMORYCLERK_XTP
memory is high, you can mitigate the problem as follows:
tempdb
database to a resource pool that limits memory consumption by Memory-optimized TempDB metadata. For more information, see Configure and use memory-optimized tempdb metadata.MEMORYCLERK_XTP
memory that is no longer needed. For more information, see sys.sp_xtp_force_gc (Transact-SQL).For more information, see memory-optimized tempdb metadata (HkTempDB) out of memory errors.
When you use In-Memory OLTP, a single transaction is not allowed to access memory-optimized tables in more than one database. Because of this, any read or write transaction that involves a memory-optimized table in a user database can't also access tempdb
system views in the same transaction. If this occurs, you receive error 41317:
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.
This limitation also applies to other scenarios where a single transaction attempts to access memory-optimized tables in more than one database.
For example, you might get error 41317 if you query the sys.stats catalog view in a user database that contains memory-optimized tables. This happens because the query attempts to access statistics data on a memory-optimized table in the user database and the memory-optimized metadata in tempdb
.
The following example script produces this error when Memory-optimized TempDB metadata is enabled:
BEGIN TRAN;
-- Create an In-memory OLTP transaction that accesses a system view in tempdb
SELECT name
FROM tempdb.sys.tables;
-- An attempt to create an In-memory OLTP transaction in the user database fails
INSERT INTO <user database>.<schema>.<memory-optimized table>
VALUES (1);
COMMIT TRAN;
Note
This limitation does not apply to temporary tables. You can create a temporary table in the same transaction that accesses a memory-optimized table in a user database.
Queries against system catalog views always use the READ COMMITTED
isolation level. When the Memory-optimized TempDB metadata is enabled, queries against system catalog views in tempdb
use the SNAPSHOT
isolation level. In either case, locking hints are not honored.
Columnstore indexes can't be created on temporary tables when Memory-optimized TempDB metadata is enabled.
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.Determining the appropriate size for tempdb
depends on many factors. These factors include the workload and the Database Engine features that are used.
We recommend that you analyze tempdb
space consumption by performing the following tasks in a test environment where you can reproduce your typical workload:
tempdb
files. All tempdb
data files should have the same initial size and autogrow configuration.tempdb
space use.tempdb
space.tempdb
accordingly.Running out of disk space in tempdb
can cause significant disruptions and application downtime. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the space used in the tempdb
files.
For example, the following example script finds:
tempdb
(not considering free disk space that might be available for tempdb
growth)SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;
To monitor 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 help you identify queries, temporary tables, or table variables that are using large amounts of tempdb
space.
For example, use the following example script to obtain the tempdb
space allocated and deallocated 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;
Use the following example script to find the tempdb
allocated and currently consumed space by internal and user objects for each session and request, for both running and completed tasks:
WITH tempdb_space_usage AS
(
SELECT session_id,
request_id,
user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
NULL AS request_id,
user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
COALESCE(request_id, 0) AS request_id,
SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Configure SQL Server resources for optimal performance - Training
Configure SQL Server resources for optimal performance