Columnstore Architecture, Rowgroups, Segments, Dictionaries, and Compression

Learning outcomes

ServiceHub has accumulated hundreds of thousands of completed work-order facts. The operations team now asks questions such as “labor minutes by region for the last quarter” and “revenue by priority across an entire year.” A rowstore design that is excellent for finding one work order by ID can become wasteful when an analytical query reads many rows but only a few columns. Columnstore addresses that physical access pattern by organizing data into compressed column segments inside rowgroups. Its value is not merely “compression”: column elimination, segment elimination, vectorized batch processing, and compact storage work together.

1. Mental model: rows enter in groups, but compressed data is stored by column

A rowgroup is the unit SQL Server compresses together. A compressed rowgroup can contain up to 1,048,576 rows. For every column in that rowgroup, SQL Server creates a separate column segment. Repeated values can be represented through dictionaries and encodings; fixed-width values can exploit value encoding, bit packing and run-length patterns. Because the query can read only referenced columns, a wide fact table no longer requires every unused column to be pulled through the same row-oriented page access path.

A clustered columnstore index (CCI) is the table's primary storage format. It does not have a B-tree key in the rowstore sense. Compressed rowgroups coexist with rowstore-format delta rowgroups when small inserts arrive, while updates to compressed rows are implemented logically as delete-plus-insert. Deleted rows in compressed rowgroups remain physically present until maintenance merges/rebuilds them, which is why the delete bitmap and deleted_rows matter operationally.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab19') IS NULL EXEC(N'CREATE SCHEMA lab19 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab19.WorkOrderFact;GOCREATE TABLE lab19.WorkOrderFact(  fact_id          bigint        NOT NULL,  work_order_id    bigint        NOT NULL,  opened_at        datetime2(0)  NOT NULL,  region_code      char(3)       NOT NULL,  status            varchar(16)   NOT NULL,  priority          tinyint       NOT NULL,  labor_minutes     int           NOT NULL,  amount            decimal(12,2) NOT NULL,  technician_code   varchar(12)   NOT NULL,  note_class        varchar(20)   NOT NULL);GO;WITH n AS(  SELECT TOP (360000)         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n  FROM sys.all_objects AS a  CROSS JOIN sys.all_objects AS b)INSERT lab19.WorkOrderFact(fact_id,work_order_id,opened_at,region_code,status,priority, labor_minutes,amount,technician_code,note_class)SELECT n,       1000000+n,       DATEADD(minute,CONVERT(int,n),'2025-01-01T00:00:00'),       CASE n%4 WHEN 0 THEN 'N01' WHEN 1 THEN 'S02' WHEN 2 THEN 'E03' ELSE 'W04' END,       CASE n%5 WHEN 0 THEN 'CLOSED' WHEN 1 THEN 'ONSITE' WHEN 2 THEN 'ASSIGNED' WHEN 3 THEN 'NEW' ELSE 'ESCALATED' END,       CONVERT(tinyint,1+n%5),       15+CONVERT(int,n%480),       CONVERT(decimal(12,2),40+(n%9000)/10.0),       CONCAT('T-',RIGHT(CONCAT('000',n%250),3)),       CASE n%4 WHEN 0 THEN 'routine' WHEN 1 THEN 'parts' WHEN 2 THEN 'safety' ELSE 'follow-up' ENDFROM n;GOCREATE CLUSTERED COLUMNSTORE INDEX CCI_WorkOrderFactON lab19.WorkOrderFactWITH (MAXDOP = 1);GO

The row count is intentionally moderate so a laptop can run the lab. It is not a performance benchmark and it may produce only a few compressed rowgroups. If the machine is constrained, reduce the row count; if you want to study more rowgroups, increase it deliberately and record the new dataset size. MAXDOP = 1 is used here to make the build behavior easier to reason about, not because serial columnstore builds are universally better.

2. Observe rowgroups, compression reasons, and segment inventory

SELECT  OBJECT_SCHEMA_NAME(rg.object_id) AS schema_name,  OBJECT_NAME(rg.object_id) AS table_name,  i.name AS index_name,  rg.row_group_id,  rg.state_desc,  rg.total_rows,  rg.deleted_rows,  rg.size_in_bytes,  rg.trim_reason_desc,  rg.transition_to_compressed_state_descFROM sys.dm_db_column_store_row_group_physical_stats AS rgJOIN sys.indexes AS i  ON i.object_id=rg.object_id AND i.index_id=rg.index_idWHERE rg.object_id=OBJECT_ID(N'lab19.WorkOrderFact')ORDER BY rg.row_group_id;GO

COMPRESSED rows are stored in columnstore format. OPEN and CLOSED states belong to the delta store. A compressed group with fewer than the maximum rows is not automatically defective: trim_reason_desc can identify a residual group, bulk-load boundary, memory pressure, dictionary pressure, reorganization, or automatic merge. Treat the reason and workload context as evidence before scheduling maintenance.

SELECT  c.name AS column_name,  COUNT(*) AS segment_count,  SUM(s.on_disk_size) AS segment_bytes,  SUM(CASE WHEN s.primary_dictionary_id >= 0 THEN 1 ELSE 0 END) AS segments_with_primary_dictionary,  SUM(CASE WHEN s.secondary_dictionary_id >= 0 THEN 1 ELSE 0 END) AS segments_with_secondary_dictionaryFROM sys.column_store_segments AS sJOIN sys.partitions AS p ON p.hobt_id=s.hobt_idJOIN sys.columns AS c ON c.object_id=p.object_id AND c.column_id=s.column_idWHERE p.object_id=OBJECT_ID(N'lab19.WorkOrderFact')GROUP BY c.nameORDER BY segment_bytes DESC;GOSELECT dictionary_id, column_id, type, entry_count, on_disk_sizeFROM sys.column_store_dictionariesWHERE hobt_id IN(  SELECT hobt_id FROM sys.partitions  WHERE object_id=OBJECT_ID(N'lab19.WorkOrderFact'))ORDER BY column_id, dictionary_id;GO

Do not read internal min/max encoding columns as a supported business API; Microsoft documents several of them as internal-use fields. For operational health, prefer rowgroup state/size/delete metadata and documented query evidence. Dictionaries are also not “one dictionary per table.” They can be global or segment-local and may not be created where the encoding strategy does not need one.

3. Segment elimination is metadata-driven skipping

Each segment carries range metadata that can let the storage engine reject an entire rowgroup before reading its values. Ordering or naturally clustered load patterns can make those ranges narrower and reduce overlap. The effect is visible in SET STATISTICS IO: for columnstore scans SQL Server reports segment reads and segment skipped. The exact counts depend on this local data distribution, build options and predicate, so the lesson asks you to measure instead of promising a fixed percentage.

SET STATISTICS IO ON;SET STATISTICS TIME ON;GOSELECT region_code,       COUNT_BIG(*) AS orders,       SUM(labor_minutes) AS labor_minutes,       SUM(amount) AS amountFROM lab19.WorkOrderFactWHERE opened_at >= '2025-08-01'  AND opened_at <  '2025-08-08'GROUP BY region_code;GOSELECT region_code,       COUNT_BIG(*) AS orders,       SUM(labor_minutes) AS labor_minutes,       SUM(amount) AS amountFROM lab19.WorkOrderFactGROUP BY region_code;GOSET STATISTICS IO OFF;SET STATISTICS TIME OFF;GO

Compare the messages pane. A selective date predicate can skip rowgroups only if rowgroup ranges make skipping possible. A full-range aggregate cannot eliminate the same segments because all ranges qualify. If both queries read similar segments, investigate overlap/load order rather than declaring “columnstore does not work.” Lesson 2 introduces ordered columnstore specifically to reduce that overlap.

4. Wrong approach: replace every rowstore access path with columnstore

Columnstore favors scans and aggregates over large ranges. A transactional request such as “fetch work order 1,234,567 immediately” is a different access pattern. A CCI can coexist with a narrow rowstore nonclustered index so selective requests still have a seek-oriented structure while analytical scans retain columnstore benefits.

CREATE NONCLUSTERED INDEX IX_WorkOrderFact_WorkOrderIdON lab19.WorkOrderFact(work_order_id)INCLUDE(opened_at,status,region_code);GOSELECT work_order_id, opened_at, status, region_codeFROM lab19.WorkOrderFactWHERE work_order_id=1234567;GO

Inspect the actual plan. The optimizer can choose the B-tree index for a selective lookup and the CCI for an aggregate. The extra rowstore index is not free: every insert/update/delete must maintain it, it consumes storage and memory, and it can reduce load throughput. The right portfolio comes from workload evidence, not a rule that “warehouse tables need no B-trees.”

5. Production judgment and bridge

Use columnstore when large-range scans and aggregates dominate enough of the workload to justify a segment-oriented storage path. Monitor rowgroup count/size, delete percentage, delta-store backlog, segment elimination, query plans, memory grants and write overhead. Do not use compression ratio alone as the acceptance criterion. Lesson 2 compares clustered and nonclustered designs and shows how delta stores, tuple mover, ordered columnstore and maintenance change the picture for continuously written tables.

Authoritative references

• Columnstore indexes: Overview
• Columnstore indexes - Query performance
• sys.dm_db_column_store_row_group_physical_stats
• sys.column_store_segments
• SQL Server 2025 editions and supported features