When you’re designing or managing partitioned tables, it’s useful to quickly verify:
- Which tables are partitioned
- The type of partition function they use (left or right)
- Which boundary points are assigned to which filegroup
- How many rows and pages are in each partition (and which boundary point they’re associated with)
This helps make sure that you’re designing your tables correctly, and it also helps you avoid goofs like merging the wrong boundary point and causing a bunch of data to move into another– which can be slow and painful.
All this information is available in TSQL, it’s just an ugly query, and it doesn’t come in any built-in reports or views.
So I’ve got an ugly query for you!
Query Listing Partitioned Tables with Boundary Point, Filegroup, Row Count, Partition Size, and Partition Number By Index
This query gives an overview of partitioned tables and indexes in a database. The query is also in a Gist, if you prefer.
SELECT sc.name + N'.' + so.name as [Schema.Table], si.index_id as [Index ID], si.type_desc as [Structure], si.name as [Index], stat.row_count AS [Rows], stat.in_row_reserved_page_count * 8./1024./1024. as [In-Row GB], stat.lob_reserved_page_count * 8./1024./1024. as [LOB GB], p.partition_number AS [Partition #], pf.name as [Partition Function], CASE pf.boundary_value_on_right WHEN 1 then 'Right / Lower' ELSE 'Left / Upper' END as [Boundary Type], prv.value as [Boundary Point], fg.name as [Filegroup] FROM sys.partition_functions AS pf JOIN sys.partition_schemes as ps on ps.function_id=pf.function_id JOIN sys.indexes as si on si.data_space_id=ps.data_space_id JOIN sys.objects as so on si.object_id = so.object_id JOIN sys.schemas as sc on so.schema_id = sc.schema_id JOIN sys.partitions as p on si.object_id=p.object_id and si.index_id=p.index_id LEFT JOIN sys.partition_range_values as prv on prv.function_id=pf.function_id and p.partition_number= CASE pf.boundary_value_on_right WHEN 1 THEN prv.boundary_id + 1 ELSE prv.boundary_id END /* For left-based functions, partition_number = boundary_id, for right-based functions we need to add 1 */ JOIN sys.dm_db_partition_stats as stat on stat.object_id=p.object_id and stat.index_id=p.index_id and stat.index_id=p.index_id and stat.partition_id=p.partition_id and stat.partition_number=p.partition_number JOIN sys.allocation_units as au on au.container_id = p.hobt_id and au.type_desc ='IN_ROW_DATA' /* Avoiding double rows for columnstore indexes. */ /* We can pick up LOB page count from partition_stats */ JOIN sys.filegroups as fg on fg.data_space_id = au.data_space_id ORDER BY [Schema.Table], [Index ID], [Partition Function], [Partition #]; GO
Column Definitions and Notes
- Schema.Table: Schema name concatenated with table name
- Index ID: Included for reference and ordering
- Structure: This will decode if it’s a partitioned heap, clustered index, nonclustered index, clustered columnstore index, or nonclustered columnstore index
- Index Name: What it sounds like
- Rows: Number of rows in that partition
- In-Row GB: Reserved in-row pages for that partition
- LOB GB: Reserved LOB pages for that partition (reminder – columnstore indexes use LOB pages)
- Partition #: This can be useful in some queries. Remember that partition numbers are reassigned when you modify your partition function (split/merge)
- Partition Function Name: The partition function is the “algorithm” that defines the boundary points for the partitions
- Boundary Type: Whether the boundary point is a “right” type (lower inclusive boundary) or a “left” type (upper inclusive boundary)
- Boundary Point: The value of the boundary point that goes with that particular partition
- Filegroup: Where the data is located (defined by the partition scheme)
If you need to know the partition scheme name, it’s easy to add that column in (sys.partition_schemes is already in the query). The partition scheme is what maps your partition function to the filegroups. In most cases, people just want to know where things currently are, so I left that out of the query.