A columnstore index starts fast, then reporting slowly loses its edge. Columnstore rowgroup health shows whether rows are still in open delta stores, compressed into tiny groups, or marked deleted. Read those details before scheduling a costly rebuild.
Columnstore Rowgroup Health Starts With a Count
A columnstore index stores compressed rows in rowgroups. A full compressed group can hold up to 1,048,576 rows. New rows can first enter an OPEN delta rowgroup in rowstore form. A CLOSED delta group waits for compression. A COMPRESSED group is available in columnstore form. These states tell you more than a single fragmentation percentage.
I start at the table and partition level. A recent partition with one open group is normal. An old partition with hundreds of tiny compressed groups is a different story. The same index can contain both. What part of the index does the slow report touch?
Read the Physical Stats View
The rowgroup physical stats DMV reports state, total rows, deleted rows, trim reason, and size. Join it to sys.indexes and sys.objects so the numbers have names. Filter to the target table when a database has many columnstore indexes. The query below stays in the current database and shows each rowgroup rather than hiding outliers in an average.
SELECT OBJECT_SCHEMA_NAME(rg.object_id) AS schema_name,
OBJECT_NAME(rg.object_id) AS table_name,
i.name AS index_name, rg.partition_number,
rg.row_group_id, rg.state_desc,
rg.total_rows, rg.deleted_rows,
rg.trim_reason_desc, rg.size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
JOIN sys.indexes AS i
ON i.object_id = rg.object_id AND i.index_id = rg.index_id
WHERE rg.object_id = OBJECT_ID(N'dbo.FactSales')
ORDER BY rg.partition_number, rg.row_group_id;Change the table name. For compressed groups, total_rows includes rows marked deleted. The live count is roughly total_rows minus deleted_rows. In delta groups, deleted_rows has a different meaning and is normally zero. A nonclustered columnstore also has delete-buffer details that this one column does not fully express. Keep index type in view when interpreting it.
Read Trim Reasons Before Blaming Maintenance
A small compressed rowgroup can have several causes. BULKLOAD points to a batch that did not fill a group. MEMORY_LIMITATION points to insufficient memory during compression. DICTIONARY_SIZE means a dictionary limit ended the group. REORG or AUTO_MERGE shows maintenance or background merging created it. The trim reason does not tell the whole story, but it points you toward the load path or resource limit.
Do not insist that every group reach the maximum. A small partition cannot fill a million-row group. A rowgroup trimmed by dictionary size is not fixed by merely waiting for more inserts. I inspect the partition's row total, the load batch size, and the number of concurrent writers before choosing maintenance. A rebuild that produces the same small groups has taught you something, but at an expensive tuition rate.
Summarize Columnstore Rowgroup Health by Partition
Aggregate by partition and state to find where open groups and deleted rows cluster. A ratio above ten percent deleted rows in a large compressed group is a useful review signal, not a universal rebuild command. Likewise, several compressed groups far below the maximum size in a mature partition deserve investigation. Keep the thresholds simple and adapt them to table size and query patterns.
SELECT partition_number, state_desc,
COUNT(*) AS group_count,
SUM(total_rows) AS stored_rows,
SUM(deleted_rows) AS deleted_row_count,
MIN(total_rows) AS smallest_group,
MAX(total_rows) AS largest_group
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(N'dbo.FactSales')
GROUP BY partition_number, state_desc
ORDER BY partition_number, state_desc;A high deleted-row count can waste scan work. Many open groups leave data in rowstore form. Tiny compressed groups increase metadata and reduce compression efficiency. Yet an index used only for a small daily load can be fine with one small active group. Compare these numbers with STATISTICS IO and the actual reporting query before scheduling anything.
Reorganize for Targeted Cleanup
On supported versions, ALTER INDEX REORGANIZE can compress closed groups, remove marked deleted rows from eligible compressed groups, and merge smaller groups. COMPRESS_ALL_ROW_GROUPS = ON also forces open groups into compressed form. That is useful after a load finishes and the open groups will stay idle. During constant trickle inserts, forcing a tiny open group closed every hour can create more small groups. Timing matters.
ALTER INDEX [CCI_FactSales] ON dbo.FactSales
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);Replace the index name and table. Try it on a test copy or a representative partition first. Measure the state counts again, and run the report. I prefer a targeted reorganize when one old partition has the problem. Maintenance is not a trophy for doing work. It is justified when the query reads less or the index stores data better afterward.
Reserve Rebuild for the Cases That Need It
A rebuild can recreate the index with a fresh compression layout and remove deleted rows, but it needs time, log space, memory, and a maintenance plan. Use it when reorganize leaves a persistent problem or when a deliberate order or compression change is required. On partitioned indexes, rebuild only affected partitions where supported. Check online availability and resource limits for the edition and version you run.
Also fix the cause. If bulk loads arrive in tiny batches, stage them into larger batches. If memory limitation repeatedly trims groups, inspect build memory and concurrency. If updates and deletes constantly mark rows, review the data flow. A nightly rebuild cannot make an unstable load pattern healthy for the other twenty-three hours.
Track Columnstore Rowgroup Health Before and After
Save rowgroup counts by state, the distribution of compressed sizes, deleted-row totals, maintenance commands, and reporting reads before and after. Compare the same partitions and predicates. A lower group count is not automatically a faster query, but it gives you a mechanism to test. If reads remain unchanged, inspect segment elimination, predicates, and plan shape rather than rebuilding again.
I revisit the snapshot after a major load or purge. The goal is predictable scan work for the queries people run, not perfect-looking metadata. A single open group at the active edge is normal. A mature partition full of tiny groups is a reason to ask how the rows arrived. On newer versions, a background merge task can clean up some groups without your job. Take a second snapshot after a quiet interval before launching maintenance. That small pause can save a large rebuild.
Related reading on this blog: Compression Delay for Columnstore Index and Columnstore Index and Fragmentation.
Rowgroup maintenance is not a ritual, it is a response to measured load and scan behavior.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
