Columnstore rows do not all enter compressed storage immediately. Columnstore bulk load batch size helps determine whether rows bypass the delta store. Inspect rowgroup states before blaming a scan for reading fresh data inefficiently.
Separate Delta Storage From Compressed Storage
A clustered columnstore index stores compressed rows in column segments organized into rowgroups. New rows can also enter a rowstore delta structure. An open delta rowgroup accepts more rows before later compression. This is normal ingestion behavior, not proof of corruption.
I inspect rowgroup states before proposing a rebuild. The workload can contain recent trickle inserts that belong in delta storage for now. Rebuilding everything to remove a small amount of fresh delta data can cost more than the scan problem it is supposed to solve.
The bulk-load threshold is 102,400 rows for direct compressed rowgroup loading. A compressed rowgroup can contain up to 1,048,576 rows, subject to practical trimming and resource conditions. Those two numbers describe different boundaries. The threshold is not the ideal maximum rowgroup size.
Prepare Two Controlled Columnstore Bulk Load Tests
Use SQL Server 2022 or later with compatibility level 160 for the GENERATE_SERIES input. The clustered columnstore feature itself predates that generator. Create two separate disposable targets so the loads do not share an existing delta rowgroup. No primary-key index is needed for this small storage experiment.
CREATE TABLE dbo.ColumnLoadSmallDemo
(RowID int NOT NULL, CategoryID int NOT NULL, Amount decimal(12,2) NOT NULL);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_ColumnLoadSmallDemo
ON dbo.ColumnLoadSmallDemo;
CREATE TABLE dbo.ColumnLoadLargeDemo
(RowID int NOT NULL, CategoryID int NOT NULL, Amount decimal(12,2) NOT NULL);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_ColumnLoadLargeDemo
ON dbo.ColumnLoadLargeDemo;
INSERT dbo.ColumnLoadSmallDemo
SELECT value,value%100,10 FROM GENERATE_SERIES(1,100000,1)
OPTION(MAXDOP 1);
INSERT dbo.ColumnLoadLargeDemo
SELECT value,value%100,10 FROM GENERATE_SERIES(1,150000,1)
OPTION(MAXDOP 1);MAXDOP one keeps worker distribution out of this particular comparison. It is an experimental control, not a universal loading recommendation. The chosen input sizes fall below and above the direct-compression threshold. They are setup values, not a claim about observed elapsed time.
Read the Actual Rowgroup States
Use sys.dm_db_column_store_row_group_physical_stats after the loads. State_desc describes whether a rowgroup is open, closed, compressed, or in another lifecycle state. Trim_reason_desc helps explain why a compressed group is smaller than the maximum. Retain rowgroup identifiers and row counts with the capture.
SELECT OBJECT_NAME(object_id) AS TableName,index_id,
partition_number,row_group_id,state_desc,total_rows,
deleted_rows,trim_reason_desc,size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id IN
(OBJECT_ID(N'dbo.ColumnLoadSmallDemo'),
OBJECT_ID(N'dbo.ColumnLoadLargeDemo'))
ORDER BY TableName,partition_number,row_group_id;Inspect the returned states instead of assuming every environment presents the same timing. Background compression can change the state between captures. Memory pressure and engine behavior also affect groups. The report describes the current storage state, not a permanent label attached to the original batch.
A small compressed group is not automatically a bad group. It can be the valid remainder of a load or a deliberate latency trade-off. The important question is whether the resulting layout serves the scan workload and ingestion requirements efficiently.
Apply the Columnstore Bulk Load Threshold to Each Stream
Parallel loading can distribute rows across workers. Partitioned loading distributes rows across partitions. The total incoming batch therefore does not guarantee that each resulting stream reaches the threshold. A large file can still leave small delta remainders when its rows are split widely.
Review the partition and worker distribution before increasing the external batch size. A batch with enough aggregate rows can contain only a small number for each date partition. Increasing concurrency can also increase memory demand. More loaders are not automatically more throughput.
I compare group sizes with the actual ingestion pattern. Which constraint matters most: immediate availability, sustained throughput, or a compact scan layout? A warehouse refresh and a near-real-time stream need different answers. The threshold provides a storage rule, not the complete scheduling policy.
Compress the Remaining Delta Groups Deliberately
SQL Server 2016 and later support REORGANIZE with COMPRESS_ALL_ROW_GROUPS. The option forces open or closed delta groups into compressed storage, even when they are small. Use it on the disposable small-load target and inspect the states again.
ALTER INDEX CCI_ColumnLoadSmallDemo
ON dbo.ColumnLoadSmallDemo
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS=ON);
SELECT row_group_id,state_desc,total_rows,trim_reason_desc
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id=OBJECT_ID(N'dbo.ColumnLoadSmallDemo')
ORDER BY row_group_id;The old delta group stays listed as TOMBSTONE until background cleanup removes it. This operation has resource and logging cost. Do not attach it to every tiny insert batch without measurement. Forcing small groups to compress immediately can produce many small compressed groups and repeated maintenance work. A short open-group lifetime is not automatically worth that cost.
REORGANIZE can also merge eligible compressed groups under its merge policy. It is not a promise that every group reaches the maximum size. Dictionary size, deleted rows, and other limits can constrain the result. Read the post-maintenance layout rather than guessing from the command's successful completion.
Measure Scans After a Columnstore Bulk Load
Capture STATISTICS IO and STATISTICS TIME for the important scan before and after the maintenance. Keep the same projection and filter. Rowgroup elimination depends on segment values, not just compression state. A query selecting a narrow date range can behave differently from a full-table aggregation.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT CategoryID,SUM(Amount) AS TotalAmount
FROM dbo.ColumnLoadSmallDemo GROUP BY CategoryID;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Include loading duration, transaction log growth, and memory demand in the broader comparison. Larger batches can improve direct compression while increasing the amount of work held in one transaction. Choose a size that respects the operational recovery and concurrency requirements too.
Keep Trickle Feeds Practical
A stream of small inserts naturally uses delta storage. Buffering can consolidate rows into larger loads, but it introduces delay and failure-handling responsibilities. Decide how long the business can wait for records to become available before designing that buffer.
Do not change durability settings to make an ingestion demonstration faster. Keep recovery requirements explicit. Use appropriate monitoring for open groups and maintenance cadence rather than treating every delta row as an emergency. The open bin is part of the storage design, not an unpaid storage bill.
Verify the Complete Input
Check total row counts and business totals after each load, independently of rowgroup state. Compression does not excuse missing input. If a load fails partway, record the transaction outcome and retry design before trying another batch size.
For production, retain the rowgroup report with capture time, load identifiers, and partition context. Compare several representative loads rather than one isolated sample. That evidence explains whether batching, distribution, or selective maintenance improves the real workload. The goal is reliable ingestion and efficient scans together.
Columnstore bulk load decisions need the resulting rowgroup distribution beside the input batch size. Measure scans and ingestion together before changing the columnstore bulk load policy.
Related reading on this blog: Columnstore Rowgroup Health: Finding Small and Open Rowgroups and Compression Delay for Columnstore Index.
Columnstore batch size is not just an input convenience, it is a storage decision whose benefits depend on each resulting rowgroup stream.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
