sys.dm_db_session_space_usage (Transact-SQL)
Returns the number of pages allocated and deallocated by each session for the database.
Note
|
|---|
|
This view is applicable only to the tempdb database. |
|
Column name |
Data type |
Description |
|---|---|---|
|
session_id |
smallint |
Session ID. session_id maps to session_id in sys.dm_exec_sessions. |
|
database_id |
smallint |
Database ID. |
|
user_objects_alloc_page_count |
bigint |
Number of pages reserved or allocated for user objects by this session. |
|
user_objects_dealloc_page_count |
bigint |
Number of pages deallocated and no longer reserved for user objects by this session. |
|
internal_objects_alloc_page_count |
bigint |
Number of pages reserved or allocated for internal objects by this session. |
|
internal_objects_dealloc_page_count |
bigint |
Number of pages deallocated and no longer reserved for internal objects by this session. |
IAM pages are not included in any of the allocation or deallocation counts reported by this view.
Page counters are initialized to zero (0) at the start of a session. The counters track the total number of pages that have been allocated or deallocated for tasks that are already completed in the session. The counters are updated only when a task ends; they do not reflect running tasks.
A session can have multiple requests active at the same time. A request can start multiple threads, tasks, if it is a parallel query.
For more information about the sessions, requests, and tasks, see sys.dm_exec_sessions (Transact-SQL), sys.dm_exec_requests (Transact-SQL), and sys.dm_os_tasks (Transact-SQL).
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
Note