Small inserts into a clustered columnstore do not all become compressed segments immediately. Small loads can first enter the delta store, where rowstore structures hold them.
Understand the Delta Store as Intermediate Storage
The delta structure is a rowstore component of a disk-based columnstore index. It accepts rows that have not entered compressed column segments. Queries can still access those rows while they remain uncompressed.
This intermediate state is normal during ingestion. Its existence alone does not establish a performance defect. The amount of uncompressed data, its age, and the reporting workload determine whether intervention helps.
I inspect rowgroup states before recommending index maintenance. I also review how the application batches its inserts. Repeated maintenance cannot compensate indefinitely for an unsuitable loading pattern.
The examples use a clustered columnstore in a fresh isolated test database. They do not describe an in-memory columnstore tail. Related structures have different behavior and should not be treated as interchangeable.
Create a Deliberately Small Load
The sample inserts one thousand synthetic rows into an initially empty columnstore table. That chosen size stays below the direct bulk-compression threshold. It is a demonstration input rather than a reported observed rowgroup size.
A three-digit cross join generates the fixture deterministically. Each row has a simple numeric amount and category. No source file or external loading tool is required for the example.
CREATE TABLE dbo.ColumnstoreLoad
(
RowId int NOT NULL,
CategoryId int NOT NULL,
Amount decimal(12,2) NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_ColumnstoreLoad
ON dbo.ColumnstoreLoad;
WITH D AS
(
SELECT N FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS V(N)
), Numbers AS
(
SELECT A.N * 100 + B.N * 10 + C.N + 1 AS RowId
FROM D AS A CROSS JOIN D AS B CROSS JOIN D AS C
)
INSERT dbo.ColumnstoreLoad(RowId, CategoryId, Amount)
SELECT RowId, RowId % 5, CONVERT(decimal(12,2), RowId / 10.0)
FROM Numbers;Inspect the metadata soon after loading rather than assuming a permanent state. Background compression can change the layout between observations. Capture timestamps with your diagnostic output when comparing successive samples.
On SQL Server 2025, the load left one OPEN rowgroup holding all one thousand rows. Verify the states on the engine version and database used for your test.
Read OPEN, CLOSED, and COMPRESSED
OPEN identifies an uncompressed rowgroup that still accepts rows. CLOSED identifies a delta rowgroup waiting for compression. COMPRESSED identifies a rowgroup stored in compressed column segments.
Use sys.dm_db_column_store_row_group_physical_stats to inspect those states for the target table. The query also includes partition, row count, and transition details. These are actual documented fields rather than invented maintenance counters.
SELECT SYSUTCDATETIME() AS CapturedUtc,
partition_number, row_group_id, state_desc,
total_rows, deleted_rows, size_in_bytes,
trim_reason_desc, transition_to_compressed_state_desc
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(N'dbo.ColumnstoreLoad')
ORDER BY partition_number, row_group_id;For compressed groups, total_rows includes rows marked as deleted. Subtract deleted_rows when estimating currently live rows in those groups. Do not interpret physical rows as automatically equal to visible business records.
The DMV's size field excludes certain shared metadata and dictionaries. It is useful rowgroup evidence, not a complete database storage accounting. State clearly what the reported bytes represent.
Use the appropriate diagnostic permissions for your version. SQL Server 2022 and later require VIEW DATABASE PERFORMANCE STATE for this view. Earlier requirements include table CONTROL and database state visibility.
Distinguish the Two Delta Store Thresholds
A bulk-loading batch with at least 102,400 rows can enter compressed rowgroups directly. Smaller qualifying batches use the delta store. A leftover tail below that threshold can also remain in delta storage.
The maximum ordinary rowgroup size is 1,048,576 rows. That is a different number serving a different purpose. Do not confuse direct-load eligibility with the capacity that closes a filling delta rowgroup.
Partitioning divides the incoming rows before columnstore grouping. A large overall load can produce small portions in several partitions. Review rows delivered per partition rather than only the total file size.
Parallel loading also affects how rows reach separate groups. The headline threshold does not guarantee one ideal group for an entire operation. Inspect the resulting layout and execution path under the actual load arrangement.
Individual trickle inserts normally enter delta storage even when their cumulative total grows large. Increasing one application's transaction count is not the same as supplying a qualifying bulk batch. Choose the ingestion pattern deliberately.
Let Background Work Empty the Delta Store
The tuple mover compresses eligible closed delta rowgroups in the background. It is not a promise that every insert becomes compressed immediately. A diagnostic sample can legitimately show closed groups awaiting that work.
SQL Server 2019 added background merge assistance that can address eligible older small groups. Internal conditions decide when that work occurs. Avoid writing a monitoring rule that treats every temporary open group as an emergency.
Look for persistent accumulation rather than one isolated OPEN state. Compare snapshots across the workload's normal loading cycle. A continuously changing table needs a different expectation from a completed nightly load.
Compression timing does not change which rows the query should return. Both delta and compressed components contribute to query results. Investigate correctness separately from physical layout and scan efficiency.
Compress After a Completed Load When Appropriate
ALTER INDEX REORGANIZE can compress closed groups and perform supported columnstore maintenance. COMPRESS_ALL_ROW_GROUPS additionally requests compression of open groups. The following statement changes only the isolated demonstration index.
Run it after the small load and then capture the metadata again. Compare actual states and counts rather than assuming a specific rowgroup identifier survives. Maintenance can change the physical grouping and its metadata.
ALTER INDEX CCI_ColumnstoreLoad
ON dbo.ColumnstoreLoad
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
SELECT partition_number, row_group_id, state_desc,
total_rows, deleted_rows, trim_reason_desc,
transition_to_compressed_state_desc
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(N'dbo.ColumnstoreLoad')
ORDER BY partition_number, row_group_id;
SELECT CategoryId, SUM(Amount) AS TotalAmount
FROM dbo.ColumnstoreLoad
GROUP BY CategoryId;In my run, the OPEN rowgroup became a TOMBSTONE. A new COMPRESSED rowgroup took its rows, with the transition reason REORG_FORCED.
Forcing compression after every tiny batch can create many undersized compressed groups. That can undermine the reason for batching ingestion. Prefer a considered end-of-load boundary when the workload allows one.
Maintenance consumes CPU, logging, and other resources despite being a supported online operation. Evaluate those costs on representative data. The demonstration does not claim a measured storage reduction or faster aggregate.
Choose the Lasting Improvement
Does your reporting workload wait for a stable load to finish? That creates a natural point for evaluating targeted compression. A continuous feed requires a policy based on observed rowgroup age and volume.
I treat the delta store as part of the loading design. I change batch delivery before adding endless maintenance schedules. Loose rows do not become an architectural crisis simply because their suitcase is still open.
Review trim reasons when compressed groups remain smaller than expected. Memory and dictionary limits can affect group size independently of your chosen batch. Increasing batch size cannot override every physical limitation.
Keep ingestion, diagnostics, and maintenance evidence together when evaluating a change. Compare rowgroup quality with actual reporting plans and resource usage. The best layout is the one that improves the real workload at an acceptable operational cost.
Related reading on this blog: Columnstore Rowgroup Health: Finding Small and Open Rowgroups and Compression Delay for Columnstore Index.
An open rowgroup is not a broken columnstore, it is uncompressed input that needs a considered loading policy.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
