Události
31. 3. 23 - 2. 4. 23
Konečná událost vedoucí komunitou SQL, Power BI, Fabric a AI. 31. března – 2. dubna. Použijte kód MSCUST pro slevu ve výši 150 USD. Ceny jdou nahoru 11. února.
Zaregistrovat se ještě dnesTento prohlížeč se už nepodporuje.
Upgradujte na Microsoft Edge, abyste mohli využívat nejnovější funkce, aktualizace zabezpečení a technickou podporu.
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 an instance of SQL Server, Azure SQL Database, or Azure SQL Managed Instance.
The tempdb
system database is a global resource that holds:
Temporary user objects that are explicitly created. They include global or local temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors.
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 features for row versioning. There are two types: a common version store and an online-index-build version store. The version stores contain:
READ COMMITTED
through row versioning isolation or snapshot isolation transactions.AFTER
triggers.Operations within 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.
tempdb
never has anything to be saved from one session of SQL Server 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 |
The number of secondary data 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. Then if contention continues, increase the number of data files by multiples of four until the contention decreases 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.
To check current size and growth parameters for tempdb
, query view tempdb.sys.database_files
.
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 |
---|---|---|
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).
The behavior of tempdb
in Azure SQL Database differs from the behavior SQL Server, Azure SQL Managed Instance, and SQL Server on Azure VMs.
Single and pooled databases in Azure SQL Database support global temporary tables and global temporary stored procedures scoped to the database level, and stored in tempdb
. Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same database. User sessions from other databases can't access global temporary tables. For more information, see Database scoped global temporary tables (Azure SQL Database).
For single databases, each single 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 pool.
For single and pooled databases in Azure SQL Database, out of all the system databases, only the master
database and tempdb
database are accessible. For more information, see What is a logical server in Azure?
To learn more about tempdb
sizes in Azure SQL Database, review:
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 managed instance. Likewise, all system databases are accessible.
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.
To learn more about tempdb
sizes in Azure SQL Managed Instance, review resource limits.
A SQL database in Microsoft Fabric supports global temporary tables and global temporary stored procedures scoped to the database level, and stored in tempdb
. Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same database. User sessions from other databases can't access global temporary tables. For more information, see Database scoped global temporary tables.
To learn more about tempdb
sizes in SQL database in Microsoft Fabric, review the Resource Limits in the Feature comparison: Azure SQL Database and SQL database in Microsoft Fabric.
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 objects, unless they receive additional permissions. It's possible to revoke the connect permission to tempdb
to prevent a user from using tempdb
. We don't recommend it because some routine operations require the use of tempdb
.
The size and physical placement of the tempdb
database can affect the performance of a system. For example, if the size that's defined for tempdb
is too small, part of the system-processing load might 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 instant file initialization to improve the performance of growth operations for data files.
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 often, which affects performance. The tempdb
database should be set to autogrow to increase disk space for unplanned exceptions.
Data files should be of equal size within each filegroup, because 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 and set it to the same increment in all data files, to prevent 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's being written to tempdb
, tempdb
might have to frequently expand via autogrowth events. Autogrowth events negatively affect performance.
To check current size and growth parameters for tempdb
, use the following query:
SELECT FileName = df.name,
current_file_size_MB = df.size*1.0/128,
max_size = CASE df.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_value =
CASE
WHEN df.growth = 0 THEN df.growth
WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
END,
growth_increment_unit =
CASE
WHEN df.growth = 0 THEN 'Size is fixed.'
WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN 'Growth value is MB.'
WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files AS df;
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 don't necessarily need to be on different disks or spindles unless you're also encountering I/O bottlenecks.
Put the tempdb
database on disks that differ from the disks that user databases use.
Poznámka
Even though the database option DELAYED_DURABILITY
is set to DISABLED for tempdb
, SQL Server uses lazy commits to flush tempdb
log changes to disk, since tempdb
is created at startup and doesn't need to run the recovery process.
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. You can accomplish this task by using the new UI input control in the Database Engine Configuration section and the command-line parameter /SQLTEMPDBFILECOUNT
. By default, setup adds as many tempdb
data files as the logical processor count 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 turned on and the property can't be modified.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
for version store usage for DBAs who can proactively plan tempdb
sizing based on the version store usage requirement per database.tempdb
utilization.FILE_FLAG_WRITE_THROUGH
option when opening files for tempdb
to allow for maximum disk throughput. Since tempdb
is recreated on startup of SQL Server, these options are not needed as they are for other system databases and user databases for data consistency. For more information on FILE_FLAG_WRITE_THROUGH
, see Logging and data storage algorithms that extend data reliability in SQL Server.tempdb
, and unlocks a new level of scalability. For more information, watch this video demo on How (and When) To: Memory Optimized TempDB Metadata. For more information, read monitoring and troubleshooting memory-optimized tempdb metadata.tempdb
. This improvement changes the way that concurrency is managed with PFS 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
file is 8 MB, with an auto growth of 64 MB. This behavior is similar to the default SQL Server installation on Windows.tempdb
heavy workloads. 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.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's part of the in-memory database feature family: Memory-optimized TempDB metadata.
This feature 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.
Poznámka
Currently the Memory-optimized TempDB metadata feature is not available in Azure SQL Database, SQL database in Microsoft Fabric, or Azure SQL Managed Instance.
Watch this seven-minute video for an overview of how and when to use Memory-optimized TempDB metadata:
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.
You can verify whether or not tempdb
is memory-optimized by using the following T-SQL command:
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
If the server fails to start for any reason after you enable Memory-optimized TempDB metadata, you can bypass the feature by starting the SQL Server instance with minimal configuration through the -f startup option. You can then disable the feature and restart SQL Server in normal mode.
To protect the server from potential out-of-memory conditions, you can bind tempdb
to a resource pool. This is done through the ALTER SERVER
command rather than the steps you would normally follow to bind a resource pool to a database.
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');
This change also requires a restart to take effect, even if Memory-optimized TempDB metadata is already enabled.
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 is not allowed to access memory-optimized tables in more than one database. Any transactions that involve a memory-optimized table in a user database won't be able to access tempdb
system views in the same transaction. If you try to access tempdb
system views in the same transaction as a memory-optimized table in a user database, you 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.
Example:
BEGIN TRAN;
SELECT *
FROM tempdb.sys.tables; -----> Creates a user in-memory OLTP transaction in tempdb
INSERT INTO <user database>.<schema>.<mem-optimized table>
VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail
COMMIT TRAN;
Queries against memory-optimized tables don't support locking and isolation hints, so queries against memory-optimized tempdb
catalog views won't honor locking and isolation hints. As with other system catalog views in SQL Server, all transactions against system views are in READ COMMITTED
(or in this case, READ COMMITTED SNAPSHOT
) isolation.
Columnstore indexes can't 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.
A system stored procedure is available to manually cause the in-memory engine to release memory related to deleted rows of in-memory data that are eligible for garbage collection. This can help with troubleshooting specific memory-optimized tempdb metadata (HkTempDB) out of memory errors. For more information, see sys.sp_xtp_force_gc (Transact-SQL).
Poznámka
These limitations apply only when you're referencing tempdb
system views. You can create a temporary table in the same transaction as you access a memory-optimized table in a user database, if desired.
Determining the appropriate size for tempdb
in a SQL Server production environment depends on many factors. As described earlier, 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:
tempdb
.tempdb
space use.tempdb
space.tempdb
accordingly.Running out of disk space in tempdb
can cause significant disruptions in the SQL Server production environment. It can also 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's used in the tempdb
files.
For example, the following four sample scripts find the amount of free space in tempdb
, the amount of space used by the version store, the amount of space used by internal objects, and amount of space used by user objects:
-- 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 tempdb.sys.dm_db_file_space_usage;
-- Determining the amount of 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 tempdb.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 tempdb.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 tempdb.sys.dm_db_file_space_usage;
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 help you identify large queries, temporary tables, or table variables that are using lots of tempdb
disk space. You can also use several counters to monitor the free space that's available in tempdb
and the resources that are using tempdb
.
For example, use the following script to obtaining the tempdb
space consumed by internal objects in all currently running tasks in each session:
-- 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;
Use the following script to find the tempdb
space consumed by internal objects in the current session, for both running and completed tasks:
-- 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;
Události
31. 3. 23 - 2. 4. 23
Konečná událost vedoucí komunitou SQL, Power BI, Fabric a AI. 31. března – 2. dubna. Použijte kód MSCUST pro slevu ve výši 150 USD. Ceny jdou nahoru 11. února.
Zaregistrovat se ještě dnesŠkolení
Modul
Konfigurace prostředků SQL Serveru pro zajištění optimálního výkonu - Training
Konfigurace prostředků SQL Serveru pro zajištění optimálního výkonu