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.

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)

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.