Database Related Dynamic Management Views

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.dm_db_file_space_usage
• sys.dm_db_partition_stats
• sys.dm_db_session_space_usage
• sys.dm_db_task_space_usage

sys.dm_db_file_space_usage

Returns space usage information for each file in the database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
database_id	•	•	•	•	smallint	Database ID.
file_id	•	•	•	•	smallint	File ID. file_id maps to file_id in sys.dm_io_virtual_file_stats2 and to fileid in sys.sysfiles3.
unallocated_extent_page_count	•	•	•	•	bigint	Total number of pages in the unallocated extents in the file. For more information, see Pages and Extents4. Unused pages in allocated extents are not included.
version_store_reserved_page_count	•	•	•	•	bigint	Total number of pages in the uniform extents allocated for the version store. Version store pages are never allocated from mixed extents. IAM pages are not included, because they are always allocated from mixed extents. PFS pages are included if they are allocated from a uniform extent. For more information, see sys.dm_tran_version_store5.
user_object_reserved_page_count	•	•	•	•	bigint	Total number of pages allocated from uniform extents for user objects in the database. Unused pages from an allocated extent are included in the count. IAM pages are not included, because they are always allocated from mixed extents. PFS pages are included if they are allocated from a uniform extent. You can use the total_pages column in the sys.allocation_units6 catalog view to return the reserved page count of each allocation unit in the user object. However, note that the total_pages column includes IAM pages.
internal_object_reserved_page_count	•	•	•	•	bigint	Total number of pages in uniform extents allocated for internal objects in the file. Unused pages from an allocated extent are included in the count. IAM pages are not included, because they are always allocated from mixed extents. PFS pages are included if they are allocated from a uniform extent. There is no catalog view or dynamic management object that returns the page count of each internal object.
mixed_extent_page_count	•	•	•	•	bigint	Total number of allocated and unallocated pages in allocated mixed extents in the file. Mixed extents contain pages allocated to different objects. This count does include all the IAM pages in the file.
filegroup_id				•	smallint	Filegroup ID.
total_page_count				•	bigint	Total number of pages in the file.
allocated_extent_page_count				•	bigint	Total number of pages in the allocated extents in the file.

TSQL

Sql 2005
SELECT [database_id], [file_id], [unallocated_extent_page_count], [version_store_reserved_page_count], [user_object_reserved_page_count], [internal_object_reserved_page_count], [mixed_extent_page_count] FROM sys.dm_db_file_space_usage
Sql 2008
SELECT [database_id], [file_id], [unallocated_extent_page_count], [version_store_reserved_page_count], [user_object_reserved_page_count], [internal_object_reserved_page_count], [mixed_extent_page_count] FROM sys.dm_db_file_space_usage
Sql 2008 R2
SELECT [database_id], [file_id], [unallocated_extent_page_count], [version_store_reserved_page_count], [user_object_reserved_page_count], [internal_object_reserved_page_count], [mixed_extent_page_count] FROM sys.dm_db_file_space_usage
Sql 2012
SELECT [database_id], [file_id], [filegroup_id], [total_page_count], [allocated_extent_page_count], [unallocated_extent_page_count], [version_store_reserved_page_count], [user_object_reserved_page_count], [internal_object_reserved_page_count], [mixed_extent_page_count] FROM sys.dm_db_file_space_usage

Back to Top

---

sys.dm_db_partition_stats

Returns page and row-count information for every partition in the current database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
partition_id	•	•	•	•	bigint	ID of the partition. This is unique within a database. This is the same value as the partition_id in the sys.partitions catalog view
object_id	•	•	•	•	int	Object ID of the table or indexed view that the partition is part of.
index_id	•	•	•	•	int	ID of the heap or index the partition is part of. 0 = Heap 1 = Clustered index.
partition_number	•	•	•	•	int	1-based partition number within the index or heap.
in_row_data_page_count	•	•	•	•	bigint	Number of pages in use for storing in-row data in this partition. If the partition is part of a heap, the value is the number of data pages in the heap. If the partition is part of an index, the value is the number of pages in the leaf level. (Nonleaf pages in the B-tree are not included in the count.) IAM (Index Allocation Map) pages are not included in either case.
in_row_used_page_count	•	•	•	•	bigint	Total number of pages in use to store and manage the in-row data in this partition. This count includes nonleaf B-tree pages, IAM pages, and all pages included in the in_row_data_page_count column.
in_row_reserved_page_count	•	•	•	•	bigint	Total number of pages reserved for storing and managing in-row data in this partition, regardless of whether the pages are in use or not.
lob_used_page_count	•	•	•	•	bigint	Number of pages in use for storing and managing out-of-row text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml columns within the partition. IAM pages are included.
lob_reserved_page_count	•	•	•	•	bigint	Total number of pages reserved for storing and managing out-of-row text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml columns within the partition, regardless of whether the pages are in use or not. IAM pages are included.
row_overflow_used_page_count	•	•	•	•	bigint	Number of pages in use for storing and managing row-overflow varchar, nvarchar, varbinary, and sql_variant columns within the partition. IAM pages are included.
row_overflow_reserved_page_count	•	•	•	•	bigint	Total number of pages reserved for storing and managing row-overflow varchar, nvarchar, varbinary, and sql_variant columns within the partition, regardless of whether the pages are in use or not. IAM pages are included.
used_page_count	•	•	•	•	bigint	Total number of pages used for the partition. Computed as in_row_used_page_count + lob_used_page_count + row_overflow_used_page_count.
reserved_page_count	•	•	•	•	bigint	Total number of pages reserved for the partition. Computed as in_row_reserved_page_count + lob_reserved_page_count + row_overflow_reserved_page_count.
row_count	•	•	•	•	bigint	Number of rows within the partition.

