Skip to content

sys-dm-db-task-space-usage-transact-sql.md

Latest commit

 

History

History
107 lines (79 loc) · 5.79 KB

sys-dm-db-task-space-usage-transact-sql.md

File metadata and controls

107 lines (79 loc) · 5.79 KB
title sys.dm_db_task_space_usage (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/15/2017
ms.prod sql-non-specified
ms.reviewer
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
dm_db_task_space_usage_TSQL
sys.dm_db_task_space_usage_TSQL
dm_db_task_space_usage
sys.dm_db_task_space_usage
dev_langs
TSQL
helpviewer_keywords
sys.dm_db_task_space_usage dynamic management view
ms.assetid fb0c87e5-43b9-466a-a8df-11b3851dc6d0
caps.latest.revision 29
author BYHAM
ms.author rickbyh
manager jhubbard

sys.dm_db_task_space_usage (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

Returns page allocation and deallocation activity by task for the database.

Note

This view is applicable only to the tempdb database.

Note

To call this from [!INCLUDEssSDWfull] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_db_task_space_usage.

Column name Data type Description
session_id smallint Session ID.
request_id int 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_id int Execution context ID of the task. For more information, see sys.dm_os_tasks (Transact-SQL).
database_id smallint Database ID.
user_objects_alloc_page_count bigint Number of pages reserved or allocated for user objects by this task.
user_objects_dealloc_page_count bigint Number of pages deallocated and no longer reserved for user objects by this task.
internal_objects_alloc_page_count bigint Number of pages reserved or allocated for internal objects by this task.
internal_objects_dealloc_page_count bigint Number of pages deallocated and no longer reserved for internal objects by this task.
pdw_node_id int Applies to: [!INCLUDEssSDWfull], [!INCLUDEssPDW]

The identifier for the node that this distribution is on.

Permissions

On [!INCLUDEssNoVersion] requires VIEW SERVER STATE permission on the server.

On [!INCLUDEssSDS] Premium Tiers requires the VIEW DATABASE STATE permission in the database. On [!INCLUDEssSDS] Standard and Basic Tiers requires the [!INCLUDEssSDS] admin account.

Remarks

IAM pages are not included in any of the page counts reported by this view.

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

Internal objects are only in tempdb. The following objects are included in the internal object page counters:

  • 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

Physical joins for sys.dm_db_session_task_usage

Relationship Cardinalities

From To Relationship
dm_db_task_space_usage.request_id dm_exec_requests.request_id One-to-one
dm_db_task_space_usage.session_id dm_exec_requests.session_id One-to-one

See Also

Dynamic Management Views and Functions (Transact-SQL)
Database Related Dynamic Management Views (Transact-SQL)
sys.dm_exec_sessions (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sys.dm_os_tasks (Transact-SQL)
sys.dm_db_session_space_usage (Transact-SQL)
sys.dm_db_file_space_usage (Transact-SQL)