Counting Partitions per Table and Spotting Empty Ones

To count partitions per table, count only the heap or clustered index rows in sys.partitions. Every other index has its own partition rows, so counting them all inflates the answer. Keep the empty partitions in the list too, because they often have a job to do.

The report that said 8

Imagine a capacity review. Someone runs a quick count against sys.partitions and announces that one table has 8 partitions. Everyone nods. Then the DBA who built the table says, “It has four.”

Both people are right about their number. Let me build the table and show where the extra four came from. It uses monthly boundaries with RANGE RIGHT, all stored on PRIMARY. I add a nonclustered index and two rows, one in January and one in March. A second plain table gives us something to compare.

DROP TABLE IF EXISTS dbo.Events;
DROP TABLE IF EXISTS dbo.PlainTable;
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = N'psMonthly')
    DROP PARTITION SCHEME psMonthly;
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = N'pfMonthly')
    DROP PARTITION FUNCTION pfMonthly;
GO
CREATE PARTITION FUNCTION pfMonthly (date)
    AS RANGE RIGHT FOR VALUES ('20260101', '20260201', '20260301');
CREATE PARTITION SCHEME psMonthly AS PARTITION pfMonthly ALL TO ([PRIMARY]);

CREATE TABLE dbo.Events (EventDate date NOT NULL, ItemId int NOT NULL)
    ON psMonthly (EventDate);
CREATE NONCLUSTERED INDEX IX_Events_ItemId ON dbo.Events (ItemId);
CREATE TABLE dbo.PlainTable (ItemId int NOT NULL);

INSERT dbo.Events VALUES ('20260115', 1), ('20260315', 2);
INSERT dbo.PlainTable VALUES (1);

The demo creates a partition function and scheme in the current database, and the last block removes them. The first lines drop objects with these names if they already exist, so use a test database.

Count the base storage once

The sys.partitions view has one row for each partition of each index. Index 0 is a heap, 1 is the clustered index, and anything higher is a nonclustered index. Compare the careless count with the careful one.

SELECT COUNT(*) AS all_partition_rows
FROM sys.partitions
WHERE object_id = OBJECT_ID(N'dbo.Events');

SELECT COUNT(*) AS partition_count, SUM(rows) AS metadata_rows
FROM sys.partitions
WHERE object_id = OBJECT_ID(N'dbo.Events')
  AND index_id IN (0, 1);

The first query says 8: four for the heap and four for the nonclustered index. The second says 4 partitions and 2 rows. That is the answer you want.

Keep the empty ones, and read the boundaries

Now list every partition with the boundary values on each side. The two LEFT JOINs turn the two outer edges into NULL, since nothing lies beyond them.

DECLARE @FunctionId int =
    (SELECT function_id FROM sys.partition_functions WHERE name = N'pfMonthly');

SELECT p.partition_number, p.rows AS metadata_rows,
       lo.value AS preceding_boundary, hi.value AS following_boundary
FROM sys.partitions AS p
LEFT JOIN sys.partition_range_values AS lo
       ON lo.function_id = @FunctionId AND lo.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS hi
       ON hi.function_id = @FunctionId AND hi.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID(N'dbo.Events') AND p.index_id IN (0, 1)
ORDER BY p.partition_number;

You see four rows. Partitions 2 and 4 hold one row each. Partitions 1 and 3 hold none. With RANGE RIGHT, a boundary value belongs to the partition on its right, so January 15 sits in partition 2, between January 1 and February 1.

Check the numbers against the data

The row count in sys.partitions is a handy inventory value, but it is metadata. For an exact answer, ask the table. The $PARTITION function tells you where each row lives.

SELECT $PARTITION.pfMonthly(EventDate) AS partition_number, COUNT_BIG(*) AS exact_rows
FROM dbo.Events
GROUP BY $PARTITION.pfMonthly(EventDate)
ORDER BY partition_number;

Only partitions 2 and 4 appear, with one row each. A grouped query can never show an empty partition, because there are no rows to group. That is why you need both queries: the metadata one shows what exists, and this one shows what is populated.

One query for the whole database

To review every table at once, group by table and count the empty partitions too.

SELECT s.name AS schema_name, t.name AS table_name,
       COUNT(*) AS partitions, SUM(p.rows) AS metadata_rows,
       SUM(CASE WHEN p.rows = 0 THEN 1 ELSE 0 END) AS empty_partitions
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
GROUP BY s.name, t.name
ORDER BY partitions DESC, table_name;

dbo.Events shows 4 partitions, 2 rows and 2 empty ones. dbo.PlainTable shows 1 partition, 1 row and none empty. Every table has at least one partition.

Before you merge an empty partition, ask what it is for. An empty edge can be the landing spot for the next month’s load. And a partition function can serve several tables, so check them all first.

DROP TABLE IF EXISTS dbo.Events;
DROP TABLE IF EXISTS dbo.PlainTable;
DROP PARTITION SCHEME psMonthly;
DROP PARTITION FUNCTION pfMonthly;

Next time a partition count looks too big, check which index rows you counted.

An empty partition is not a useless partition, it is a boundary that needs a purpose.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.