Home » Blog » Partitions, Boundaries and Filegroups

Partitions, Boundaries and Filegroups

2012-07-08 - General, SQL Server Internals

Introduction

Earlier today on twitter I ran across an interesting question. The question was, how to identify all partitions and their boundaries for a given filegroup. The solution is not as trivial as it may sound, so I will expand on it here a little.

Partitions and Filegroups

First we need to find out which filegroup a partition is on. All partitions are listed in the sys.partitions view. That view has an object_id column that we can use to join to sys.tables. Joining to sys.tables makes sure we are dealing only with partitions of user tables. sys.partitions has a hobt_id column that allows us to join to sys.allocation_units on its container_id column. Each table and each index has at least one partition. All normal data pages of a partition are stored together in a single allocation unit. The sys.allocation_units view also has a data_space_id column that can be used to join to sys.filegroups.

Partition Functions

To get from a partition to its partition function we need to first join sys.partitions with sys.indexes on both the object_id and on the index_id columns. sys.indexes also has a data_space_id column that we can use to join to sys.partition_schemes.data_space_id. From sys.partition_schemes we get to sys.partition_functions using the function_id column of both tables.

Boundaries

A partition function defines the boundaries of the partitions. The first partition does not have a lower boundary and the last partition does not have an upper boundary. Wether the boundary value itself belongs to its left or its right partition is specified by the LEFT or the RIGHT key word that was used at the time the partition function was created. It is always the same for all boundaries of a single partition function. Which one was used can be seen in the boundary_value_on_right column of the sys.partition_functions view. The actual boundary values can be found in the value column of the sys.partition_range_values view.

As each partition has up to two boundaries, we need to join to sys.partition_range_values twice. The function_id column ties it to the sys.partition_functions view. The boundary_id can be correlated with the partition_number column of sys.partitions. The first partition has boundary one as upper boundary; the second partition has boundary one as lower boundary and boundary two as upper boundary. So for the lower boundary the join condition has to be sys.partition_range_values.boundary_id + 1 = sys.partitions.partition_number and for the upper boundary it is sys.partition_range_values.boundary_id = sys.partitions.partition_number.

Final Solution

When putting this all together, you need to pay attention to the fact, that while every table has at least one partition, not everyone is located on a partition scheme. Also, not every partition of a partitioned table has both the upper and the lower boundary defined. That means that the four joins to the partition scheme related views have to be outer joins.

The final query can be found below.

[sql] SELECT f.data_space_id,
f.NAME AS file_group_name,
SCHEMA_NAME(t.schema_id) AS table_schema,
t.name AS table_name,
p.partition_number,
ps.name AS partition_schem_name,
pf.name AS partition_function_name,
pf.boundary_value_on_right,
left_prv.value AS left_range,
right_prv.value AS right_value,
ISNULL(STR(CAST(left_prv.value AS BIGINT)), '-INF')
+ CASE WHEN pf.boundary_value_on_right = 0 THEN ' < '
ELSE ' <= '
END + 'X' + CASE WHEN pf.boundary_value_on_right = 0 THEN ' <= '
ELSE ' < '
END + ISNULL(STR(CAST(right_prv.value AS BIGINT)), 'INF') AS range_desc
FROM sys.partitions p
JOIN sys.tables t
ON p.object_id = t.object_id
JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.allocation_units au
ON p.hobt_id = au.container_id
JOIN sys.filegroups f
ON au.data_space_id = f.data_space_id
LEFT JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.partition_functions pf
ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values left_prv
ON left_prv.function_id = ps.function_id
AND left_prv.boundary_id + 1 = p.partition_number
LEFT JOIN sys.partition_range_values right_prv
ON right_prv.function_id = ps.function_id
AND right_prv.boundary_id = p.partition_number;
[/sql]