TempDB 数据库TempDB Database

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

TempDB 系统数据库是一个全局资源,可供连接到 SQL ServerSQL Server 实例或 SQL 数据库的所有用户使用 。The TempDB system database is a global resource that is available to all users connected to the instance of SQL ServerSQL Server or connected to SQL Database. tempdb 用于保留: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:

    • 用于储存假脱机、游标、排序和临时大型对象 (LOB) 存储的中间结果的工作表。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.
    • 用于创建或重新生成索引等操作(如果指定了 SORT_IN_TEMPDB)的中间排序结果,或者某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果。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.

    备注

    每个内部对象至少使用九页;一个 IAM 页,一个八页的区。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 数据库单一数据库和弹性池支持存储在 TempDB 中并且作用域为数据库级别的全局临时表和全局临时存储过程。Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored in TempDB and are scoped to the database level. 全局临时表和全局临时存储过程供同一 Azure SQL 数据库中的所有用户会话共享。Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same Azure SQL database. 其他 Azure SQL 数据库中的用户会话无法访问全局临时表。User sessions from other Azure SQL databases cannot access global temporary tables. 有关详细信息,请参阅数据库作用域内全局临时表(Azure SQL 数据库)For more information, see Database scoped global temporary tables (Azure SQL Database). Azure SQL 数据库托管实例与 SQL Server 支持相同的临时对象。Azure SQL Database Managed Instance) supports the same temporary objects as does SQL Server. 对于 Azure SQL 数据库单一数据库和弹性池,仅 master 数据库和 TempDB 数据库适用。For Azure SQL Database single databases and elastic pools, only master database and TempDB database apply. 有关详细信息,请参阅什么是 Azure SQL 数据库服务器For more information, see What is an Azure SQL Database server. 有关 Azure SQL 数据库单一数据库和弹性池上下文中 TempDB 的讨论,请参阅 Azure SQL 数据库单一数据库和弹性池中的 TempDB 数据库For a discussion of TempDB in the context of Azure SQL Database single databases and elastic pools, see TempDB Database in Azure SQL Database single databases and elastic pools. 对于 Azure SQL 数据库托管实例,所有系统数据库都适用。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.
    • 由数据修改事务为实现联机索引操作、多个活动的结果集 (MARS) 以及 AFTER 触发器等功能而生成的行版本。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 中的操作是最小日志记录操作,以便回滚事务。Operations within TempDB are minimally logged so that transactions can be rolled back. 每次启动 时都会重新创建 TempDBSQL ServerSQL Server,从而在系统启动时总是具有一个干净的数据库副本。TempDB is re-created every time SQL ServerSQL 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 中不会有什么内容从一个 SQL ServerSQL Server 会话保存到另一个会话。Therefore, there is never anything in TempDB to be saved from one session of SQL ServerSQL Server to another. 不允许对 TempDB 进行备份和还原操作。Backup and restore operations are not allowed on TempDB.

SQL Server 中 TempDB 的物理属性Physical Properties of TempDB in SQL Server

下表列出了 SQL Server 中 TempDB 数据和日志文件的初始配置值(基于模型数据库的默认设置)。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. 对于不同版本的 SQL ServerSQL Server,这些文件的大小可能略有不同。The sizes of these files may vary slightly for different editions of SQL ServerSQL Server.

文件File 逻辑名称Logical name 物理名称Physical name 初始大小Initial size 文件增长File growth
主数据Primary data tempdevtempdev tempdb.mdftempdb.mdf 8 MB8 megabytes 以 64 MB 的速度自动增长直到磁盘已满Autogrow by 64 MB until the disk is full
次要数据文件*Secondary data files* temp#temp# tempdb_mssql_#.ndftempdb_mssql_#.ndf 8 MB8 megabytes 以 64 MB 的速度自动增长直到磁盘已满Autogrow by 64 MB until the disk is full
日志Log templogtemplog templog.ldftemplog.ldf 8 MB8 megabytes 以 64 MB 的速度自动增长直到达到上限 2 TBAutogrow by 64 megabytes to a maximum of 2 terabytes

