🗄️ SQL Server: Fix a Columnstore Archive Compression Job Stalling
ALTER INDEX ... REORGANIZE WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) compresses closed row groups further for cold data, but it can only fully archive-compress a row group once it’s already in a COMPRESSED state. If background tuple-mover activity keeps merging row groups concurrently — common on a table still receiving occasional writes — the archive job spends most of its time waiting for row groups to settle, making it look like it’s stalled on a table that isn’t actually idle.
🔎 The Problem
ALTER INDEX CCI_Orders ON dbo.Orders
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON, DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
-- running for 40+ minutes on a table with only ~2M rows
SELECT state_desc, COUNT(*)
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('dbo.Orders')
GROUP BY state_desc;
-- OPEN: 6, CLOSED: 14, COMPRESSED: 3 <- most row groups aren't settled yet
✅ Fix: Let Row Groups Settle Before Archiving Them
- Run archive compression during a maintenance window where the table has genuinely stopped receiving writes, or pause the background tuple-mover’s competing activity, so REORGANIZE isn’t racing other row-group-state changes.
- Check
sys.dm_db_column_store_row_group_physical_statsfor row groups stuck in OPEN or CLOSED state before and during the job, since a large count of non-COMPRESSED row groups is what predicts a stall rather than the job genuinely taking that long. - For a partitioned columnstore table, batch archive compression by partition — reorganizing one cold partition at a time — rather than issuing one REORGANIZE against the entire table, so active partitions don’t interfere with the archive pass.
🚨 Why This Is Dangerous
- A maintenance job that appears to hang can get killed mid-run by an operator assuming it’s broken, which can leave a table with a mix of partially-archived and never-touched row groups that then needs a second full pass to reconcile, doubling the actual maintenance cost.
Archive compression can only finish a row group once the row group itself has stopped changing shape — a table that’s still quietly being tuned underneath it isn’t idle just because nothing visible is running against it.

