By Richie Lee

I’ve been working with data warehouses again recently and needed to help out the rest of the team who were not familiar with partitioning in SQL Server. It’s one of those subjects that really requires hands on experience with to truly appreciate the mechanics of partitioning, and for me it especially helps if I can visualize a concept.

I’ve always thought that the best way to explain partitioning as imagining that any one table in SQL Server has a partition count of exactly one. When we actually partition the table on a range value on a column then we introduce multiple copies of that table, and that it is the meta data that is replicated for the table. And it is from this copying of the table behind the scenes that helps with archiving and query performance. This is probably not far off from how partitioning actually works.

To help visualize how a table is partitioned further I wrote detailed script to display the information on each partition in a table. The script will only work if the column the table is partitioned by is a DATETIME, but that is a fairly common data type for partitioning by.

This script is also useful for checking if we are going to run out of partition ranges any time soon: if the range is by calendar month of finance quarter, it makes sense to split the top partition now well into the future because this is one less thing to have to worry about!

Depending on the number of partitions and the amount of data in the table, this script can take a long time to run, so I advise running it on a test database first to get an idea of the duration.


DECLARE @Schema NVARCHAR(50) = <schema>
DECLARE @partitionedTableName VARCHAR(50) = <table name>
DECLARE @parts TABLE (
[object_id] INT
,[schema] SYSNAME
,NAME SYSNAME
,schemeName SYSNAME
,partitionNumber INT
,fileGroupName SYSNAME
,rangeValue DATETIME
,[rows] INT
,boundaryOnRight BIT
,totalSpace_MB INT
,UsedSpace_MB INT
,UnUsedSpace_MB INT UNIQUE CLUSTERED (
NAME
,partitionNumber
)
)
DECLARE @table_ids TABLE ([object_id] INT)
DECLARE @partitionScheme SYSNAME

SET @partitionScheme = <pscName>

INSERT INTO @table_ids
SELECT t.object_id AS [objectid]
FROM sys.TABLES t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.index_id < 2
AND ps.NAME = @partitionScheme
AND (
@partitionedTableName IS NULL
OR t.NAME LIKE @partitionedTableName
);

WITH Parts (
[object_id]
,[schema]
,NAME
,schemeName
,partitionNumber
,fileGroupName
,rangeValue
,rows
,boundaryOnRight
,totalSpace_MB
,UsedSpace_MB
,UnUsedSpace_MB
)
AS (
SELECT I.[object_id]
,S.NAME AS [schema]
,OBJECT_NAME(I.[object_id]) AS NAME
,PS.NAME AS schemeName
,DDS.destination_id AS partitionNumber
,FG.NAME AS fileGroupName
,cast(PRV.value AS INT) AS rangeValue
,P.rows
,PF.boundary_value_on_right AS boundaryOnRight
,SUM(a.total_pages) / 128 AS TotalSpaceMB
,SUM(a.used_pages) / 128 AS UsedSpaceMB
,(SUM(a.total_pages) - SUM(a.used_pages)) / 128 AS UnusedSpaceMB
FROM sys.indexes I WITH (NOLOCK)
INNER JOIN sys.objects O WITH (NOLOCK) ON O.object_id = I.object_id
INNER JOIN sys.schemas S WITH (NOLOCK) ON O.schema_id = S.schema_id
INNER JOIN sys.partition_schemes PS WITH (NOLOCK) ON PS.data_space_id = I.data_space_id
INNER JOIN sys.partition_functions PF WITH (NOLOCK) ON PF.function_id = PS.function_id
INNER JOIN sys.destination_data_spaces DDS WITH (NOLOCK) ON DDS.partition_scheme_id = PS.data_space_id
INNER JOIN sys.filegroups FG WITH (NOLOCK) ON FG.data_space_id = DDS.data_space_id
INNER JOIN sys.partitions P WITH (NOLOCK) ON P.object_id = i.object_id
AND P.partition_number = DDS.destination_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.partition_range_values PRV WITH (NOLOCK) ON PRV.boundary_id = DDS.destination_id
AND PS.function_id = PRV.function_id
WHERE i.[object_id] IN (
SELECT [object_id]
FROM @table_ids
)
GROUP BY I.[object_id]
,S.NAME
,OBJECT_NAME(I.[object_id])
,PS.NAME
,DDS.destination_id
,FG.NAME
,cast(PRV.value AS INT)
,P.rows
,PF.boundary_value_on_right
)
INSERT INTO @parts
SELECT *
FROM parts

SELECT P1.object_id
,p1.[schema]
,p1.NAME
,p1.schemeName
,p1.partitionNumber
,p1.fileGroupName
,p1.rangeValue
,p1.rows
,p1.boundaryOnRight
,CASE
WHEN P2.rangeValue IS NULL
THEN ''
ELSE CASE P1.boundaryOnRight
WHEN 0
THEN '>'
ELSE '>='
END
END + COALESCE(CAST(P2.rangeValue AS VARCHAR) + CASE
WHEN P1.rangeValue IS NULL
THEN ''
ELSE ' AND '
END, '') + CASE
WHEN P1.rangeValue IS NULL
THEN ''
ELSE CASE P1.boundaryOnRight
WHEN 0
THEN '<='
ELSE '<'
END
END + COALESCE(CAST(P1.rangeValue AS VARCHAR), '') AS [expr]
,p1.totalSpace_MB
,p1.UsedSpace_MB
,p1.UnUsedSpace_MB
FROM @Parts P1
LEFT JOIN @Parts P2 ON P1.partitionNumber = P2.partitionNumber + 1
AND P1.schemeName = P2.schemeName
AND P1.NAME = P2.NAME
ORDER BY p1.partitionNumber DESC
GO