🗄️ Fix a Columnstore Index Fragmenting After Bulk Deletes
A columnstore index stores data in large, immutable compressed row groups; a `DELETE` against rows in one doesn’t physically remove them from their row group — it marks them as logically deleted in an internal bitmap while the physical data stays resident on disk and in memory during every scan. A bulk delete that removes a large percentage of rows from many row groups leaves those row groups mostly “deleted” but still fully present, and because the background tuple mover only compacts row groups opportunistically rather than aggressively reorganizing ones fragmented by deletes, query performance can degrade substantially even though the table’s apparent size hasn’t changed.
🔎 The Problem
SELECT object_id, row_group_id, total_rows, deleted_rows,
CAST(deleted_rows AS float) / total_rows AS deleted_ratio
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('FactSales')
ORDER BY deleted_ratio DESC;
-- row groups showing 60-90% deleted_ratio are still scanned in full on
-- every query against this table until explicitly reorganized
✅ Fix: Reorganize After the Delete, Not Instead of It
- After any bulk delete that removes a significant percentage of a columnstore table’s rows, check `sys.dm_db_column_store_row_group_physical_stats` for a high deleted-to-total row ratio instead of assuming the automatic tuple mover already handled it.
- Run `ALTER INDEX … REORGANIZE` with `COMPRESS_ALL_ROW_GROUPS = ON` (or a full rebuild for heavily fragmented cases) as an explicit maintenance step after large deletes, rather than relying on the tuple mover, which is designed for ongoing light fragmentation, not a one-time bulk cleanup.
- For a recurring bulk-delete workload like a nightly purge job, schedule the reorganize/rebuild as a follow-up step in the SAME maintenance window instead of a separate, easy-to-forget job, since the regression compounds with every delete between rebuilds.
🚨 Why This Is Dangerous
- A columnstore table that silently keeps scanning through millions of logically-deleted-but-physically-present rows can turn a sub-second analytical query into a multi-second one with no error, no warning, and no obvious link back to a bulk delete that might have run weeks earlier.
A columnstore DELETE removes a row from your results today and from your query’s workload only after someone tells the index to catch up.
