Get Row Counts For Partitioned Tables

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.

Advertisements

One thought on “Get Row Counts For Partitioned Tables

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s