--- title: "tempdb Database" description: This article provides details about the configuration and use of the tempdb database in SQL Server, Azure SQL Database, SQL database in Fabric, and Azure SQL Managed Instance. author: WilliamDAssafMSFT ms.author: wiassaf ms.reviewer: randolphwest, dfurman ms.date: 03/12/2025 ms.service: sql ms.topic: conceptual ms.custom: - P360 - ignite-2024 helpviewer_keywords: - "temporary tables [SQL Server], tempdb database" - "tempdb database [SQL Server], about tempdb" - "temporary stored procedures [SQL Server]" - "tempdb database [SQL Server]" monikerRange: "=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric" --- # tempdb database [!INCLUDE [SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB](../../includes/applies-to-version/sql-asdb-asdbmi-fabricsqldb.md)] This article describes the `tempdb` system database, a global resource available to all users connected to a [!INCLUDE[ssde-md](../../includes/ssde-md.md)] instance in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], Azure SQL Database, or Azure SQL Managed Instance. ## Overview The `tempdb` system database is a global resource that holds: - **User objects** that are explicitly created. They include: - Global or local temporary tables and indexes on these tables - Temporary stored procedures - Table variables - Tables returned in table-valued functions - Cursors 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 [!INCLUDE[ssde-md](../../includes/ssde-md.md)] instance restarts. - **Internal objects** that the database engine creates. They 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](../../relational-databases/pages-and-extents-architecture-guide.md#pages-and-extents). - **Version stores**, which are collections of data pages that hold the data rows that support [row versioning](../sql-server-transaction-locking-and-row-versioning-guide.md#Row_versioning). There are two types: 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 row versioning-based `READ COMMITTED` 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. `tempdb` is re-created every time the [!INCLUDE[ssde-md](../../includes/ssde-md.md)] 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 [!INCLUDE[ssde-md](../../includes/ssde-md.md)] to another. Backup and restore operations are not allowed on `tempdb`. ## Physical properties of tempdb in SQL Server The following table lists the initial configuration values of the `tempdb` data and log files in [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. The values are based on the defaults for the `model` database. The sizes of these files might vary slightly for different editions of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. | 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. ### Number of tempdb data files Depending on the version of the [!INCLUDE[ssde-md](../../includes/ssde-md.md)], 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: - If the number of logical processors is less than or equal to eight, use the same number of data files. - If the number of logical processors is greater than eight, use eight data files. - If `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](/troubleshoot/sql/database-engine/performance/recommendations-reduce-allocation-contention). To check current size and growth parameters for `tempdb`, use the [sys.database_files](../system-catalog-views/sys-database-files-transact-sql.md) catalog view in `tempdb`. ### Move the tempdb data and log files in SQL Server To move the `tempdb` data and log files, see [Move system databases](move-system-databases.md). ### 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](../system-catalog-views/sys-databases-transact-sql.md) 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 [!INCLUDE[ssde-md](../../includes/ssde-md.md)] version.

For more information, see [ALTER DATABASE (Transact-SQL) compatibility level](../../t-sql/statements/alter-database-transact-sql-compatibility-level.md). | 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 [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]

