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