December 6, 2012

Database Related Dynamic Management Views

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

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

No comments:

Post a Comment

Total Pageviews