Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a Columnstore Archive Compression Job Stalling

- 21.11.26 - ErcanOPAK

🗄️ 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_stats for 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.

— Database Engineer

Related posts:

How to solve “A cursor with the name already exists” problem?

How to solve “A cursor with the name already exists” problem?

SQL: Use Computed Columns for Automatic Calculated Values

SQL: Use Computed Columns for Automatic Calculated Values

How to solve 'incorrect syntax near the keyword WITH' problem?

How to solve 'incorrect syntax near the keyword WITH' problem?

Views: 2

Post navigation

Photoshop: Fix a Pattern Preview Mode Not Tiling at the Canvas Edge
Windows 11: Fix Snap Layouts Forgetting a Custom Grid After Sleep

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (661)
  • Add Constraint to SQL Table to ensure email contains @ (619)
  • Average of all values in a column that are not zero in SQL (577)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (553)
  • Find numbers with more than two decimal places in SQL (481)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store
  • WordPress: Fix a Navigation Block Losing Submenu Items After Sync
  • Visual Studio: Fix CodeLens Reference Counts Stuck at Stale Numbers
  • C#: Fix a UTF-8 String Literal Behaving Differently in a Switch
  • Photoshop: Fix a Liquify Mesh Snapping Back on a Smart Object
  • The AI Prompt That Turns a Crash Dump Into a Short Repro Script

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com