Lately I have been working with a database that has a comprehensive table partitioning requirement. In order to do troubleshooting, and to test partition switching and data archiving code, we needed a quick but accurate way to view what rows are in which partition for each table. Importantly, we needed to align the filegroup/disk where the rows are all the way back to the value in the partition function that causes the rows to land where they land. In short, “Where are the rows on disk for December 2012 for this table? How many are there?”
There are some examples of how to join the DMVs out on the web, but there seemed to be one detail missing from a lot of them: the number of partitions is always one more than the number of boundary values in a partition function. The arrangement is really like this:
ROWS | ROWS | ROWS | ROWS | ROWS | ROWS | ROWS
PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION PARTITION
BOUNDARY BOUNDARY BOUNDARY BOUNDARY BOUNDARY BOUNDARY
The number of boundaries in a partition function is (number of partitions) - 1. That means that if you query the various DMVs for partition function, partition scheme, boundary values, partitions and filegroups all joined together, it’s deceptively easy to be off by one, and mistake having rows for, say, 2012-01 in partition 6 instead of where they really are in partition 7. If there are 10 million rows there, that be a major mistake.
For me the key to this is incorporating the RANGE RIGHT or RANGE LEFT value for the boundary, which determines if that boundary value belongs to the partition on its left or the partition on its right. That T-SQL option when you create the partition function is represented in the DMVs as the column sys.partition_functions.boundary_value_on_right.
Here’s an example that I think makes the partitions and the partition boundary values line up correctly, by incorporating the value of boundary_value_on_right to bump the partition_range_values boundary_id by one relative to sys.partitions partition_number.
SELECT schema_name( o.schema_id ) schemaname,
FROM sys.objects o
JOIN sys.indexes i on o.object_id = i.object_id
JOIN sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
JOIN sys.data_spaces ds on i.data_space_id = ds.data_space_id
JOIN sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf on pf.function_id = ps.function_id
JOIN sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
AND p.partition_number = dds.destination_id
JOIN sys.filegroups fg on dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values pv on pv.function_id = pf.function_id
AND p.partition_number - pf.boundary_value_on_right = pv.boundary_id
WHERE i.index_id in ( 0, 1 )
AND SCHEMA_NAME( o.schema_id ) = 'dbo'
AND o.name = 'someTable'
-- AND p.rows > 0
ORDER BY o.name, SCHEMA_NAME( o.schema_id ), p.partition_number
OPTION (FORCE ORDER)