sys.dm_db_task_space_usage (Transact-SQL)sys.dm_db_task_space_usage (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

返回任务为数据库进行的页分配和释放活动。Returns page allocation and deallocation activity by task for the database.

备注

此视图仅适用于 tempdb 数据库This view is applicable only to the tempdb database.

备注

若要从或调用此 Azure Synapse AnalyticsAzure Synapse Analytics 并行数据仓库Parallel Data Warehouse ,请使用名称 sys.dm_pdw_nodes_db_task_space_usageTo call this from Azure Synapse AnalyticsAzure Synapse Analytics or 并行数据仓库Parallel Data Warehouse, use the name sys.dm_pdw_nodes_db_task_space_usage.

列名称Column name 数据类型Data type 说明Description
session_idsession_id smallintsmallint 会话 ID。Session ID.
request_idrequest_id intint 会话内的请求 ID。Request ID within the session.

请求也称为批,可以包含一个或多个查询。A request is also called a batch and may contain one or more queries. 一个会话可以同时具有多个活动请求。A session may have multiple requests active at the same time. 如果使用并行执行计划,则请求中的每个查询可以启动多个线程(任务)。Each query in the request may start multiple threads (tasks), if a parallel execution plan is used.
exec_context_idexec_context_id intint 任务的执行上下文 ID。Execution context ID of the task. 有关详细信息,请参阅 (transact-sql)sys.dm_os_tasks For more information, see sys.dm_os_tasks (Transact-SQL).
database_iddatabase_id smallintsmallint 数据库 ID。Database ID.
user_objects_alloc_page_countuser_objects_alloc_page_count bigintbigint 此任务为用户对象保留或分配的页数。Number of pages reserved or allocated for user objects by this task.
user_objects_dealloc_page_countuser_objects_dealloc_page_count bigintbigint 此任务为用户对象释放并不再保留的页数。Number of pages deallocated and no longer reserved for user objects by this task.
internal_objects_alloc_page_countinternal_objects_alloc_page_count bigintbigint 此任务为内部对象保留或分配的页数。Number of pages reserved or allocated for internal objects by this task.
internal_objects_dealloc_page_countinternal_objects_dealloc_page_count bigintbigint 此任务为内部对象释放并不再保留的页数。Number of pages deallocated and no longer reserved for internal objects by this task.
pdw_node_idpdw_node_id intint 适用 于: Azure Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data WarehouseApplies to: Azure Synapse AnalyticsAzure Synapse Analytics, 并行数据仓库Parallel Data Warehouse

此分发所在的节点的标识符。The identifier for the node that this distribution is on.

权限Permissions

在上 SQL ServerSQL Server ,需要 VIEW SERVER STATE 权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
在 SQL 数据库的基本、S0 和 S1 服务目标上,对于弹性池中的数据库, 服务器管理员 帐户或 Azure Active Directory 管理员 帐户是必需的。On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account or the Azure Active Directory admin account is required. 对于所有其他 SQL 数据库服务目标, VIEW DATABASE STATE 数据库中需要该权限。On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.

备注Remarks

此视图所报告的任何页计数均不包括 IAM 页。IAM pages are not included in any of the page counts reported by this view.

在请求开始时,页计数器被初始化为零 (0)。Page counters are initialized to zero (0) at the start of a request. 在请求完成时,这些值将在会话级别聚合。These values are aggregated at the session level when the request is completed. 有关详细信息,请参阅 sys.dm_db_session_space_usage (Transact-SQL)For more information, see sys.dm_db_session_space_usage (Transact-SQL).

工作表缓存、临时表缓存和延迟的删除操作会影响在指定任务中分配和释放的页数。Work table caching, temporary table caching, and deferred drop operations affect the number of pages allocated and deallocated in a specified task.

用户对象User Objects

用户对象页计数器中包括下列对象:The following objects are included in the user object page counters:

  • 用户定义的表和索引User-defined tables and indexes

  • 系统表和索引System tables and indexes

  • 全局临时表和索引Global temporary tables and indexes

  • 局部临时表和索引Local temporary tables and indexes

  • 表变量Table variables

  • 表值函数中返回的表Tables returned in the table-valued functions

内部对象Internal Objects

内部对象仅在 tempdb 中。Internal objects are only in tempdb. 内部对象页计数器中包括下列对象:The following objects are included in the internal object page counters:

  • 用于游标或假脱机操作以及临时大型对象 (LOB) 存储的工作表Work tables for cursor or spool operations and temporary large object (LOB) storage

  • 用于哈希联接等操作的工作文件Work files for operations such as a hash join

  • 排序段Sort runs

物理联接Physical Joins

sys.dm_db_session_task_usage 的物理联接Physical joins for sys.dm_db_session_task_usage

关系基数Relationship Cardinalities

FromFrom 功能To RelationshipRelationship
dm_db_task_space_usage.request_iddm_db_task_space_usage.request_id dm_exec_requests.request_iddm_exec_requests.request_id 一对一One-to-one
dm_db_task_space_usage.session_iddm_db_task_space_usage.session_id dm_exec_requests.session_iddm_exec_requests.session_id 一对一One-to-one

另请参阅See Also

动态管理视图和函数 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
与数据库相关的动态管理视图 (Transact-sql) Database Related Dynamic Management Views (Transact-SQL)
sys.dm_exec_sessions (Transact-SQL) sys.dm_exec_sessions (Transact-SQL)
sys.dm_exec_requests (Transact-SQL) sys.dm_exec_requests (Transact-SQL)
sys.dm_os_tasks (Transact-sql) sys.dm_os_tasks (Transact-SQL)
sys.dm_db_session_space_usage (Transact-sql) sys.dm_db_session_space_usage (Transact-SQL)
sys.dm_db_file_space_usage (Transact-SQL)sys.dm_db_file_space_usage (Transact-SQL)