--- title: "sys.dm_db_task_space_usage (Transact-SQL)" description: sys.dm_db_task_space_usage returns page allocation and deallocation activity by task for the database. author: rwestMSFT ms.author: randolphwest ms.date: "02/24/2023" ms.service: sql ms.subservice: system-objects ms.topic: "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" helpviewer_keywords: - "sys.dm_db_task_space_usage dynamic management view" dev_langs: - "TSQL" monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current" --- # sys.dm_db_task_space_usage (Transact-SQL) [!INCLUDE [sql-asdb-asdbmi-asa-pdw](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)] Returns page allocation and deallocation activity by task for the database. > [!NOTE] > This view is applicable only to the [tempdb database](../../relational-databases/databases/tempdb-database.md). > [!NOTE] > To call this from [!INCLUDE[ssazuresynapse-md](../../includes/ssazuresynapse-md.md)] or [!INCLUDE[ssPDW](../../includes/sspdw-md.md)], use the name `sys.dm_pdw_nodes_db_task_space_usage`. [!INCLUDE[synapse-analytics-od-unsupported-syntax](../../includes/synapse-analytics-od-unsupported-syntax.md)] |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)](../../relational-databases/system-dynamic-management-views/sys-dm-os-tasks-transact-sql.md).| |**database_id**|**smallint**|Database ID.

In [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)], the values are unique within a single database or an elastic pool, but not within a logical server.| |**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**: [!INCLUDE[ssazuresynapse-md](../../includes/ssazuresynapse-md.md)], [!INCLUDE[ssPDW](../../includes/sspdw-md.md)]

The identifier for the node that this distribution is on.| ## Permissions On [!INCLUDE[ssNoVersion_md](../../includes/ssnoversion-md.md)] and SQL Managed Instance, requires `VIEW SERVER STATE` permission. On SQL Database **Basic**, **S0**, and **S1** service objectives, and for databases in **elastic pools**, the [server admin](/azure/azure-sql/database/logins-create-manage#existing-logins-and-user-accounts-after-creating-a-new-database) account, the [Microsoft Entra admin](/azure/azure-sql/database/authentication-aad-overview#administrator-structure) account, or membership in the `##MS_ServerStateReader##` [server role](/azure/azure-sql/database/security-server-roles) is required. On all other SQL Database service objectives, either the `VIEW DATABASE STATE` permission on the database, or membership in the `##MS_ServerStateReader##` server role is required. ### Permissions for SQL Server 2022 and later Requires VIEW SERVER PERFORMANCE STATE permission on the server. ## 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)](../../relational-databases/system-dynamic-management-views/sys-dm-db-session-space-usage-transact-sql.md). 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 :::image type="content" source="../../relational-databases/system-dynamic-management-views/media/join-dm-db-task-space-usage-1.svg" alt-text="Diagram of physical joins for sys.dm_db_task_space_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| ## Next steps [Dynamic Management Views and Functions (Transact-SQL)](~/relational-databases/system-dynamic-management-views/system-dynamic-management-views.md) [Database Related Dynamic Management Views (Transact-SQL)](../../relational-databases/system-dynamic-management-views/database-related-dynamic-management-views-transact-sql.md) [sys.dm_exec_sessions (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql.md) [sys.dm_exec_requests (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql.md) [sys.dm_os_tasks (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-os-tasks-transact-sql.md) [sys.dm_db_session_space_usage (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-db-session-space-usage-transact-sql.md) [sys.dm_db_file_space_usage (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql.md)