*文件数取决于计算机上的(逻辑)处理器数。* The number of files depends on the number of (logical) processors on the machine. 一般而言,如果逻辑处理器数目小于或等于 8,则使用的数据文件数与逻辑处理器数相同。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. 如果逻辑处理器数目大于 8,则使用 8 个数据文件,如果仍然存在争用,则以 4 的倍数增加数据文件的数量,直到争用减少到可接受的级别或对工作负荷/代码进行更改。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.

备注

数据文件数的默认值遵循 KB 2154845中的一般准则。The default value for the number of data files is based on the general guidelines in KB 2154845.

在 SQL Server 中移动 TempDB 数据和日志文件Moving the TempDB data and log files in SQL Server

若要移动 TempDB 数据和日志文件,请参阅移动系统数据库To move the TempDB data and log files, see Move System Databases.

SQL Server 中 TempDB 的数据库选项Database Options for TempDB in SQL Server

下表列出了 TempDB 数据库中每个数据库选项的默认值,以及是否可以修改该选项。The following table lists the default value for each database option in the TempDB database and whether the option can be modified. 若要查看这些选项的当前设置,请使用 sys.databases 目录视图。To view the current settings for these options, use the sys.databases catalog view.

数据库选项Database option 默认值Default value 是否可修改Can be modified
ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION OFFOFF Yes
ANSI_NULL_DEFAULTANSI_NULL_DEFAULT OFFOFF Yes
ANSI_NULLSANSI_NULLS OFFOFF Yes
ANSI_PADDINGANSI_PADDING OFFOFF Yes
ANSI_WARNINGSANSI_WARNINGS OFFOFF Yes
ARITHABORTARITHABORT OFFOFF Yes
AUTO_CLOSEAUTO_CLOSE OFFOFF No
AUTO_CREATE_STATISTICSAUTO_CREATE_STATISTICS ONON Yes
AUTO_SHRINKAUTO_SHRINK OFFOFF No
AUTO_UPDATE_STATISTICSAUTO_UPDATE_STATISTICS ONON Yes
AUTO_UPDATE_STATISTICS_ASYNCAUTO_UPDATE_STATISTICS_ASYNC OFFOFF Yes
CHANGE_TRACKINGCHANGE_TRACKING OFFOFF No
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL OFFOFF Yes
CURSOR_CLOSE_ON_COMMITCURSOR_CLOSE_ON_COMMIT OFFOFF Yes
CURSOR_DEFAULTCURSOR_DEFAULT GLOBALGLOBAL Yes
数据库可用性选项Database Availability Options ONLINEONLINE

MULTI_USERMULTI_USER

READ_WRITEREAD_WRITE
No

No

No
DATE_CORRELATION_OPTIMIZATIONDATE_CORRELATION_OPTIMIZATION OFFOFF Yes
DB_CHAININGDB_CHAINING ONON No
ENCRYPTIONENCRYPTION OFFOFF No
MIXED_PAGE_ALLOCATIONMIXED_PAGE_ALLOCATION OFFOFF No
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF Yes
PAGE_VERIFYPAGE_VERIFY 对于新安装的 SQL ServerSQL Server,为 CHECKSUM。CHECKSUM for new installations of SQL ServerSQL Server.

对于升级的 SQL ServerSQL Server,为 NONE。NONE for upgrades of SQL ServerSQL Server.
Yes
PARAMETERIZATIONPARAMETERIZATION SIMPLESIMPLE Yes
QUOTED_IDENTIFIERQUOTED_IDENTIFIER OFFOFF Yes
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT OFFOFF No
RECOVERYRECOVERY SIMPLESIMPLE No
RECURSIVE_TRIGGERSRECURSIVE_TRIGGERS OFFOFF Yes
Service Broker 选项Service Broker Options ENABLE_BROKERENABLE_BROKER Yes
TRUSTWORTHYTRUSTWORTHY OFFOFF No

有关这些数据库选项的说明,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

SQL 数据库中的 TempDB 数据库TempDB database in SQL Database

基于 DTU 的服务层的 TempDB 大小TempDB sizes for DTU-based service tiers

