The short answer
A Clustered Columnstore Index achieves optimal query performance when every compressed rowgroup contains roughly 1 million rows (the maximum is 1,048,576 rows). When rowgroups are full, segment elimination skips massive volumes of data.
However, loading data in small, frequent micro-batches damages columnstore quality.
How rowgroups degrade
When you insert fewer than 102,400 rows in an ingestion batch, Synapse cannot compress them directly into a columnar rowgroup. Instead, it writes them into a temporary, uncompressed B-tree structure called a delta store.
Queries must scan both the compressed columnstore and the uncompressed delta store, slowing down read throughput. In addition, if memory pressure occurs during ingestion, Synapse closes rowgroups prematurely, creating trimmed rowgroups holding only 50,000 or 100,000 rows.
Inspecting columnstore health
Query sys.dm_pdw_nodes_db_column_store_row_group_physical_stats to inspect rowgroup states across the 60 distributions:
SELECT
state_description,
COUNT(*) as rowgroup_count,
SUM(total_rows) as total_rows
FROM sys.dm_pdw_nodes_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.fact_orders')
GROUP BY state_description;A healthy table shows rowgroups in the COMPRESSED state with total_rows near 1 million. An unhealthy table shows hundreds of rowgroups in the OPEN or TOMBSTONE states.
Maintenance: Rebuilding the index
To merge fragmented rowgroups and force delta stores into compressed , execute an index rebuild:
ALTER INDEX ALL ON dbo.fact_orders REBUILD;Run index rebuilds periodically after massive bulk loads or continuous batches to maintain maximum query scan speeds.