Which Filegroup is that Partition Using? How Many Rows Does It Have?

Table Partitioning in SQL Server has a bit of a learning curve. It’s tricky to just figure out how much data you have and where the data is stored.

When you’re designing or managing partitioned tables, it’s useful to quickly verify:

  • Which tables are partitioned
  • The type of partition function they use (left or right)
  • Which boundary points are assigned to which filegroup
  • How many rows and pages are in each partition (and which boundary point they’re associated with)

This helps make sure that you’re designing your tables correctly, and it also helps you avoid goofs like merging the wrong boundary point and causing a bunch of data to move into another– which can be slow and painful.

All this information is available in TSQL, it’s just an ugly query, and it doesn’t come in any built-in reports or views.

So I’ve got an ugly query for you!

Query Listing Partitioned Tables with Boundary Point, Filegroup, Row Count, Partition Size, and Partition Number By Index

This query gives an overview of partitioned tables and indexes in a database. The query is also in a Gist, if you prefer.

    sc.name + N'.' + so.name as [Schema.Table],
    si.index_id as [Index ID],
    si.type_desc as [Structure],
    si.name as [Index],
    stat.row_count AS [Rows],
    stat.in_row_reserved_page_count * 8./1024./1024. as [In-Row GB],
    stat.lob_reserved_page_count * 8./1024./1024. as [LOB GB],
    p.partition_number AS [Partition #],
    pf.name as [Partition Function],
    CASE pf.boundary_value_on_right
        WHEN 1 then 'Right / Lower'
        ELSE 'Left / Upper'
    END as [Boundary Type],
    prv.value as [Boundary Point],
    fg.name as [Filegroup]
FROM sys.partition_functions AS pf
JOIN sys.partition_schemes as ps on ps.function_id=pf.function_id
JOIN sys.indexes as si on si.data_space_id=ps.data_space_id
JOIN sys.objects as so on si.object_id = so.object_id
JOIN sys.schemas as sc on so.schema_id = sc.schema_id
JOIN sys.partitions as p on 
    and si.index_id=p.index_id
LEFT JOIN sys.partition_range_values as prv on prv.function_id=pf.function_id
    and p.partition_number= 
        CASE pf.boundary_value_on_right WHEN 1
            THEN prv.boundary_id + 1
        ELSE prv.boundary_id
        /* For left-based functions, partition_number = boundary_id, 
           for right-based functions we need to add 1 */
JOIN sys.dm_db_partition_stats as stat on stat.object_id=p.object_id
    and stat.index_id=p.index_id
    and stat.index_id=p.index_id and stat.partition_id=p.partition_id
    and stat.partition_number=p.partition_number
JOIN sys.allocation_units as au on au.container_id = p.hobt_id
    and au.type_desc ='IN_ROW_DATA' 
        /* Avoiding double rows for columnstore indexes. */
        /* We can pick up LOB page count from partition_stats */
JOIN sys.filegroups as fg on fg.data_space_id = au.data_space_id
ORDER BY [Schema.Table], [Index ID], [Partition Function], [Partition #];

Column Definitions and Notes

  • Schema.Table: Schema name concatenated with table name
  • Index ID: Included for reference and ordering
  • Structure: This will decode if it’s a partitioned heap, clustered index, nonclustered index, clustered columnstore index, or nonclustered columnstore index
  • Index Name: What it sounds like
  • Rows: Number of rows in that partition
  • In-Row GB: Reserved in-row pages for that partition
  • LOB GB: Reserved LOB pages for that partition (reminder – columnstore indexes use LOB pages)
  • Partition #: This can be useful in some queries. Remember that partition numbers are reassigned when you modify your partition function (split/merge)
  • Partition Function Name: The partition function is the “algorithm” that defines the boundary points for the partitions
  • Boundary Type: Whether the boundary point is a “right” type (lower inclusive boundary) or a “left” type (upper inclusive boundary)
  • Boundary Point: The value of the boundary point that goes with that particular partition
  • Filegroup: Where the data is located (defined by the partition scheme)

If you need to know the partition scheme name, it’s easy to add that column in (sys.partition_schemes is already in the query). The partition scheme is what maps your partition function to the filegroups. In most cases, people just want to know where things currently are, so I left that out of the query.

7 Comments. Leave new

  • It’s a great script!
    I added these two columns:



    • Thanks!

      One minor thing to note about the data_compression related columns is that for columnstore indexes, it may be misleading. Columnstore indexes get two rows in sys.allocation_units: one row for in row data, one for the LOB data (which would show as compressed). I have the join set up to only report on the in-row data so the script doesn’t show duplicate row counts over two rows for columnstore indexes.

  • […] I like to use the query here to verify everything looks right. […]

  • Thank you so much to publish this work! It helped me immensely! Respect!

  • I have seperate file groups for partitioned Tables. I want to update incremental statistics only for FGDaily and not for monthly Filegroups. How can I do that ?

    • Interesting question — but it’s not directly related to this post, and I don’t have time to research this anytime soon. I recommend writing a detailed question, along with information about what you’ve tried and the research you’ve done with the documentation, and posting it to a site like Stack Overflow or the SQL Server Central forums.

  • Andre Ranieri
    May 4, 2020 11:32 am

    This is my go-to script for validation as I build the table partitions and clustered columnstore indexes for our DW project. This is a somewhat tangential question: when I initially loaded historical data to the tables the LOB space was relatively small due to compression, however, in the past couple months we’ve partition switched staging tables to the DW; these newly loaded partitions are showing as in-row GB, consumed space is about 15X what was it was for the initial load of historical tables. Does the large amount of in-row GB space indicate the new partitions are somehow delta rowgroups that haven’t been converted to CCI columnstore formatting?


Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: