sys.dm_db_partition_stats (Transact-SQL)

sys.dm_db_partition_stats (Transact...

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

sys.dm_db_partition_stats (Transact-SQL)

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

Column name	Data 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.

Remarks

sys.dm_db_partition_stats displays information about the space used to store and manage in-row data LOB data, and row-overflow data for all partitions in a database. One row is displayed per partition.

The counts on which the output is based are cached in memory or stored on disk in various system tables.

In-row data, LOB data, and row-overflow data represent the three allocation units that make up a partition. For more information about allocation units, see Table and Index Organization. The sys.allocation_units catalog view can be queried for metadata about each allocation unit in the database.

If a heap or index is not partitioned, it is made up of one partition (with partition number = 1); therefore, only one row is returned for that heap or index. For more information about partitions, see Table and Index Organization. The sys.partitions catalog view can be queried for metadata about each partition of all the tables and indexes in a database.

The total count for an individual table or an index can be obtained by adding the counts for all relevant partitions.

Permissions

Requires VIEW DATABASE STATE permission to query the sys.dm_db_partition_stats dynamic management view. For more information about permissions on dynamic management views, see Dynamic Management Views and Functions (Transact-SQL).

Examples

A. Returning all counts for all partitions of all indexes and heaps in a database

The following example shows all counts for all partitions of all indexes and heaps in the AdventureWorks database.

USE AdventureWorks;
GO
SELECT * FROM sys.dm_db_partition_stats;
GO

B. Returning all counts for all partitions of a table and its indexes

The following example shows all counts for all partitions of the HumanResources.Employee table and its indexes.

USE AdventureWorks;
GO
SELECT * FROM sys.dm_db_partition_stats 
WHERE object_id = OBJECT_ID('HumanResources.Employee');
GO

C. Returning total used pages and total number of rows for a heap or clustered index

The following example returns total used pages and total number of rows for the heap or clustered index of the HumanResources.Employee table. Because the Employee table is not partitioned by default, note the sum includes only one partition.

USE AdventureWorks;
GO
SELECT SUM(used_page_count) AS total_number_of_used_pages, 
    SUM (row_count) AS total_number_of_rows 
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('HumanResources.Employee')    AND (index_id=0 or index_id=1);
GO

A. Returning all counts for all partitions of all indexes and heaps in a database

B. Returning all counts for all partitions of a table and its indexes

C. Returning total used pages and total number of rows for a heap or clustered index

Reference

Other Resources

Help and Information