sys.dm_db_task_space_usage (Transact-SQL)
Returns page allocation and deallocation activity by task for the database.
 Note |
|---|
This view is applicable only to the tempdb database. |
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. |
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
For examples that use the sys.dm_db_task_space_usage view, see Troubleshooting Insufficient Disk Space in tempdb.
