locked
Querying table partition RRS feed

  • 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 AM
    Answerer