View partitions in SQL Server

2012/09/29 — Leave a comment

This query is quite useful when looking at which partitions are in the database. It is originally taken from the book Microsoft SQL Server 2008 Internals (p. 438), and then I have tweaked it a bit and added some more information.

I used this query in my demo today at SQLSaturday #152 in Bulgaria, where I talked about layered partitioning.

DECLARE @TableName NVARCHAR(200) = N'Facts.Credit'

SELECT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(i.object_id) AS [object]
     , p.partition_number AS [p#]
     , AS [filegroup]
     , p.rows
     , au.total_pages AS pages
     , CASE boundary_value_on_right
       WHEN 1 THEN 'less than'
       ELSE 'less than or equal to' END as comparison
     , rv.value
     , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
       SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20),
       CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
       SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
       SUBSTRING (au.first_page, 1, 1))) AS first_page
FROM sys.partitions p
INNER JOIN sys.indexes i
     ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
     ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au
     ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
     ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f
     ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
     ON dds.partition_scheme_id = ps.data_space_id
     AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
     ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv
     ON f.function_id = rv.function_id
     AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
     AND o.object_id = OBJECT_ID(@TableName);

The result is something like the following.

View partitions in SQL Server

The query shows:

  • object – The table name.
  • p# – The partition number.
  • filegroup – The filegroup the partition is located on. Note that in this result, I am using the same filegroup for all partitions, but usually you would probably not do this.
  • rows – Number of rows in the partition.
  • comparison – shows “less than” if you are using right range partitioning or “less than or equal to” if you are using left range partitioning.
  • value – The boundary point between the two partitions. This value is either in the right or left partition of the boundary point, depending on whether or not you are using right range or left range partitioning.
  • first_page – The first file:page allocated for the partition.

David Peter Hansen

Posts Twitter

David Peter Hansen is a Microsoft Certified Master of SQL Server and works as Senior Manager at Hitachi Consulting in London. He has worked with database development and administration for 12 years, and has worked with the Microsoft Business Intelligence platform since SQL Server 2000. He specializes in developer coaching as well as scalable architecture and performance tuning on large-scale data warehouses and BI solutions. He is a frequent speaker at SQL Server and BI conferences.

No Comments

Be the first to start the conversation.

Leave a Reply