TSQL

Sql 2005
SELECT [partition_id], [object_id], [index_id], [partition_number], [in_row_data_page_count], [in_row_used_page_count], [in_row_reserved_page_count], [lob_used_page_count], [lob_reserved_page_count], [row_overflow_used_page_count], [row_overflow_reserved_page_count], [used_page_count], [reserved_page_count], [row_count] FROM sys.dm_db_partition_stats
Sql 2008
SELECT [partition_id], [object_id], [index_id], [partition_number], [in_row_data_page_count], [in_row_used_page_count], [in_row_reserved_page_count], [lob_used_page_count], [lob_reserved_page_count], [row_overflow_used_page_count], [row_overflow_reserved_page_count], [used_page_count], [reserved_page_count], [row_count] FROM sys.dm_db_partition_stats
Sql 2008 R2
SELECT [partition_id], [object_id], [index_id], [partition_number], [in_row_data_page_count], [in_row_used_page_count], [in_row_reserved_page_count], [lob_used_page_count], [lob_reserved_page_count], [row_overflow_used_page_count], [row_overflow_reserved_page_count], [used_page_count], [reserved_page_count], [row_count] FROM sys.dm_db_partition_stats
Sql 2012
SELECT [partition_id], [object_id], [index_id], [partition_number], [in_row_data_page_count], [in_row_used_page_count], [in_row_reserved_page_count], [lob_used_page_count], [lob_reserved_page_count], [row_overflow_used_page_count], [row_overflow_reserved_page_count], [used_page_count], [reserved_page_count], [row_count] FROM sys.dm_db_partition_stats

Back to Top

---

sys.dm_db_session_space_usage

Returns the number of pages allocated and deallocated by each session for the database. Note This view is applicable only to the tempdb database1.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
session_id	•	•	•	•	smallint	Session ID. session_id maps to session_id in sys.dm_exec_sessions2.
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.

TSQL

Sql 2005
SELECT [session_id], [database_id], [user_objects_alloc_page_count], [user_objects_dealloc_page_count], [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count] FROM sys.dm_db_session_space_usage
Sql 2008
SELECT [session_id], [database_id], [user_objects_alloc_page_count], [user_objects_dealloc_page_count], [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count] FROM sys.dm_db_session_space_usage
Sql 2008 R2
SELECT [session_id], [database_id], [user_objects_alloc_page_count], [user_objects_dealloc_page_count], [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count] FROM sys.dm_db_session_space_usage
Sql 2012
SELECT [session_id], [database_id], [user_objects_alloc_page_count], [user_objects_dealloc_page_count], [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count] FROM sys.dm_db_session_space_usage

Back to Top

---

sys.dm_db_task_space_usage

Returns page allocation and deallocation activity by task for the database. Note This view is applicable only to the tempdb database1.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	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_tasks2.
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.

TSQL

Sql 2005
SELECT [session_id], [request_id], [exec_context_id], [database_id], [user_objects_alloc_page_count], [user_objects_dealloc_page_count], [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count] FROM sys.dm_db_task_space_usage
Sql 2008
SELECT [session_id], [request_id], [exec_context_id], [database_id], [user_objects_alloc_page_count], [user_objects_dealloc_page_count], [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count] FROM sys.dm_db_task_space_usage
Sql 2008 R2
SELECT [session_id], [request_id], [exec_context_id], [database_id], [user_objects_alloc_page_count], [user_objects_dealloc_page_count], [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count] FROM sys.dm_db_task_space_usage
Sql 2012
SELECT [session_id], [request_id], [exec_context_id], [database_id], [user_objects_alloc_page_count], [user_objects_dealloc_page_count], [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count] FROM sys.dm_db_task_space_usage

Back to Top

---