Answered by:
Querying table partition

Question
-
Hi
I am writing a query to check that all of our table partitions are set up correctly.
I am pulling the scheme and function which is being used on a table and the range which will cause the table to split into the different filegroups.
But I haven't found a system view which will give a list of filegroups which are linked to the scheme
My query currently looks like this
Select ps.Name PartitionScheme, pf.name PartitionFunction, sv.value SplitValue
from sys.indexes I
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
join sys.partition_range_values sv on pf.function_id = sv.function_id
where i.object_id = object_id('tableName')
Thanks for any help
Monday, August 1, 2016 10:16 AM
Answers
-
The query below will show the partitions, boundaries, filegroups and space information for the table and each index.
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(p.object_id)) AS ObjectName , CASE WHEN i.name IS NULL THEN '(heap)' ELSE i.name END AS IndexName , i.index_id AS IndexID , CASE WHEN pf.name IS NULL THEN '' ELSE pf.name END AS PartitionFunctionName , CASE pf.boundary_value_on_right WHEN 0 THEN 'LEFT' WHEN 1 THEN 'RIGHT' ELSE '' END AS PartitionFunctionRange , (SELECT name FROM sys.columns AS c WHERE c.object_id = ic.object_id AND c.column_id = ic.column_id) AS PartitioningColumn , CASE WHEN pf.name IS NULL THEN '' ELSE (SELECT CAST(ic.column_id AS varchar(11)) --partitioning column + pf.type --partition function range specification (LEFT or RIGHT) + ( SELECT prv.value AS v FROM sys.partition_range_values AS prv WHERE ps.function_id = prv.function_id ORDER BY v FOR XML PATH('') ) --non-null partition boundaries + ( SELECT 'N' FROM sys.partition_range_values AS prv WHERE ps.function_id = prv.function_id AND prv.value IS NULL ) --NULL partition boundary, if present AS PartitionFunctionDefinition FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal = 1 INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id INNER JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id WHERE i.object_id = p.object_id AND i.index_id = p.index_id ) END AS PartitionFunctionDefinition , CASE WHEN pf.name IS NOT NULL THEN ds.name ELSE NULL END AS PartitionSchemeName , p.partition_number AS PartitionNumber , COUNT(*) OVER(PARTITION BY p.object_id, p.index_id) AS PartitionCount , fg.name AS FileGroupName , prv_left.value AS LowerBoundaryValue , prv_right.value AS UpperBoundaryValue , SUM(CASE WHEN p.index_id IN(0,1) THEN p.rows ELSE 0 END) OVER(PARTITION BY p.object_id, p.index_id) AS TableRowCount , p.rows AS Rows , pst.used_page_count AS UsedPages , pst.used_page_count * 8 AS UsedSpaceKB , pst.reserved_page_count as ReservedPages , pst.reserved_page_count * 8 as ReservedSpaceKB FROM sys.partitions AS p JOIN sys.dm_db_partition_stats AS pst ON pst.partition_id = p.partition_id AND pst.object_id = p.object_id AND pst.index_id = p.index_id JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id LEFT JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id LEFT JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id LEFT JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number LEFT JOIN sys.filegroups AS fg ON fg.data_space_id = COALESCE(dds2.data_space_id, i.data_space_id) LEFT JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal = 1 LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number WHERE p.object_id = OBJECT_ID(N'YourSchema.YourTable') ORDER BY ObjectName, IndexID, PartitionNumber;
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
- Marked as answer by GD_SNC Monday, August 1, 2016 2:05 PM
Monday, August 1, 2016 11:11 AM
All replies
-
The query below will show the partitions, boundaries, filegroups and space information for the table and each index.
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(p.object_id)) AS ObjectName , CASE WHEN i.name IS NULL THEN '(heap)' ELSE i.name END AS IndexName , i.index_id AS IndexID , CASE WHEN pf.name IS NULL THEN '' ELSE pf.name END AS PartitionFunctionName , CASE pf.boundary_value_on_right WHEN 0 THEN 'LEFT' WHEN 1 THEN 'RIGHT' ELSE '' END AS PartitionFunctionRange , (SELECT name FROM sys.columns AS c WHERE c.object_id = ic.object_id AND c.column_id = ic.column_id) AS PartitioningColumn , CASE WHEN pf.name IS NULL THEN '' ELSE (SELECT CAST(ic.column_id AS varchar(11)) --partitioning column + pf.type --partition function range specification (LEFT or RIGHT) + ( SELECT prv.value AS v FROM sys.partition_range_values AS prv WHERE ps.function_id = prv.function_id ORDER BY v FOR XML PATH('') ) --non-null partition boundaries + ( SELECT 'N' FROM sys.partition_range_values AS prv WHERE ps.function_id = prv.function_id AND prv.value IS NULL ) --NULL partition boundary, if present AS PartitionFunctionDefinition FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal = 1 INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id INNER JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id WHERE i.object_id = p.object_id AND i.index_id = p.index_id ) END AS PartitionFunctionDefinition , CASE WHEN pf.name IS NOT NULL THEN ds.name ELSE NULL END AS PartitionSchemeName , p.partition_number AS PartitionNumber , COUNT(*) OVER(PARTITION BY p.object_id, p.index_id) AS PartitionCount , fg.name AS FileGroupName , prv_left.value AS LowerBoundaryValue , prv_right.value AS UpperBoundaryValue , SUM(CASE WHEN p.index_id IN(0,1) THEN p.rows ELSE 0 END) OVER(PARTITION BY p.object_id, p.index_id) AS TableRowCount , p.rows AS Rows , pst.used_page_count AS UsedPages , pst.used_page_count * 8 AS UsedSpaceKB , pst.reserved_page_count as ReservedPages , pst.reserved_page_count * 8 as ReservedSpaceKB FROM sys.partitions AS p JOIN sys.dm_db_partition_stats AS pst ON pst.partition_id = p.partition_id AND pst.object_id = p.object_id AND pst.index_id = p.index_id JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id LEFT JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id LEFT JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id LEFT JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number LEFT JOIN sys.filegroups AS fg ON fg.data_space_id = COALESCE(dds2.data_space_id, i.data_space_id) LEFT JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal = 1 LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number WHERE p.object_id = OBJECT_ID(N'YourSchema.YourTable') ORDER BY ObjectName, IndexID, PartitionNumber;
Dan Guzman, Data Platform MVP, http://www.dbdelta.com
- Marked as answer by GD_SNC Monday, August 1, 2016 2:05 PM
Monday, August 1, 2016 11:11 AM -
Scriot written by Itzik Ben-Gan
- Definition of UDF fn_script_partition_scheme
IF OBJECT_ID('dbo.fn_script_partition_scheme', 'FN') IS NOT NULL
DROP FUNCTION dbo.fn_script_partition_scheme;
GO
CREATE FUNCTION dbo.fn_script_partition_scheme(@ps AS SYSNAME)
RETURNS NVARCHAR(4000)
AS
BEGIN
RETURN
N'CREATE PARTITION SCHEME ' + QUOTENAME(@ps) + N' AS PARTITION '
+
-- Return partition function name
-- based on input partition scheme name
(SELECT QUOTENAME(PF.name)
FROM sys.partition_schemes AS PS
JOIN sys.partition_functions AS PF
ON PS.function_id = PF.function_id
where PS.name = @ps)
+
N'
TO ('
+
-- Return concatenated list of filegroup names
-- that participate in partition scheme
STUFF(
(SELECT N',' + QUOTENAME(DS.name) AS [text()]
FROM sys.partition_schemes AS PS
JOIN sys.destination_data_spaces AS DDS
ON PS.data_space_id = DDS.partition_scheme_id
JOIN sys.data_spaces AS DS
ON DDS.data_space_id = DS.data_space_id
WHERE PS.name = @ps
ORDER BY DDS.destination_id
FOR XML PATH(')), 1, 1, N')
+
N');'; PRINT dbo.fn_script_partition_scheme(N'PS1');
PRINT dbo.fn_script_partition_scheme(N'PS2');
And you should get the following output:
CREATE PARTITION SCHEME [PS1] AS PARTITION [PF1]
TO ([FG1],[FG2],[FG3],[FG4]);
CREATE PARTITION SCHEME [PS2] AS PARTITION [PF2]
TO ([FG5],[FG6],[FG7]);Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Monday, August 1, 2016 11:55 AMAnswerer