TempDB 数据库tempdb database

• 09/16/2020
• • s
  • o
  • O
  • S

适用于：Applies to: SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database适用于：Applies to: SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database

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

  • 用于储存假脱机、游标、排序和临时大型对象 (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.

  全局临时表和全局临时存储过程供同一 SQL 数据库中的所有用户会话共享。Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same SQL database. 其他 SQL 数据库中的用户会话无法访问全局临时表。User sessions from other SQL databases can't access global temporary tables. 有关详细信息，请参阅数据库作用域内全局临时表（Azure SQL 数据库）。For more information, see Database scoped global temporary tables (Azure SQL Database). Azure SQL 托管实例与 SQL Server 支持相同的临时对象。Azure SQL 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 the 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 Managed Instance, all system databases apply.

• 版本存储区是数据页的集合，它包含支持用于行版本控制的功能的数据行。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 的数据库中的数据修改事务生成的行版本。Row versions that are generated by data modification transactions in a database that uses READ COMMITTED through 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. 每次启动 SQL ServerSQL Server 时都会重新创建 tempdb，从而在系统启动时总是具有一个干净的数据库副本。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 的一个会话保存到另一个会话。tempdb never has anything 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. 这些值基于 model 数据库的默认值。The values are based on the defaults for the model database. 对于不同版本的 SQL ServerSQL Server，这些文件的大小可能略有不同。The sizes of these files might vary slightly for different editions of SQL ServerSQL Server.

辅助数据文件数取决于计算机上的（逻辑）处理器数。The number of secondary data 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 个数据文件。If the number of logical processors is greater than eight, use eight data files. 如果仍然存在争用，则以 4 的倍数增加数据文件的数量，直到争用减少到可接受的级别或对工作负荷/代码进行更改。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.

在 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.

有关这些数据库选项的说明，请参阅 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

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

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

限制Restrictions

不能在 tempdb 数据库中执行下列操作：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 的所有者是 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 CHECKALLOC。Running DBCC CHECKALLOC.
• 正在运行 DBCC CHECKCATALOG。Running DBCC CHECKCATALOG.
• 将数据库设置为 OFFLINE。Setting the database to OFFLINE.
• 将数据库或主文件组设置为 READ_ONLY。Setting the database or primary filegroup to READ_ONLY.

权限Permissions

任何用户都可以在 tempdb 中创建临时对象。Any user can create temporary objects in tempdb. 用户只能访问自己的对象，除非他们获得更多的权限。Users can access only their own objects, unless they receive additional permissions. 可以撤销对 tempdb 的连接权限以阻止用户使用 tempdb。It's possible to revoke the connect permission to tempdb to prevent a user from using tempdb. 我们不建议这样做，因为一些例程操作需要使用 tempdb。We don't recommend it because 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'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 ServerSQL Server.

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

通过将文件大小设置为足够容纳环境中典型工作负载的值来预分配所有 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 often, which affects performance. tempdb 数据库应设置为自动增长，以便在出现意外情况时增加磁盘空间。The tempdb database should be set to autogrow to increase disk space for unplanned exceptions.

每个文件组中的数据文件应大小一致，因为 SQL ServerSQL Server 使用比例填充算法，这种算法可增加可用空间，便于文件分配。Data files should be of equal size within each filegroup, because 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 prevent 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's being written to tempdb, tempdb might have to constantly expand. 这将影响性能。That will affect performance.

要检查 tempdb 的当前大小和增长参数，请使用以下查询：To check current size and growth parameters for tempdb, 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 don't necessarily need to be on different disks or spindles unless you're also encountering I/O bottlenecks.

将 tempdb 数据库放置在用户数据库使用的磁盘以外的磁盘中。Put the tempdb database on disks that differ from the disks that user databases use.

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 run very quickly. 缓存还可以减少页分配和元数据争用问题。Caching also reduces page allocation and metadata contention.
• 改进了分配页闩锁协议，减少了所用 UP（更新）闩锁的数量。The 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 来完成此任务。You can accomplish this task by using the new UI input control in the Database Engine Configuration section and the 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 the same time and by the same amount, depending on growth settings. 不再需要跟踪标志 1117。Trace flag 1117 is no longer required.
• tempdb 中的所有分配使用统一盘区。All allocations in tempdb use uniform extents. 不再需要跟踪标志 1118。Trace flag 1118 is no longer required.
• 对于主文件组，AUTOGROW_ALL_FILES 属性已启用，且不能修改此属性。For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property can't be modified.

有关 tempdb 中性能改进的详细信息，请参阅博客文章 TEMPDB - Files and Trace Flags and Updates, Oh My!（TEMPDB - 文件和跟踪标志以及更新，天哪！）。For more information on performance improvements in tempdb, see the blog article TEMPDB - Files and Trace Flags and Updates, Oh My!.

内存优化 tempdb 元数据Memory-optimized tempdb metadata

对于 SQL ServerSQL Server 上运行的许多工作负载，tempdb 元数据争用历来是可伸缩性的瓶颈。Metadata contention in tempdb 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 元数据。introduces a new feature that's part of the in-memory database feature family: memory-optimized tempdb metadata.

此功能有效地消除了这种瓶颈，并为 tempdb 繁重的工作负荷提供了新级别的可伸缩性。This feature 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.

本视频时长 7 分钟，请观看它来大致了解如何及何时使用经过内存优化的 tempdb 元数据：Watch this seven-minute video for an overview of how and when to use memory-optimized tempdb metadata:

配置和使用内存优化 tempdb 元数据Configuring and using 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.

可使用以下 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 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. 然后，你可以禁用该功能，并在正常模式下重启 SQL Server。You can then disable the feature and restart SQL Server in normal mode.

若要防止服务器可能出现内存不足的情况，可以将 tempdb 绑定到资源池。To protect the server from potential out-of-memory conditions, you can bind tempdb to a resource pool. 这是通过 ALTER SERVER 命令（而不是将资源池绑定到数据库时通常遵循的步骤）完成的。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');

此更改还需要重新启动才能生效，即使已启用内存优化 tempdb 元数据也是如此。This change also requires a restart to take effect, even if memory-optimized tempdb metadata is already enabled.

内存优化 tempdb 限制Memory-optimized tempdb limitations

• 该功能的打开和关闭不是动态的。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.

• 单个事务无法访问多个数据库中的内存优化表。A single transaction is not allowed to access memory-optimized tables in more than one database. 涉及用户数据库中内存优化表的任何事务都无法访问同一事务中的 tempdb 系统视图。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. 如果尝试在与用户数据库中内存优化表相同的事务中访问 tempdb 系统视图，将收到以下错误：If you try to access tempdb system views in the same transaction as a memory-optimized table in a user database, you'll 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;
  

• 针对内存优化表的查询不支持锁定和隔离提示，因此针对内存优化 tempdb 目录视图的查询将不会遵循锁定和隔离提示。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. 与 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.

• 如果启用内存优化的 tempdb 元数据，则无法在临时表上创建列存储索引。Columnstore indexes can't be created on temporary tables when memory-optimized tempdb metadata is enabled.

• 由于对列存储索引的限制，启用内存优化的 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.

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 earlier, 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 空间使用情况。Run individual queries or workload trace files and monitor tempdb space use.
• 执行索引维护操作（例如重新生成索引），并监视 tempdb 空间。Execute index maintenance operations such as rebuilding indexes, and monitor tempdb space.
• 使用前面步骤中的空间使用值来预测工作负荷总使用量。Use the space-use values from the previous steps to predict your total workload usage. 为计划的并发活动调整此值，然后相应地设置 tempdb 的大小。Adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.

监视 tempdb 的使用情况Monitoring tempdb use

tempdb 中的磁盘空间不足可能会导致 SQL ServerSQL Server 生产环境中出现严重中断。Running out of disk space in tempdb can cause significant disruptions in the SQL ServerSQL Server production environment. 它还可能会阻止正在运行的应用程序完成操作。It can also 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's 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 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 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_usage 和 sys.dm_db_task_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. 这些视图有助于标识使用 tempdb 中大量磁盘空间的大型查询、临时表或表变量。These views can help you identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. 还可使用若干个计数器来监视 tempdb 中的可用空间以及正在使用 tempdb 的资源。You can also use several counters to monitor the free space that's available in tempdb and the resources that are using tempdb.

-- 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;;

相关内容Related content

用于索引的 SORT_IN_TEMPDB 选项 SORT_IN_TEMPDB option for indexes
系统数据库 System databases
sys.databases sys.databases
sys.master_files sys.master_files
移动数据库文件Move database files