SLOSLO 最大 TempDB 数据文件大小 (GB)Max TempDB Data File Size (GBs) TempDB 数据文件数# of TempDB data files 最大 TempDB 数据大小 (GB)Max TempDB data size (GB)
“基本”Basic 1313 11 1313
S0S0 1313 11 1313
S1S1 1313 11 1313
S2S2 1313 11 1313
S3S3 3232 11 3232
S4S4 3232 22 6464
S6S6 3232 33 9696
S7S7 3232 66 192192
S9S9 3232 1212 384384
S12S12 3232 1212 384384
P1P1 1313 1212 156156
P2P2 1313 1212 156156
P4P4 1313 1212 156156
P6P6 1313 1212 156156
P11P11 1313 1212 156156
P15P15 1313 1212 156156
高级弹性池(所有 DTU 配置)Premium Elastic Pools (all DTU configurations) 1313 1212 156156
标准弹性池 (S0-S2)Standard Elastic Pools (S0-S2) 1313 1212 156156
标准弹性池(S3 及更高版本)Standard Elastic Pools (S3 and above) 3232 1212 384384
基本弹性池(所有 DTU 配置)Basic Elastic Pools (all DTU configurations) 1313 1212 156156

基于 vCore 的服务层的 TempDB 大小TempDB sizes for vCore-based service tiers

请参阅基于 vCore 的资源限制See vCore-based resource limits

限制Restrictions

不能对 TempDB 数据库执行以下操作:The following operations cannot be performed on the TempDB database:

  • 添加文件组Adding filegroups
  • 备份或还原数据库Backing up or restoring the database
  • 更改排序规则。Changing collation. 默认排序规则为服务器排序规则The default collation is the server collation
  • 更改数据库所有者。Changing the database owner. TempDB 的所有者是 sa TempDB is owned by sa
  • 创建数据库快照Creating a database snapshot
  • 删除数据库Dropping the database
  • 从数据库中删除 guest 用户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
  • 运行 DBCC CHECKALLOCRunning DBCC CHECKALLOC
  • 运行 DBCC CHECKCATALOGRunning DBCC CHECKCATALOG
  • 将数据库设置为 OFFLINESetting the database to OFFLINE
  • 将数据库或主文件组设置为 READ_ONLYSetting the database or primary filegroup to READ_ONLY

权限Permissions

任何用户都可以在 TempDB 中创建临时对象。Any user can create temporary objects in TempDB. 用户只能访问自己的对象,除非他们获得更多的权限。Users can only access their own objects, unless they receive additional permissions. 可以撤消对 TempDB 的连接权限以阻止用户使用 TempDB,但是不建议这样做,因为一些例行操作需要使用 TempDB。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 TempDB.

在 SQL Server 中优化 TempDB 性能Optimizing TempDB performance in SQL Server

TempDB 数据库的大小和物理位置可能会影响系统的性能。The size and physical placement of the TempDB database can affect the performance of a system. 例如,如果定义的 TempDB 大小过小,那么每次重启 SQL ServerSQL Server 实例时,都可能会占用部分系统处理负荷,让 TempDB 自动增长到支持工作负荷所需的大小。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 ServerSQL Server.

如果可以,请使用数据库即时文件初始化来提高数据文件增长操作的性能。If possible, use database instant file initialization to improve the performance of data file grow operations.

通过将文件大小设置为足够容纳环境中典型工作负荷的值来预分配所有 TempDB 文件的空间。Preallocate space for all TempDB files by setting the file size to a value large enough to accommodate the typical workload in the environment. 预先分配可避免 TempDB 因扩展得过于频繁而影响性能。Preallocation prevents TempDB from expanding too frequently, which affects performance. TempDB 数据库应设置为自动增长,但是在出现意外情况时此设置将用于增加磁盘空间。The TempDB database should be set to autogrow, but this should be used to increase disk space for unplanned exceptions.

每个文件组中的数据文件应大小一致,因为 SQL ServerSQL Server 使用比例填充算法,这种算法可增加可用空间,便于文件分配。Data files should be of equal size within each filegroup, as SQL ServerSQL Server uses a proportional-fill algorithm that favors allocations in files with more free space. 将 TempDB 分割成大小相等的多个数据文件,可以为使用 TempDB 的操作提供更高的并行效率。Dividing TempDB into multiple data files of equal size provides a high degree of parallel efficiency in operations that use TempDB.

将文件增量设置为合理的大小以避免 TempDB 数据库文件的增量过小。Set the file growth increment to a reasonable size to avoid the TempDB database files from growing by too small a value. 如果文件的增量与写入 TempDB 的数据量相比过小,则 TempDB 可能需要不断扩大,并且会影响性能。If the file growth is too small, compared to the amount of data that is being written to TempDB, TempDB may have to constantly expand and affect performance.

