We put a clustered columnstore index on a two-billion-row fact table and the nightly report went from four minutes to six seconds. Then a well-meaning service started trickle-inserting rows one commit at a time, and within two months the same report took ninety seconds. The compression ratio had barely moved. The rowgroups had rotted, and the difference between those two statements is the entire mental model this article is about.
Columnstore is not a compression feature you bolt onto a table. It is a different execution model with its own lifecycle, its own failure modes, and its own instrumentation. Here is how I think about it on SQL Server 2016 through 2022, including where it loses to a boring btree.
Why is columnstore an execution model rather than a compression feature?
Because the speed comes from three mechanisms compounding, and compression is only the multiplier behind them. First, column segments: each column is stored separately, so a query reads only the columns it references. Second, segment elimination: every segment carries minimum and maximum metadata, so the engine skips whole rowgroups that cannot contain matching rows. Third, batch mode: operators process roughly nine hundred rows per loop in vectorized batches instead of one row at a time, which changes the CPU economics of big scans completely.
The unit underneath all of this is the rowgroup, up to 1,048,576 rows, with each column in each rowgroup stored as its own segment with its own dictionary. When your WHERE clause touches three of forty columns and the segment metadata eliminates ninety percent of rowgroups, you read a fraction of a fraction of the table, and the ten-to-one compression shrinks the I/O behind that again. That is how the same data becomes fifty times faster. It was never one trick.
Clustered or nonclustered columnstore, which one do I build?
Clustered columnstore is the table's primary storage and is the right default for fact tables and analytics-heavy tables that bulk-load and get scanned. Nonclustered columnstore is a secondary, read-optimized copy that sits beside an ordinary rowstore table, one per table, updatable since SQL Server 2016, and it is the tool for real-time operational analytics: reports against live OLTP data without dragging the OLTP workload down.
The nonclustered pattern deserves more use than it gets. The OLTP workload keeps its btrees for seeks, the dashboards run against the columnar copy, and a filtered nonclustered columnstore covering only the hot rows keeps maintenance cheap. My dividing line: if the table's main job is being scanned, go clustered. If its main job is being poked one row at a time but the business insists on live dashboards over it, keep the rowstore and add a filtered nonclustered columnstore.
What is the deltastore, and why do trickle inserts rot compression?
New rows land in deltastore rowgroups, which are ordinary uncompressed btrees, and they only become columnar when a rowgroup fills to its million-row cap and closes, or when the tuple mover background process sweeps closed rowgroups into compressed format. A row-at-a-time trickle does not spray tiny rowgroups around, because concurrent inserts share the open delta rowgroup; what it does is keep a permanently growing slice of the table parked in uncompressed btree storage, scanning at rowstore speed and giving segment elimination nothing to work with. And when someone eventually forces the backlog down, the half-full delta rowgroups compress into undersized columnar rowgroups that never merge back, so the table rots from both ends. That is exactly what our well-meaning service did to the fact table.
The damage is fully visible in sys.dm_db_column_store_row_group_physical_stats, which shows state_desc (OPEN, CLOSED, COMPRESSED, TOMBSTONE), total_rows, deleted_rows, and trim_reason_desc explaining why a rowgroup stopped short of the cap:
SELECT rg.state_desc, rg.trim_reason_desc,
COUNT(*) AS rowgroups,
SUM(rg.total_rows) AS total_rows,
SUM(rg.deleted_rows) AS deleted_rows,
AVG(CAST(rg.total_rows AS float)) AS avg_rows_per_rowgroup
FROM sys.dm_db_column_store_row_group_physical_stats AS rg
WHERE rg.object_id = OBJECT_ID(N'dbo.FactOrder')
GROUP BY rg.state_desc, rg.trim_reason_desc;The fixes, in order of preference. Load in batches of at least 102,400 rows so inserts bypass the deltastore and land directly in compressed rowgroups; this one change would have prevented our ninety-second report entirely. Run REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON to force the closed backlog into columnar format. REBUILD when the mess is structural. And remember that updates are delete-plus-insert under the hood: deleted_rows accumulate in a delete bitmap, and once the deleted fraction climbs past roughly ten percent, REORGANIZE earns its keep again.
What did batch mode on rowstore in 2019 actually change?
Under database compatibility level 150, the optimizer can run eligible analytical queries in batch mode against plain rowstore, with no columnstore anywhere in the plan, pulling batches through vectorized joins and aggregations straight from the buffer pool. Heuristics decide eligibility: wide scans, big aggregations, plenty of rows. You confirm it in the actual execution plan, where operators show Batch as their execution mode.
It will not turn a bad query into a good one, and it does nothing for OLTP-shaped statements, but on mixed workloads it hands back free CPU for the reporting-shaped queries nobody had time to index properly. Each release since has widened which operators can run in batch mode, so the 2019 baseline keeps getting more generous. Check your compatibility level before you assume you have it; plenty of 2022 instances still run databases at level 140 because nobody flipped the switch after the upgrade.
When does a plain btree still win?
For selective point seeks and hot OLTP. A btree lookup by key is three page touches and done, while a columnstore seek means opening segments for every referenced column and validating against the delete bitmap, and hammering a clustered columnstore with singleton updates generates maintenance debt faster than the tuple mover can retire it. If your access pattern is thousands of tiny seeks per second, stay rowstore and do not let anyone talk you out of it.
The engine itself votes the same way on correctness work: since SQL Server 2016 a clustered columnstore table can carry ordinary btree nonclustered indexes, and unique, primary key, and foreign key enforcement is built on those btrees. My rule after years of running both: thousands of tiny seeks per second means rowstore; scanning fifty million rows to aggregate means columnstore, and batch mode will repay the migration within a week. Tables that genuinely do both jobs get the nonclustered columnstore alongside the rowstore, and everyone keeps their job.
Watching rowgroup health with MonPG when SQL Server support ships
Columnstore health is exactly the kind of thing that should be a graph rather than a forensic query: the ratio of OPEN, CLOSED, and COMPRESSED rowgroups over time, average rows per rowgroup, deleted-row fractions, and the trim reasons that explain undersized groups. MonPG monitors PostgreSQL in production today; SQL Server support is in active development, and rowgroup telemetry is on the list of counters it should surface, because columnstore decay is invisible until a report doubles in runtime. Honest status lives on the SQL Server monitoring (coming soon) page. Until it ships, schedule the physical-stats query above weekly on any table you care about, and let the trend tell you when the tuple mover needs help.