Existing `PAGE_VERIFY` value might be retained when an instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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)](../../t-sql/statements/alter-database-transact-sql-set-options.md). ## tempdb in Azure SQL Database In Azure SQL Database, some aspects of `tempdb` behavior and configuration are different from [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. 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](../system-catalog-views/sys-database-files-transact-sql.md) view: ```sql SELECT file_id, type_desc, name, size, max_size, growth FROM tempdb.sys.database_files; ``` Global temporary tables in [Azure SQL Database](/azure/azure-sql/database/sql-database-paas-overview) are database-scoped. For more information, see [Database scoped global temporary tables in Azure SQL Database](../../t-sql/statements/create-table-transact-sql.md#database-scoped-global-temporary-tables-in-azure-sql-database). To learn more about `tempdb` sizes in Azure SQL Database, review: - vCore purchasing model: [single databases](/azure/azure-sql/database/resource-limits-vcore-single-databases), [pooled databases](/azure/azure-sql/database/resource-limits-vcore-elastic-pools) - DTU purchasing model: [single databases](/azure/azure-sql/database/resource-limits-dtu-single-databases#tempdb-sizes), [pooled databases](/azure/azure-sql/database/resource-limits-dtu-elastic-pools#tempdb-sizes) ## tempdb in SQL Managed Instance In Azure SQL Managed Instance, some aspects of `tempdb` behavior and default configuration are different from [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. 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/azure-sql/managed-instance/tempdb-configure?view=azuresql-mi&preserve-view=true). Azure SQL Managed Instance supports temporary objects in the same way as [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], 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](/azure/azure-sql/managed-instance/resource-limits). ## tempdb in SQL database in Fabric To learn more about `tempdb` sizes in [!INCLUDE [fabric-sqldb](../../includes/fabric-sqldb.md)], review the resource limits section in [Feature comparison: Azure SQL Database and SQL database in Microsoft Fabric](/fabric/database/sql/feature-comparison-sql-database-fabric#resource-limits). Similarly to [Azure SQL Database](/azure/azure-sql/database/sql-database-paas-overview), global temporary tables in [[!INCLUDE [fabric-sqldb](../../includes/fabric-sqldb.md)]](/fabric/database/sql/overview) are database-scoped. For more information, see [Database scoped global temporary tables in Azure SQL Database](../../t-sql/statements/create-table-transact-sql.md#database-scoped-global-temporary-tables-in-azure-sql-database). ## Restrictions The following operations can't 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 access only their own non-temporary objects in `tempdb`, unless they receive additional permissions. It's possible to [revoke](../../t-sql/statements/revoke-transact-sql.md) 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`. ## Optimize tempdb performance in SQL Server 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 [!INCLUDE[ssde-md](../../includes/ssde-md.md)] instance is restarted. - If possible, use [instant file initialization](../../relational-databases/databases/database-instant-file-initialization.md) to improve performance of the growth operations for data files. - Starting with [!INCLUDE [SQL Server 2022](../../includes/sssql22-md.md)], transaction log file growth events up to 64 MB can also benefit from instant file initialization. For more information, see [Instant file initialization and the transaction log](database-instant-file-initialization.md#instant-file-initialization-and-the-transaction-log). - 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 autogrowing too often, which can negatively affect performance. - The files in the `tempdb` database should be set to autogrow to provide space during unplanned growth events. - Dividing `tempdb` into multiple data files of equal size can improve efficiency of operations that use `tempdb`. - To avoid data allocation imbalance, data files should have the same initial size and growth parameters because the [!INCLUDE[ssde-md](../../includes/ssde-md.md)] uses a proportional-fill algorithm that favors allocations in files with more free space. - Set the file growth increment to a reasonable size, for example 64 MB, and make the growth increment the same for all data files to prevent growth imbalance. To check current size and growth parameters for `tempdb`, use the following query: ```sql 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](../logs/control-transaction-durability.md) 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. ## Performance improvements in tempdb for SQL Server #### Introduced in [!INCLUDE [sssql16-md](../../includes/sssql16-md.md)] - Temporary tables and table variables are cached. Caching allows operations that drop and create the temporary objects to run very quickly. Caching also reduces page allocation and metadata contention. - The allocation page latching protocol is improved to reduce the number of `UP` (update) latches that are used. - Logging overhead for `tempdb` is reduced to reduce disk I/O bandwidth consumption on the `tempdb` log file. - SQL Setup adds multiple `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. - When there are multiple `tempdb` data files, all files autogrow at the same time and by the same amount, depending on growth settings. [Trace flag 1117](../../t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql.md) is no longer required. For more information, read [-T1117 and -T1118 changes for TEMPDB and user databases](/archive/blogs/psssql/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases). - All allocations in `tempdb` use uniform extents. [Trace flag 1118](../../t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql.md) 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!](/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my). - The `AUTOGROW_ALL_FILES` property is always turned on for the `PRIMARY` filegroup. #### Introduced in [!INCLUDE [sssql17-md](../../includes/sssql17-md.md)] - The SQL Setup experience improves guidance for initial `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](database-instant-file-initialization.md) is not enabled, preventing instance startup delays. - The [sys.dm_tran_version_store_space_usage](../system-dynamic-management-views/sys-dm-tran-version-store-space-usage.md) dynamic management view tracks version store usage per database. This DMV is useful for DBAs who want to proactively plan `tempdb` sizing based on the version store usage requirement per database. - [Intelligent query processing](../performance/intelligent-query-processing.md) features such as adaptive joins and memory grant feedback reduce memory spills on consecutive executions of a query, reducing `tempdb` utilization. #### Introduced in [!INCLUDE [sssql19-md](../../includes/sssql19-md.md)] - [!INCLUDE[ssde-md](../../includes/ssde-md.md)] doesn't use the `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](/troubleshoot/sql/database-engine/database-file-operations/logging-data-storage-algorithms#performance-impacts). - [Memory-optimized TempDB metadata](#memory-optimized-tempdb-metadata) removes temporary object metadata contention in `tempdb`. - Concurrent Page Free Space (PFS) page updates reduce page latch contention in all databases, an issue most commonly seen in `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](https://techcommunity.microsoft.com/t5/sql-server-blog/under-the-covers-gam-sgam-and-pfs-pages/ba-p/383125). - By default, a new installation of SQL Server on Linux creates multiple `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. #### Introduced in [!INCLUDE [sssql22-md](../../includes/sssql22-md.md)] - Introduced [improved scalability with system page latch concurrency enhancements](https://cloudblogs.microsoft.com/sqlserver/2022/07/21/improve-scalability-with-system-page-latch-concurrency-enhancements-in-sql-server-2022/). Concurrent updates to global allocation map (GAM) pages and shared global allocation map (SGAM) pages reduce page latch contention while allocating/deallocating data pages and extents. These enhancements apply to all user databases and especially benefit heavy workloads in `tempdb`. For more information on GAM and SGAM pages, read [Under the covers: GAM, SGAM, and PFS pages](https://techcommunity.microsoft.com/t5/sql-server-blog/under-the-covers-gam-sgam-and-pfs-pages/ba-p/383125). For more information, watch [System Page Latch Concurrency Enhancements (Ep. 6) | Data Exposed](/shows/data-exposed/system-page-latch-concurrency-enhancements). ## Memory-optimized TempDB metadata Temporary object metadata contention has historically been a bottleneck to scalability for many [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] workloads. To address that, [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)] introduced a feature that's part of the [in-memory database](../in-memory-database.md) 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 [!INCLUDE[sql-server-2019](../../includes/sssql19-md.md)], the system tables involved in managing temporary object metadata can become latch-free, non-durable, memory-optimized tables. > [!TIP] > Because of current [limitations](#limitations-of-memory-optimized-tempdb-metadata), 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](../system-tables/system-base-tables.md), and returns the number of sessions contending for access to that table at the time when this diagnostic query is executed. ```sql 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: > [!VIDEO https://learn-video.azurefd.net/vod/player?show=data-exposed&ep=how-and-when-to-memory-optimized-tempdb-metadata] > [!NOTE] > Currently, the Memory-optimized TempDB metadata feature is not available in Azure SQL Database, [!INCLUDE [fabric-sqldb](../../includes/fabric-sqldb.md)], and Azure SQL Managed Instance. ### Configure and use Memory-optimized TempDB metadata The following sections include steps to enable, configure, verify, and disable the Memory-optimized TempDB metadata feature. #### Enable To enable this feature, use the following script: ```sql ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON; ``` For more information, see [ALTER SERVER](../../t-sql/statements/alter-server-configuration-transact-sql.md). 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: ```sql 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 [!INCLUDE[ssde-md](../../includes/ssde-md.md)] instance with [minimal configuration](../../database-engine/configure-windows/start-sql-server-with-minimal-configuration.md) using the `-f` startup option. You can then [disable](#disable) the feature and remove the `-f` option to restart the [!INCLUDE[ssde-md](../../includes/ssde-md.md)] in normal mode. #### Bind to resource pool to limit memory usage To protect the server from potential out-of-memory conditions, we recommend that you bind `tempdb` to a resource governor [resource pool](../in-memory-oltp/bind-a-database-with-memory-optimized-tables-to-a-resource-pool.md) 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](../resource-governor/resource-governor.md), 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. ```sql 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. #### Verify resource pool binding and monitor memory usage To verify that `tempdb` is bound to a resource pool and to monitor memory usage statistics for the pool, use the following query: ```sql 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'; ``` #### Remove resource pool binding To remove the resource pool binding while keeping Memory-optimized TempDB metadata enabled, execute the following command and restart the service: ```sql ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON; ``` #### Disable To disable Memory-optimized TempDB metadata, execute the following command and restart the service: ```sql ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF; ``` ### Limitations of Memory-optimized TempDB metadata - 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: ```sql 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: - Bind the `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](#configure-and-use-memory-optimized-tempdb-metadata). - A system stored procedure can be periodically executed to release `MEMORYCLERK_XTP` memory that is no longer needed. For more information, see [sys.sp_xtp_force_gc (Transact-SQL)](../system-stored-procedures/sys-sp-xtp-force-gc-transact-sql.md). For more information, see [memory-optimized tempdb metadata (HkTempDB) out of memory errors](/troubleshoot/sql/admin/memory-optimized-tempdb-out-of-memory). - When you use [In-Memory OLTP](../in-memory-oltp/overview-and-usage-scenarios.md), 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: ```output 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](../system-catalog-views/sys-stats-transact-sql.md) catalog view in a user database that contains memory-optimized tables. This happens because the query attempts to access [statistics](../in-memory-oltp/statistics-for-memory-optimized-tables.md) 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: ```sql 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 .. 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](../indexes/columnstore-indexes-overview.md) can't be created on temporary tables when Memory-optimized TempDB metadata is enabled. - As a consequence, the 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. ## Capacity planning for tempdb in SQL Server Determining the appropriate size for `tempdb` depends on many factors. These factors include the workload and the [!INCLUDE[ssde-md](../../includes/ssde-md.md)] 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: - Enable [autogrow](../../t-sql/statements/alter-database-transact-sql-file-and-filegroup-options.md) for `tempdb` files. All `tempdb` data files should have the same initial size and autogrow configuration. - Reproduce the workload and monitor `tempdb` space use. - If you use periodic [index maintenance](../indexes/reorganize-and-rebuild-indexes.md), execute your maintenance jobs and monitor `tempdb` space. - Use the maximum space used 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. ## Monitor tempdb use Running out of disk space in `tempdb` can cause significant disruptions and application downtime. You can use the [sys.dm_db_file_space_usage](../../relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql.md) dynamic management view to monitor the space used in the `tempdb` files. For example, the following example script finds: - Free space in `tempdb` (not considering free disk space that might be available for `tempdb` growth) - Space used by the version store - Space used by internal objects - Space used by user objects ```sql 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](../../relational-databases/system-dynamic-management-views/sys-dm-db-session-space-usage-transact-sql.md) and [sys.dm_db_task_space_usage](../../relational-databases/system-dynamic-management-views/sys-dm-db-task-space-usage-transact-sql.md) 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: ```sql 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: ```sql 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; ``` ## Related content - [SORT_IN_TEMPDB Option For Indexes](../indexes/sort-in-tempdb-option-for-indexes.md) - [System databases](system-databases.md) - [sys.databases](../system-catalog-views/sys-databases-transact-sql.md) - [sys.master_files](../system-catalog-views/sys-master-files-transact-sql.md) - [Move database files](move-database-files.md)