Deleting data from a compressed rowgroup leaves work behind for later scans. Columnstore deleted rows remain inside compressed rowgroups until maintenance removes them. The delete bitmap keeps query results correct, but the stored rows still shape the work the scan must perform.
Understand What a Delete Leaves Behind
Compressed columnstore data is stored in column segments grouped into rowgroups. Deleting a row does not immediately unpack and rewrite every compressed segment. SQL Server marks the row as deleted and uses that information when returning results. The old contents remain physically represented until later cleanup.
An UPDATE also creates this pattern. The engine logically deletes the old row and inserts its replacement. A table receiving repeated corrections can accumulate deleted entries even when its live row count stays steady. Counting the business rows alone misses that churn.
I look at the modification pattern before recommending maintenance. A mostly append-only table and a frequently corrected table need different expectations. If the same process repeatedly changes yesterday's records, a one-time cleanup cannot prevent tomorrow's bitmap growth. The bitmap is doing its job. It simply does not make old compressed data disappear on command.
Count columnstore deleted rows by rowgroup before deciding which partitions need maintenance.
Measure Columnstore Deleted Rows by Rowgroup
Run this query in the database containing the table. Replace the example name with an existing columnstore table. The DMV reports physical rowgroup information, including state, total rows, deleted rows, and partition. Keep these dimensions together instead of reducing everything to one database-wide percentage.
The denominator is protected with NULLIF because empty states should not cause a divide-by-zero error. Compressed rowgroups are the main focus for delete-bitmap analysis. Delta rowgroups and transitional states describe different parts of the loading lifecycle.
Look for concentrations of deleted rows in the partitions scanned by the slow query. A large deleted share in an unrelated historical partition does not explain a query reading recent data. I keep the per-rowgroup output because averages hide the groups that deserve attention. Check permissions if the DMV returns an access error rather than treating a failed inspection as evidence of healthy storage.
SELECT partition_number, row_group_id, state_desc,
total_rows, deleted_rows,
CONVERT(decimal(9,2), 100.0 * deleted_rows / NULLIF(total_rows, 0)) AS DeletedPct
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(N'dbo.ColumnstoreSales')
ORDER BY partition_number, row_group_id;Connect the Bitmap to a Real Scan
The next query is a pattern for an existing sales table with SaleDate and Amount columns. Use a representative date range and enable the actual execution plan in SSMS. SET STATISTICS IO reports the work performed by that execution, without requiring you to guess at a performance result.
Capture the output before maintenance and keep the exact statement and parameters. Check which rowgroups the scan reads or eliminates. Also inspect delta-store access, predicates, and the returned aggregate. A slow scan has more possible causes than deleted entries, including broad date ranges and weak rowgroup elimination.
Ask yourself which part of the query should improve after physical cleanup. If it still needs every rowgroup, removing deleted data reduces waste but does not turn the query into a selective lookup. Compare the same business calculation under similar conditions. A different filter is a different test, even if its output happens to look familiar.
SET STATISTICS IO ON;
SELECT SUM(Amount) AS SalesAmount
FROM dbo.ColumnstoreSales
WHERE SaleDate >= '20260101' AND SaleDate < '20260201';
SET STATISTICS IO OFF;
Use REORGANIZE With Realistic Expectations
On SQL Server 2016 and later, columnstore REORGANIZE performs more useful work than merely closing delta rowgroups. It removes logically deleted rows from qualifying compressed rowgroups and merges suitable groups. The documented deleted-row threshold for this cleanup is ten percent within a rowgroup.
The following statement requests that work for the named index. COMPRESS_ALL_ROW_GROUPS also compresses open and closed delta rowgroups. That option is useful after a load, but forcing many small groups to compress deserves review. Compression quality depends on the data and rowgroup population.
Run the physical-stats query again after the operation. Compare rowgroup identifiers, states, deleted shares, and total rows. Groups can merge or be replaced, so do not require a one-to-one identifier match. Confirm that eligible waste was removed rather than promising that every deleted counter everywhere becomes zero. Background cleanup and concurrent writes also affect the result you capture.
ALTER INDEX CCI_ColumnstoreSales ON dbo.ColumnstoreSales
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);Reserve REBUILD for the Larger Decision
REBUILD recreates the index or a chosen partition. It is heavier than targeted REORGANIZE and requires planning for CPU, memory, transaction log, storage, and blocking behavior. Available online options depend on the operation and platform. Check those details before scheduling it on a busy table.
A rebuild makes sense when the problem extends beyond a qualifying deleted share, or when a tested rebuild provides benefits the lighter operation cannot deliver. It is not a default response to every nonzero bitmap. Columnstore maintenance should serve the workload, not a dashboard that insists every counter look empty.
For partitioned tables, inspect where the churn occurs. Work on an affected partition when that meets the requirement. A retention process that deletes whole old date ranges also deserves a partition design review. Switching or removing an expired partition avoids repeatedly marking large historical ranges row by row.
Fix the Pattern That Recreates Columnstore Deleted Rows
Review the update process next. Does it rewrite rows whose values already match? Can corrections be collected into a controlled batch? Can immutable history stay separate from actively changing rows? These choices address the source of columnstore deleted rows instead of repeatedly treating the symptom.
After maintenance, rerun the captured scan and read its actual IO and plan. Check correctness first, then compare the work. Keep the measured output from your own server beside the rowgroup evidence. Do not substitute the maintenance command's successful completion for query validation.
Schedule follow-up inspection around the actual change pattern. A monthly report table and a continuously updated operational table accumulate waste differently. Maintain the rowgroups that need attention, watch the process that creates them, and keep the reader's query as the final judge of whether the work was worthwhile.
Related reading on this blog: Columnstore Rowgroup Health: Finding Small and Open Rowgroups and Columnstore Index and Fragmentation.
A deleted columnstore row is not immediately freed space, it is a marked entry awaiting cleanup.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