要检查当前 TempDB 大小和增长参数,请使用以下查询: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 数据库放置在快速 I/O 子系统中。Put the TempDB database on a fast I/O subsystem. 如果有许多直接连接的磁盘,则请使用磁盘条带化。Use disk striping if there are many directly attached disks. 单个或成组的 TempDB 数据文件并不一定要位于不同的磁盘或主轴上,除非存在 I/O 瓶颈。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 数据库放置在用户数据库使用的磁盘以外的磁盘中。Put the TempDB database on disks that differ from those that are used by user databases.

SQL Server 中 TempDB 的性能提高Performance improvements in TempDB for SQL Server

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,已通过以下方式进一步优化 TempDB 性能:Starting with SQL Server 2016 (13.x)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.
  • 改进了分配页闩锁协议,减少了所用 UP(更新)闩锁的数量。Allocation page latching protocol is improved to reduce the number of UP (update) latches that are used.
  • 减少了 TempDB 的日志记录开销,从而减少了 TempDB 日志文件的磁盘 I/O 带宽消耗 。Logging overhead for TempDB is reduced to reduce disk I/O bandwidth consumption on the TempDB log file.
  • 在新的实例安装过程中,安装程序会添加多个 TempDB 数据文件。Setup adds multiple TempDB data files during a new instance installation. 可以使用“数据库引擎配置” 部分中新增的 UI 输入控件和命令行参数 /SQLTEMPDBFILECOUNT 来完成此任务。This task can be accomplished with the new UI input control on the Database Engine Configuration section and a command-line parameter /SQLTEMPDBFILECOUNT. 默认情况下,安装程序添加的 TempDB 数据文件数为逻辑处理器计数或 8,以较小者为准。By default, setup adds as many TempDB data files as the logical processor count or eight, whichever is lower.
  • 如果有多个 TempDB 数据文件,那么所有文件都会同时自动增长相同的量,具体取决于增长设置。When there are multiple TempDB data files, all files autogrow at same time and by the same amount depending on growth settings. 不再需要跟踪标志 1117Trace flag 1117 is no longer required.
  • TempDB 中的所有分配使用统一盘区。All allocations in TempDB use uniform extents. 不再需要跟踪标志 1118Trace flag 1118 is no longer required.
  • 对于主文件组,AUTOGROW_ALL_FILES 属性已启用,且不能修改此属性。For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property cannot be modified.

要详细了解 TempDB 中的性能改进,请参阅以下博客文章:For more information on performance improvements in TempDB, see the following blog article:

TEMPDB - 文件和跟踪标志以及更新,天哪!TEMPDB - Files and Trace Flags and Updates, Oh My!

内存优化 TempDB 元数据Memory-Optimized TempDB Metadata

对于 SQL ServerSQL Server 上运行的许多工作负荷,TempDB 元数据争用历来是可伸缩性的瓶颈。TempDB metadata contention has historically been a bottleneck to scalability for many workloads running on SQL ServerSQL Server. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 引入了一个新功能,该功能属于内存数据库功能内存优化 TempDB 元数据,它可有效消除此瓶颈,并为 TempDB 繁重的工作负荷解锁新级别的可伸缩性。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. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中,管理临时表元数据时所涉及的系统表可以移动到无闩锁的非持久内存优化表中。In SQL Server 2019 (15.x)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. 要选择加入此新功能,请使用以下脚本: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:

  1. 该功能的打开和关闭不是动态的。Toggling the feature on and off is not dynamic. 由于需要对 TempDB 结构进行内部更改,因此需要重新启动才能启用或禁用该功能。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.

  2. 单个事务可能无法访问多个数据库中的内存优化表。A single transaction may not access memory-optimized tables in more than one database. 这意味着涉及用户数据库中内存优化表的任何事务都无法访问同一事务中的 TempDB 系统视图。This means that any transactions that involve a memory-optimized table in a user database will not be able to access TempDB system views in the same transaction. 如果尝试在与用户数据库中内存优化表相同的事务中访问 TempDB 系统视图,将收到以下错误:If you attempt to access TempDB system 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.
    

    例如:Example:

    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
    
  3. 针对内存优化表的查询不支持锁定和隔离提示,因此针对内存优化 TempDB 目录视图的查询将不会遵循锁定和隔离提示。Queries against memory-optimized tables do not support locking and isolation hints, so queries against memory-optimized TempDB catalog views will not honor locking and isolation hints. SQL ServerSQL Server 中的其他系统目录视图一样,针对系统视图的所有事务都将处于 READ COMMITTED(或在本例中为 READ COMMITTED SNAPSHOT)隔离。As with other system catalog views in SQL ServerSQL Server, all transactions against system views will be in READ COMMITTED (or in this case READ COMMITTED SNAPSHOT) isolation.

  4. 如果启用内存优化的 TempDB 元数据,则无法在临时表上创建列存储索引Columnstore indexes cannot be created on temporary tables when Memory-Optimized TempDB Metadata is enabled.

  5. 由于对列存储索引的限制,启用内存优化 TempDB 元数据时,不支持将 sp_estimate_data_compression_savings 系统存储过程与 COLUMNSTORE 或 COLUMNSTORE_ARCHIVE 数据压缩参数一起使用。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.

