Partition Details and Row Counts

You will likely find the following query useful if you work with partitioned objects.  I developed this when I first started using table partitioning in order to verify proper partition boundaries, filegroups and row counts.  Not only does this provide much more information that can be obtained by querying the underlying table with the partition function to get partition numbers, it runs much faster because only catalog views are used.

As-is, the query includes both partitioned and non-partitioned user objects in the context database but you can customize the WHERE clauses as desired.  I think this query would make a perfect source for a SSMS custom report so that it can be easily invoked from the SSMS Object explorer.  That’s on my to-do list.

Query for Partition Details Using Catalog Views

--paritioned table and index details

SELECT

      OBJECT_NAME(p.object_id) AS ObjectName,

      i.name                   AS IndexName,

      p.index_id               AS IndexID,

      ds.name                  AS PartitionScheme,   

      p.partition_number       AS PartitionNumber,

      fg.name                  AS FileGroupName,

      prv_left.value           AS LowerBoundaryValue,

      prv_right.value          AS UpperBoundaryValue,

      CASE pf.boundary_value_on_right

            WHEN 1 THEN 'RIGHT'

            ELSE 'LEFT' END    AS Range,

      p.rows AS Rows

FROM sys.partitions                  AS p

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

JOIN sys.partition_schemes           AS ps

      ON ps.data_space_id = ds.data_space_id

JOIN sys.partition_functions         AS pf

      ON pf.function_id = ps.function_id

JOIN sys.destination_data_spaces     AS dds2

      ON dds2.partition_scheme_id = ps.data_space_id 

      AND dds2.destination_id = p.partition_number

JOIN sys.filegroups                  AS fg

      ON fg.data_space_id = dds2.data_space_id

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

      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0

UNION ALL

--non-partitioned table/indexes

SELECT

      OBJECT_NAME(p.object_id)    AS ObjectName,

      i.name                      AS IndexName,

      p.index_id                  AS IndexID,

      NULL                        AS PartitionScheme,

      p.partition_number          AS PartitionNumber,

      fg.name                     AS FileGroupName,  

      NULL                        AS LowerBoundaryValue,

      NULL                        AS UpperBoundaryValue,

      NULL                        AS Boundary, 

      p.rows                      AS Rows

FROM sys.partitions     AS p

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

JOIN sys.filegroups           AS fg

      ON fg.data_space_id = i.data_space_id

WHERE

      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0

ORDER BY

      ObjectName,

      IndexID,

      PartitionNumber;

 

Here is some sample output.

ObjectName

IndexName

IndexID

PartitionScheme

PartitionNumber

FileGroupName

LowerBoundaryValue

UpperBoundaryValue

Boundary

Rows

SalesTransactions_NonPartitioned

cdx_SalesTransactions_SalesTransactionTime

1

NULL

1

PartitioningDemo_Data

NULL

NULL

NULL

6576000

SalesTransactions_NonPartitioned

idx_SalesTransactions_StoreID_ProductID

2

NULL

1

PartitioningDemo_Index

NULL

NULL

NULL

6576000

SalesTransactions_NonPartitioned

idx_SalesTransactions_CustomerID

3

NULL

1

PartitioningDemo_Index

NULL

NULL

NULL

6576000

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

1

PartitioningDemo_Data

NULL

2006-01-01 0:00:00

RIGHT

0

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

2

PartitioningDemo_Data

2006-01-01 0:00:00

2006-02-01 0:00:00

RIGHT

0

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

3

PartitioningDemo_Data

2006-02-01 0:00:00

2006-03-01 0:00:00

RIGHT

0

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

4

PartitioningDemo_Data

2006-03-01 0:00:00

2006-04-01 0:00:00

RIGHT

0

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

5

PartitioningDemo_Data

2006-04-01 0:00:00

2006-05-01 0:00:00

RIGHT

180000

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

6

PartitioningDemo_Data

2006-05-01 0:00:00

2006-06-01 0:00:00

RIGHT

186000

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

7

PartitioningDemo_Data

2006-06-01 0:00:00

2006-07-01 0:00:00

RIGHT

180000

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

8

PartitioningDemo_Data

2006-07-01 0:00:00

2006-08-01 0:00:00

RIGHT

186000

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

9

PartitioningDemo_Data

2006-08-01 0:00:00

2006-09-01 0:00:00

RIGHT

186000

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

10

PartitioningDemo_Data

2006-09-01 0:00:00

2006-10-01 0:00:00

RIGHT

180000

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

11

PartitioningDemo_Data

2006-10-01 0:00:00

2006-11-01 0:00:00

RIGHT

186000

SalesTransactions_Partitioned

cdx_SalesTransactions_SalesTransactionTime

1

PS_SalesTransactions_Data

12

PartitioningDemo_Data

2006-11-01 0:00:00

2006-12-01 0:00:00

RIGHT

180000

 

posted @ Thursday, December 11, 2008 7:22 AM

Print

Comments on this entry:

# re: Partition Details and Row Counts

Left by shan at 7/2/2012 2:19 AM
Gravatar
Just fabalous, u saved me.

# re: Partition Details and Row Counts

Left by guzmanda at 7/2/2012 6:58 AM
Gravatar
I created a custom report so that this query can be invoked from the SSMS Object Browser by right-clicking on the desired database or table node. The project is on Codeplex. See my other blog post for more details and the download link:
weblogs.sqlteam.com/...
Comments have been closed on this topic.