Count pages occupied by partition

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

In_row_data_page_count: 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 the data pages in the heap. If the partition is part of a index, the value is the number of pages in the leaf level. (nonleaf pages in the B-tree is not included in the count.) IAM (Index Allocation Map) pages is not included in either case. Always 0 for an xvelocity memory optimized Columnstore index.

in_row_used_page_count: Total number of pages in use to store and manage the In-row data in this partition. This is count includes nonleaf b-tree pages, IAM pages, and all pages included in the in_row_data_page_count column. Always 0 for a columnstore index.

in_row_reserved_page_count: Total number of pages reserved for storing and managing In-row data in this partition, Regardless of whether the pages is in use or not. Always 0 for a columnstore index.

Selectps.partition_id, PS.object_id, ps.index_id, Ps.partition_number, Ps.in_row_data_page_count, Ps.in_row_used_page_count, Ps.in_row_res Erved_page_count, Ps.lob_used_page_count, Ps.lob_reserved_page_count, Ps.row_overflow_used_page_count, Ps.row_ Overflow_reserved_page_count, Ps.used_page_count, Ps.reserved_page_count, Ps.row_count fromsys.dm_db_partition_stats PSInner Joinsys.objects o onPs.object_id=O.object_idwhereO.name=N'Users'

Reference Documentation:

SYS.DM_DB_PARTITION_STATS (Transact-SQL)

Count pages occupied by partition