备注

这些限制仅适用于引用 TempDB 系统视图的情况,如果需要,可在访问用户数据库中内存优化表的同一事务中创建临时表。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.

可以使用以下 T-SQL 命令验证 TempDB 是否经过内存优化:You can verify whether or not TempDB is memory-optimized by using the following T-SQL command:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')

如果启用内存优化的 TempDB 元数据后,服务器因任何原因未能启动,则可以使用 -f 启动选项以最小配置启动 SQL Server,从而绕过该功能。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. 这将使用户能够禁用该功能,然后在正常模式下重启 SQL Server。This will enable you to disable the feature and then restart SQL Server in normal mode.

SQL Server 中的 TempDB 容量规划Capacity Planning for TempDB in SQL Server

确定 TempDB 在 SQL ServerSQL Server 生产环境中的适当大小取决于多种因素。Determining the appropriate size for TempDB in a SQL ServerSQL Server production environment depends on many factors. 如本文中前面所述,这些因素包括现有工作负荷以及使用的 SQL ServerSQL Server 功能。As described previously in this article, these factors include the existing workload and the SQL ServerSQL Server features that are used. 建议你通过在 SQL Server 测试环境中执行下列任务来分析现有的工作负荷:We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

  • 设置 TempDB 的自动增长。Set autogrow on for TempDB.
  • 执行单独的查询或工作负荷跟踪文件,并监视 TempDB 空间使用。Execute individual queries or workload trace files and monitor TempDB space use.
  • 执行索引维护操作(例如重新生成索引),并监视 TempDB 空间。Execute index maintenance operations, such as rebuilding indexes and monitor TempDB space.
  • 使用前面步骤中的空间使用值来预测总的工作负荷使用情况;针对计划的并发活动调整此值,然后相应地设置 TempDB 的大小。Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of TempDB accordingly.

如何监视 TempDB 的使用How to Monitor TempDB use

如果 TempDB 中磁盘空间不足,则可能会严重破坏 SQL ServerSQL Server 生产环境,并会使正在运行的应用程序无法完成操作。Running out of disk space in TempDB can cause significant disruptions in the SQL ServerSQL Server production environment and can prevent applications that are running from completing operations. 可以使用 sys.dm_db_file_space_usage 动态管理视图来监视 TempDB 文件中使用的磁盘空间: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;

此外,若要在会话级或任务级监视 TempDB 中的页分配或页释放活动,可以使用 sys.dm_db_session_space_usagesys.dm_db_task_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. 这些视图可用于标识使用 TempDB 中大量磁盘空间的大型查询、临时表或表变量。These views can be used to identify large queries, temporary tables, or table variables that are using lots of TempDB disk space. 还可以使用若干个计数器来监视 TempDB 中的可用空间以及正在使用 TempDB 的资源。There are also several counters that can be used to monitor the free space that is available in TempDB and also the resources that are using TempDB. 有关详细信息,请参阅下一节。For more information, see the next section.

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

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

用于索引的 SORT_IN_TEMPDB 选项 SORT_IN_TEMPDB Option For Indexes
系统数据库 System Databases
sys.databases sys.databases
sys.master_files sys.master_files
移动数据库文件Move Database Files