Columnstore and the delta store

In one line: a clustered columnstore index is only columnstore once its rows are compressed. Rows from small inserts sit in a delta store, an ordinary uncompressed rowstore, until 1,048,576 of them accumulate. And the order you load rows in decides how much of the table a query can skip.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/columnstore-delta-store on GitHub.

What you’ll see

  • A columnstore table that isn’t as small or as fast as promised. In the demo, 200,000 rows loaded by small inserts took 5,576 KB and 703 reads per scan. Loaded in one insert, they took 706 KB and 12 reads.
  • Rowgroups in state OPEN or CLOSED.
  • A one-day query that reads every rowgroup.

Why it happens

  • An insert of at least 102,400 rows goes straight into a compressed rowgroup. One row fewer (102,399 in the demo) and the whole lot goes into the delta store.
  • A delta store stays OPEN until it holds 1,048,576 rows. Only then is it closed and compressed by the background tuple mover. A table fed a few hundred thousand rows a day in small batches can stay a rowstore indefinitely.
  • Each compressed rowgroup records each column’s minimum and maximum, and a query skips rowgroups whose range can’t match. If every rowgroup spans every date, none can be skipped.

What to do

  • Load in batches of at least 102,400 rows.
  • Compress what trickles in on your own schedule: ALTER INDEX ... REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON). It’s online and cheap next to a rebuild. Don’t run it every few thousand rows: many small compressed rowgroups are better than delta stores but worse than full ones.
  • Load in the order you filter by. Build a rowstore clustered index on the filter column, then convert it with CREATE CLUSTERED COLUMNSTORE INDEX ... WITH (DROP_EXISTING = ON, MAXDOP = 1). On SQL Server 2022 you can also use the ORDER clause. The ordered table let a one-day query skip five of six rowgroups, for 5.5× fewer reads.

What doesn’t help

  • ORDER BY on the insert, or a parallel conversion. Threads split the input, and their rowgroups interleave the ranges again.
  • Waiting for the tuple mover. It only compresses delta stores that are already closed.

Check your server

-- Columnstore rowgroups in this database by state: OPEN/CLOSED are uncompressed delta stores.
SELECT OBJECT_SCHEMA_NAME(rg.object_id) AS schema_name,
       OBJECT_NAME(rg.object_id)        AS table_name,
       rg.state_desc,
       RowGroups = COUNT(*),
       TotalRows = SUM(rg.total_rows),
       AvgRows   = AVG(rg.total_rows),
       SizeMB    = CAST(SUM(rg.size_in_bytes) / 1048576.0 AS decimal(10, 1))
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
GROUP BY rg.object_id, rg.state_desc
ORDER BY table_name, rg.state_desc;

Many COMPRESSED rowgroups with a low AvgRows mean the loads are too small.

See it happen

The columnstore-delta-store demo finds the 102,400-row line, trickle-loads a table, compresses it with REORGANIZE, and compares rowgroup elimination on ordered and unordered copies of 6,000,000 rows.

Related: data compression · minimal logging · partition elimination

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights