I’m adding this query to my SQL utility belt. This query tells you, for a specific table, all of the partitions, the lowest value in that partition’s range, and the (estimated) number of records in that partition.
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName, o.name AS TableName, ddps.partition_number AS PartitionNumber, prv.value AS EndValue, ddps.row_count AS [RowCount] FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.dm_db_partition_stats AS ddps ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id INNER JOIN sys.partition_range_values prv ON ps.function_id = prv.function_id AND ddps.partition_number = prv.boundary_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 AND o.name LIKE '%TABLENAME%' ORDER BY SchemaName, TableName, PartitionNumber;
This query has a couple of good uses. The first is that you can monitor a backfill with it. In my scenario, I’m loading several years worth of data into a new fact table. SSIS is loading data one day at a time so that I don’t leave a connection open so long that it gets killed. Running this query shows me approximately how far along I am, as I know the valid date range for data in this table and I can watch the partitions as they fill up.
Another use for this is to check to make sure that your partitions are not skewed too heavily. For example, my partitions are pretty tightly defined: they are all (except for the current partition) within a 15-20% of average, meaning that they’re pretty close to a uniform distribution. That really helps in cases in which I can eliminate partitions, as this means that when the optimizer can throw away 47 out of 48 partitions, it can throw away about 47/48ths of the data, regardless of which partition remains. I would consider that a good partition. At the opposite extreme, where almost all data is stored in a single partition, you get zero benefit from partitioning but still have to deal with the DBA overhead involved, and that table might be a candidate for re-thinking a partitioning strategy or eliminating partitioning altogether.