I sometimes want to know how my data is spread across different partitions in a table or index – after all, this can affect performance and storage a great deal, and if the data is really badly skewed, most or all of it could be stuck in a single partition, rendering the partitioning scheme pretty much useless in the first place.
You can use dynamic management views to find out how your data is spread across different partitions, and how those partitions are delimited, in “plain english”. Here’s how!
Here’s the basic information we’re looking for:
SELECT * FROM sys.partition_range_values;
This DMV contains one row for each partition, for each partitioning function. The “value” column of sys.partition_range_values is a sql_variant value, so if you want to use or display it, you’ll have to explicitly convert it to another datatype first.Here, we’ve cleaned up the output data a bit for readability:
SELECT val.function_id, val.boundary_id, pf.boundary_value_on_right, (CASE SQL_VARIANT_PROPERTY(val.[value], 'BaseType') --- Convert to "native" date format: WHEN 'date' THEN '{d '''+LEFT(CONVERT(varchar(max), val.[value], 120), 10)+'''}' --- Convert to datetime (timestamp) format: WHEN 'datetime' THEN '{ts '''+CONVERT(varchar(max), val.[value], 120)+'''}' --- Everything else is just plain CAST() to varchar: ELSE CAST(val.[value] AS varchar(max)) END) AS boundary_value FROM sys.partition_range_values AS val INNER JOIN sys.partition_functions AS pf ON val.function_id = pf.function_id ORDER BY val.function_id, val.boundary_id;
Partitioning functions come in two distinctly different flavours, defined by whether the “boundary value” of each partition is on the right or left (lower or upper) end of the partition. This is indicated by a bit column in sys.partition_functions, boundary_value_on_right. If the boundary value for a given partition is 100 and the boundary is on the right (upper) side, all values in this partition will be less than 100. However, if the boundary is on the left side, the partition will contain values up to and including 100. Conversely, the next partition will start at 100 if the boundary is on the right, and at 101 if the boundary is on the left.
Now that we have all the partitioning boundaries along with the knowledge if the partition function is bounded “right” or “left”, we can put this query in a common table expression and join the lower and upper boundary for each partition together.
We’ll use a FULL JOIN here, because the first partition will not have a lower boundary, and the last one doesn’t have an upper boundary.
Here’s the query:
WITH spc (data_space_id, function_id, boundary_id,
boundary_value_on_right, boundary_value)
AS (--- Common table expression containing all partition boundaries
SELECT ps.data_space_id,
val.function_id,
val.boundary_id,
pf.boundary_value_on_right,
(CASE SQL_VARIANT_PROPERTY(val.[value], 'BaseType')
WHEN 'date' THEN
'{d '''+LEFT(CONVERT(varchar(max), val.[value], 120), 10)+'''}'
WHEN 'datetime' THEN
'{ts '''+CONVERT(varchar(max), val.[value], 120)+'''}'
ELSE CAST(val.[value] AS varchar(max))
END) AS boundary_value
FROM sys.partition_range_values AS val
INNER JOIN sys.partition_functions AS pf ON
val.function_id = pf.function_id
INNER JOIN sys.partition_schemes AS ps ON
pf.function_id=ps.function_id)
--- ... joined together in a FULL JOIN:
SELECT ISNULL(a.function_id, b.function_id) AS function_id,
ISNULL(a.data_space_id, b.data_space_id) AS data_space_id,
ISNULL(a.boundary_id+1, b.boundary_id) AS boundary_id,
a.boundary_value+(CASE
WHEN a.boundary_value_on_right=1 THEN '<='
WHEN a.boundary_value_on_right=0 THEN '<'
END) AS lower_rng,
(CASE
WHEN b.boundary_value_on_right=1 THEN '<'+b.boundary_value
WHEN b.boundary_value_on_right=0 THEN '<='+b.boundary_value
END) AS upper_rng
FROM spc AS a
FULL JOIN spc AS b ON
a.function_id=b.function_id AND
a.boundary_id+1=b.boundary_id
ORDER BY 1, 3;
Now, all that remains to do is to connect this query to actual tables and indexes. Note that partitioning functions can be applied to indexes and tables independently, so you’ll have to make sure not to mix those up!
The way to do this is by using the data_space_id column in sys.partition_schemes, which maps to the data_space_id column in sys.indexes. From the index, in turn, you’ll find the table.
Finally, in this example, we’ll also LEFT JOIN sys.partitions, which contains one row for each partition in the database, to get information about the (estimated) number of rows in each partition as well as if we’re using any data compression on it.
--- Partition boundaries... WITH spc (data_space_id, function_id, boundary_id, boundary_value_on_right, boundary_value) AS ( SELECT ps.data_space_id, val.function_id, val.boundary_id, pf.boundary_value_on_right, (CASE SQL_VARIANT_PROPERTY(val.[value], 'BaseType') WHEN 'date' THEN '{d '''+LEFT(CONVERT(varchar(max), val.[value], 120), 10)+'''}' WHEN 'datetime' THEN '{ts '''+CONVERT(varchar(max), val.[value], 120)+'''}' ELSE CAST(val.[value] AS varchar(max)) END) AS boundary_value FROM sys.partition_range_values AS val INNER JOIN sys.partition_functions AS pf ON val.function_id = pf.function_id INNER JOIN sys.partition_schemes AS ps ON pf.function_id=ps.function_id), --- Ranges, i.e. lower and upper bounds of a partition: rng (function_id, data_space_id, boundary_id, lower_rng, upper_rng) AS ( SELECT ISNULL(a.function_id, b.function_id), ISNULL(a.data_space_id, b.data_space_id), ISNULL(a.boundary_id+1, b.boundary_id), a.boundary_value+(CASE WHEN a.boundary_value_on_right=1 THEN '<=' WHEN a.boundary_value_on_right=0 THEN '<' END) AS lower_rng, (CASE WHEN b.boundary_value_on_right=1 THEN '<'+b.boundary_value WHEN b.boundary_value_on_right=0 THEN '<='+b.boundary_value END) AS upper_rng FROM spc AS a FULL JOIN spc AS b ON a.function_id=b.function_id AND a.boundary_id+1=b.boundary_id) --- ... joined with sys.indexes and sys.tables: SELECT p.[object_id], SCHEMA_NAME(t.[schema_id])+'.'+t.[name] AS [object_name], ix.index_id, ix.[name] AS index_name, pf.[name] AS pf_name, rng.boundary_id, ISNULL(rng.lower_rng, '')+'key'+ISNULL(rng.upper_rng, '') AS boundary, p.[rows], p.data_compression_desc FROM rng INNER JOIN sys.partition_functions AS pf ON rng.function_id = pf.function_id INNER JOIN sys.indexes AS ix ON ix.data_space_id=rng.data_space_id LEFT JOIN sys.tables AS t ON ix.[object_id]=t.[object_id] LEFT JOIN sys.partitions AS p ON ix.[object_id]=p.[object_id] AND ix.index_id=p.index_id AND rng.boundary_id=p.partition_number ORDER BY t.[schema_id], t.[name], p.index_id, rng.boundary_id;
The one thing I couldn’t figure out from the DMVs are what file group each respective partition uses, but I suppose that this isn’t as important anyway.
Let me know in the comments if you find this article useful, and if there’s anything I’ve left out.