2 Ways to Get Partition Information For a Table in SQL Server (T-SQL)

Here are a couple of ways to return partition info for a table in SQL Server.

  • You can use the sys.partitions system catalog view to return partition info for a table and most kinds of views.
  • You can use the sys.dm_db_partition_stats system dynamic management view to return page and row-count information for every partition in the current database.

If a table or index hasn’t been partitioned, these views will still return partition info (with a partition_number of 1). This is because all tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

sys.partitions

The sys.partitions view contains a row for each partition of all the tables and most types of indexes in the database. Special index types such as Full-Text, Spatial, and XML are not included.

Here’s an example to demonstrate.

SELECT *
FROM sys.partitions
WHERE object_id = OBJECT_ID('Movies');

Result:

+-------------------+-------------+------------+--------------------+-------------------+--------+---------------------------+--------------------+-------------------------+
| partition_id      | object_id   | index_id   | partition_number   | hobt_id           | rows   | filestream_filegroup_id   | data_compression   | data_compression_desc   |
|-------------------+-------------+------------+--------------------+-------------------+--------+---------------------------+--------------------+-------------------------|
| 72057594049986560 | 91147370    | 1          | 1                  | 72057594049986560 | 0      | 0                         | 0                  | NONE                    |
| 72057594050052096 | 91147370    | 1          | 2                  | 72057594050052096 | 100    | 0                         | 0                  | NONE                    |
| 72057594050117632 | 91147370    | 1          | 3                  | 72057594050117632 | 3979   | 0                         | 0                  | NONE                    |
| 72057594050183168 | 91147370    | 1          | 4                  | 72057594050183168 | 0      | 0                         | 0                  | NONE                    |
+-------------------+-------------+------------+--------------------+-------------------+--------+---------------------------+--------------------+-------------------------+

We can see that there are four partitions for this table.

Here it is again, this time using vertical output (to save you from having to scroll sideways):

-[ RECORD 1 ]-------------------------
partition_id            | 72057594049986560
object_id               | 91147370
index_id                | 1
partition_number        | 1
hobt_id                 | 72057594049986560
rows                    | 0
filestream_filegroup_id | 0
data_compression        | 0
data_compression_desc   | NONE
-[ RECORD 2 ]-------------------------
partition_id            | 72057594050052096
object_id               | 91147370
index_id                | 1
partition_number        | 2
hobt_id                 | 72057594050052096
rows                    | 100
filestream_filegroup_id | 0
data_compression        | 0
data_compression_desc   | NONE
-[ RECORD 3 ]-------------------------
partition_id            | 72057594050117632
object_id               | 91147370
index_id                | 1
partition_number        | 3
hobt_id                 | 72057594050117632
rows                    | 3979
filestream_filegroup_id | 0
data_compression        | 0
data_compression_desc   | NONE
-[ RECORD 4 ]-------------------------
partition_id            | 72057594050183168
object_id               | 91147370
index_id                | 1
partition_number        | 4
hobt_id                 | 72057594050183168
rows                    | 0
filestream_filegroup_id | 0
data_compression        | 0
data_compression_desc   | NONE

sys.dm_db_partition_stats

The sys.dm_db_partition_stats view returns page and row-count information for every partition in the current database. But if you’re only interested in one table, you can also narrow it down to just that table.

Here’s an example.

SELECT *
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Movies');

Result:

+-------------------+-------------+------------+--------------------+--------------------------+--------------------------+------------------------------+-----------------------+---------------------------+--------------------------------+------------------------------------+-------------------+-----------------------+-------------+
| partition_id      | object_id   | index_id   | partition_number   | in_row_data_page_count   | in_row_used_page_count   | in_row_reserved_page_count   | lob_used_page_count   | lob_reserved_page_count   | row_overflow_used_page_count   | row_overflow_reserved_page_count   | used_page_count   | reserved_page_count   | row_count   |
|-------------------+-------------+------------+--------------------+--------------------------+--------------------------+------------------------------+-----------------------+---------------------------+--------------------------------+------------------------------------+-------------------+-----------------------+-------------|
| 72057594049986560 | 91147370    | 1          | 1                  | 0                        | 0                        | 0                            | 0                     | 0                         | 0                              | 0                                  | 0                 | 0                     | 0           |
| 72057594050052096 | 91147370    | 1          | 2                  | 1                        | 2                        | 9                            | 0                     | 0                         | 0                              | 0                                  | 2                 | 9                     | 100         |
| 72057594050117632 | 91147370    | 1          | 3                  | 13                       | 15                       | 33                           | 0                     | 0                         | 0                              | 0                                  | 15                | 33                    | 3979        |
| 72057594050183168 | 91147370    | 1          | 4                  | 0                        | 0                        | 0                            | 0                     | 0                         | 0                              | 0                                  | 0                 | 0                     | 0           |
+-------------------+-------------+------------+--------------------+--------------------------+--------------------------+------------------------------+-----------------------+---------------------------+--------------------------------+------------------------------------+-------------------+-----------------------+-------------+

Here it is again using vertical output:

-[ RECORD 1 ]-------------------------
partition_id                     | 72057594049986560
object_id                        | 91147370
index_id                         | 1
partition_number                 | 1
in_row_data_page_count           | 0
in_row_used_page_count           | 0
in_row_reserved_page_count       | 0
lob_used_page_count              | 0
lob_reserved_page_count          | 0
row_overflow_used_page_count     | 0
row_overflow_reserved_page_count | 0
used_page_count                  | 0
reserved_page_count              | 0
row_count                        | 0
-[ RECORD 2 ]-------------------------
partition_id                     | 72057594050052096
object_id                        | 91147370
index_id                         | 1
partition_number                 | 2
in_row_data_page_count           | 1
in_row_used_page_count           | 2
in_row_reserved_page_count       | 9
lob_used_page_count              | 0
lob_reserved_page_count          | 0
row_overflow_used_page_count     | 0
row_overflow_reserved_page_count | 0
used_page_count                  | 2
reserved_page_count              | 9
row_count                        | 100
-[ RECORD 3 ]-------------------------
partition_id                     | 72057594050117632
object_id                        | 91147370
index_id                         | 1
partition_number                 | 3
in_row_data_page_count           | 13
in_row_used_page_count           | 15
in_row_reserved_page_count       | 33
lob_used_page_count              | 0
lob_reserved_page_count          | 0
row_overflow_used_page_count     | 0
row_overflow_reserved_page_count | 0
used_page_count                  | 15
reserved_page_count              | 33
row_count                        | 3979
-[ RECORD 4 ]-------------------------
partition_id                     | 72057594050183168
object_id                        | 91147370
index_id                         | 1
partition_number                 | 4
in_row_data_page_count           | 0
in_row_used_page_count           | 0
in_row_reserved_page_count       | 0
lob_used_page_count              | 0
lob_reserved_page_count          | 0
row_overflow_used_page_count     | 0
row_overflow_reserved_page_count | 0
used_page_count                  | 0
reserved_page_count              | 0
row_count                        | 0

This sample table has been partitioned using the Microsoft recommended method of keeping empty partitions at both ends of the partition range. This ensures that any future partition splits/merges don’t cause any unexpected data movement.