Loading Patterns, Partition Switching, Index Reorganization/Rebuild, and Fragmentation

Learning outcomes

A columnstore system can start fast and then degrade because ingestion creates many undersized rowgroups, deletes accumulate, or maintenance rebuilds far more data than necessary. “Fragmentation” therefore means something different from a B-tree page-order metric. Columnstore health is about rowgroup fullness/state, deleted rows, trim reasons, segment overlap and whether the loading pattern lets SQL Server create efficient compressed groups. Maintenance should repair an observed problem at the smallest justified scope.

1. Load shape determines rowgroup quality

Single-row/trickle inserts go to delta stores. Large bulk operations can bypass the delta store and create compressed rowgroups directly when the batch for a partition is large enough. Microsoft documents 102,400 rows per partition as the threshold for direct compressed bulk-load behavior, with the ideal compressed rowgroup approaching 1,048,576 rows. Smaller batches are not “invalid,” but persistent small groups reduce compression and give the engine more segments to scan/manage.

DROP TABLE IF EXISTS lab19.LoadTarget;GOCREATE TABLE lab19.LoadTarget(  load_id bigint NOT NULL,  loaded_at datetime2(0) NOT NULL,  region_code char(3) NOT NULL,  amount decimal(12,2) NOT NULL);CREATE CLUSTERED COLUMNSTORE INDEX CCI_LoadTarget ON lab19.LoadTarget;GO-- Small batch: normally enters delta storage.INSERT lab19.LoadTarget(load_id,loaded_at,region_code,amount)SELECT TOP (20000)       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),       DATEADD(second,CONVERT(int,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))),'2026-01-01'),       'N01',100.00FROM sys.all_objects a CROSS JOIN sys.all_objects b;GO-- Larger batch: designed to cross the documented 102,400-row threshold.;WITH n AS(  SELECT TOP (130000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n  FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT lab19.LoadTarget(load_id,loaded_at,region_code,amount)SELECT 1000000+n,DATEADD(second,CONVERT(int,n),'2026-02-01'),'S02',125.00FROM n;GOSELECT row_group_id,state_desc,total_rows,trim_reason_descFROM sys.dm_db_column_store_row_group_physical_statsWHERE object_id=OBJECT_ID(N'lab19.LoadTarget')ORDER BY row_group_id;GO

Observe rather than memorize the exact rowgroup layout: memory pressure, parallelism, dictionary limits and partition boundaries can trim groups. The lesson's decision is not “always batch one million rows,” but “feed sufficiently large, partition-aware batches when your pipeline can do so, then measure rowgroup quality.”

2. REORGANIZE is incremental; REBUILD recreates rowgroups

ALTER INDEX ... REORGANIZE for columnstore can merge smaller compressed rowgroups and physically remove deleted rows according to engine thresholds. COMPRESS_ALL_ROW_GROUPS = ON additionally forces eligible open/closed delta groups into compressed storage. REBUILD recreates all rowgroups in the specified table or partition, which can improve order/compression but consumes more CPU, I/O, log and maintenance time.

SELECT  rg.partition_number,  rg.row_group_id,  rg.state_desc,  rg.total_rows,  rg.deleted_rows,  CAST(100.0*rg.deleted_rows/NULLIF(rg.total_rows,0) AS decimal(6,2)) AS deleted_pct,  rg.trim_reason_desc,  rg.size_in_bytesFROM sys.dm_db_column_store_row_group_physical_stats AS rgWHERE rg.object_id=OBJECT_ID(N'lab19.LoadTarget')ORDER BY rg.partition_number,rg.row_group_id;GO-- Use only after the report justifies it.ALTER INDEX CCI_LoadTarget ON lab19.LoadTargetREORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);GO

Microsoft's DMV documentation gives examples such as investigating high deleted-row percentages, but a fixed “20% means rebuild everything” policy is still incomplete. A large table might have one damaged partition; a full rebuild can create far more log, tempdb/workspace and operational risk than a partition-scoped action.

3. Partitioning isolates load/maintenance units

Each partition has its own columnstore rowgroups and delta rowgroups. That makes partitioning valuable for time-based lifecycle management: load a staging table, validate it, switch a partition into the fact table, and later switch/drop old partitions. Partition switching is metadata-driven only when source and target are structurally aligned—including columns, indexes, partition boundaries and constraints. It is not a “fast INSERT” that ignores schema/index equivalence.

-- Pattern only: execute after creating aligned partition function/scheme and tables.-- Both source and target must have compatible structure/indexes and constraints.BEGIN TRAN;  -- Validate staging row count/business checks first.  -- ALTER TABLE dbo.StageWorkOrderFact  -- SWITCH TO dbo.WorkOrderFact PARTITION 12;COMMIT;GO-- Partition-scoped columnstore maintenance pattern:-- ALTER INDEX CCI_WorkOrderFact ON dbo.WorkOrderFact-- REBUILD PARTITION = 12;GO

The mandatory lab does not repartition the established ServiceHub database because partition-function changes are database-lifecycle work. The pattern is shown explicitly, while the hands-on work uses disposable tables. In production, test switch constraints, compression/index alignment, locking and rollback before treating partition switching as a load SLA.

4. Delete/reload can be better than update-heavy churn

Large corrections to immutable fact slices often fit a partition or batch replacement model better than millions of random row updates. An update to compressed columnstore effectively marks the old version deleted and inserts a new version. For a whole closed month, rebuilding/replacing a clean slice can be operationally simpler than generating pervasive delete bitmap pressure—but only when source-of-truth and transaction/recovery rules permit it.

DELETE FROM lab19.LoadTargetWHERE load_id BETWEEN 1005000 AND 1029999;GOSELECT row_group_id,total_rows,deleted_rows,       CAST(100.0*deleted_rows/NULLIF(total_rows,0) AS decimal(6,2)) AS deleted_pct,       state_desc,trim_reason_descFROM sys.dm_db_column_store_row_group_physical_statsWHERE object_id=OBJECT_ID(N'lab19.LoadTarget')ORDER BY row_group_id;GO

5. Rebuild and ordered-columnstore implications in SQL Server 2025

An ordered columnstore rebuild can improve segment order but is not free. SQL Server 2025 improves sort quality for ordered clustered columnstore; an online ordered build with MAXDOP=1 uses tempdb for sorting and can achieve full order, trading additional I/O and duration for reduced overlap. On boxed SQL Server, online index create/rebuild remains Enterprise-only. Standard/Express maintenance must plan offline alternatives and locking windows instead of copying Enterprise scripts.

DROP TABLE IF EXISTS lab19.LoadTarget;GO

6. Production judgment and bridge

Design the ingestion pipeline together with the index: batch size, ordering, partitioning, update/delete behavior, compression delay and maintenance window determine columnstore quality. Capture rowgroup evidence before and after maintenance and record logging/tempdb/elapsed impact locally. Lesson 5 applies all of this to mixed operational analytics and asks the architectural question: when should analytics stay beside OLTP, and when should it move to a warehouse/lakehouse?

Authoritative references

• Columnstore indexes - Data loading guidance
• Columnstore indexes - Design guidance
• Optimize columnstore index maintenance
• sys.dm_db_column_store_row_group_physical_stats
• Performance tuning with ordered columnstore indexes