Diagnose SQL Server Columnstore Performance by Row Group

A columnstore index can accelerate a large aggregation while disappointing on another table with the same row count. The difference is often in the physical groups of rows, the columns being read, and how well predicates eliminate work. Looking only at whether a columnstore index exists misses those details.

Columnstore is particularly useful for scanning selected columns across many rows and aggregating them. It is not an automatic replacement for a narrow rowstore index serving selective lookups. Begin with the query shape and the write workload, then examine whether the actual columnstore layout supports that shape.

Read the row group evidence

A compressed row group can contain up to 1,048,576 rows. New rows may first enter rowstore delta groups, which later become eligible for compression. The following read-only query exposes the relevant state for an existing fact table.

-- Replace dbo.FactSales with the table being investigated.
SELECT i.name AS IndexName, rg.partition_number, rg.row_group_id,
       rg.state_desc, rg.total_rows, rg.deleted_rows,
       CAST(100.0 * rg.deleted_rows / NULLIF(rg.total_rows, 0)
            AS decimal(6,2)) AS DeletedPercent,
       rg.size_in_bytes, rg.trim_reason_desc
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.index_id, rg.partition_number, rg.row_group_id;

OPEN and CLOSED groups indicate delta-store stages; COMPRESSED groups hold columnar data. Multiple open groups are not automatically a defect, especially across partitions or concurrent loading streams. Focus on patterns over time rather than one isolated snapshot.

For compressed groups, compare total rows, deleted rows, and trim reasons. A group with many logically deleted rows can require work for entries that no longer contribute to results. A small group may reflect a memory limit, dictionary pressure, or a deliberately small input rather than random fragmentation. A NULL percentage for an empty group is preferable to inventing a zero denominator.

The view requires diagnostic permissions appropriate to the engine version, such as VIEW DATABASE STATE on older versions and VIEW DATABASE PERFORMANCE STATE on SQL Server 2022 and later. Use an authorized diagnostic connection, not a broad permission increase for the application.

Connect physical layout to query work

Consider a monthly category aggregation.

-- Example report shape for an existing fact table.
SELECT ProductCategoryId, SUM(Revenue) AS Revenue
FROM dbo.FactSales
WHERE SaleDate >= '20260101' AND SaleDate < '20260201'
GROUP BY ProductCategoryId;

Columnstore can avoid reading unused columns and may skip segments whose value bounds cannot satisfy the date predicate. This segment elimination is different from a B-tree seek. If every row group contains dates spanning the entire history, a narrow month filter may still touch many groups.

Inspect the actual execution plan and available segment-read diagnostics. Compare the rows read with rows returned, aggregation behavior, and any spills. Compression ratio alone does not tell you whether the query avoided work, and batch-mode execution alone does not prove that the layout is effective.

Data arrangement and load patterns influence segment bounds, but a sorted staging query is not by itself a guarantee of a particular final physical layout. Verify the resulting groups and the report's reads. Also check whether joins introduce large intermediate results before the aggregate; the fact table index cannot repair a many-to-many join mistake.

If users mostly retrieve a few individual sales, consider a suitable supporting rowstore access path or a different design. Benchmark the real mix, including inserts and updates, instead of extrapolating from a single warehouse-style SUM query.

Improve the cause before rebuilding everything

Bulk loading can send sufficiently large batches directly to compressed groups; 102,400 rows is an important threshold in the documented bulk-load behavior. The effective batch reaching each partition matters. Dividing one large file among many small partitions or loading streams can produce far smaller groups than the file's total row count suggests.

Frequent tiny loads and repeated updates may increase delta-store and deleted-row work. Consider batching ingestion where freshness requirements allow it. Conversely, forcing every tiny open group to compress immediately can create persistently small compressed groups. Maintenance should serve a measured objective rather than a rule that all groups must always be compressed.

Choose reorganization or rebuild only after identifying the affected index and partitions, expected benefit, and resource cost. Rebuilding can consume CPU, memory, log, and temporary space while competing with reports. Version-specific background merge behavior may already address some conditions over time.

Record before-and-after group counts, effective rows, report reads, duration, and ingestion throughput. Use the same representative reporting window. A useful change reduces the work needed for real queries without making the load pipeline unable to meet its own deadlines.

Technical references: Microsoft Learn: Columnstore overview · Microsoft Learn: Columnstore query performance · Microsoft Learn: Row group physical statistics.