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 Index Fragmenting After Bulk Deletes

- 19.11.26 - ErcanOPAK

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

— Database Engineer

Related posts:

SQL Server: Fix a Query That Falls Back to an Expensive Key Lookup Because One Extra Column in the S...

SQL Server: Fix a Query That Falls Back to an Expensive Key Lookup Because One Extra Column in the S...

What does COALESCE do in SQL?

What does COALESCE do in SQL?

SQL Server: Fix a Stored Procedure's Cached Execution Plan That Goes Stale the Moment a Client Chang...

SQL Server: Fix a Stored Procedure's Cached Execution Plan That Goes Stale the Moment a Client Chang...

Views: 4

Post navigation

Docker: Fix a Container Losing Logs After a Restart Policy
Asp.Net Core: Fix a Background Service Leaking Scoped Dependencies

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,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (658)
  • Add Constraint to SQL Table to ensure email contains @ (613)
  • Average of all values in a column that are not zero in SQL (571)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (549)
  • Find numbers with more than two decimal places in SQL (477)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK
  • Git: Fix a Worktree Locking a Branch Needed Somewhere Else
  • Asp.Net Core: Fix a Background Service Leaking Scoped Dependencies
  • SQL Server: Fix a Columnstore Index Fragmenting After Bulk Deletes
  • Docker: Fix a Container Losing Logs After a Restart Policy
  • Asp.Net Core: Fix Output Caching Serving a Stale Authorized Page

Most Viewed Posts

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

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK

Social

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