You Have a Columnstore Index. But Is Your Data Actually in Columnstore?

MSSQLTips ran a good piece this month on when a columnstore index does not make sense -- the case where your queries pull back too many columns to benefit from columnar storage. Fair point, and a design question worth asking.

But there is another question I would ask first: is the columnstore index actually storing your data as columnstore?

Having a clustered columnstore index on a table does not mean every row in that table is sitting in a healthy compressed rowgroup. Depending on how the data was loaded, a significant number of rows can still be sitting in rowstore, or compressed into rowgroups far smaller than you expected. Before you decide columnstore was the wrong design, there is one DMV worth checking.

The number that matters is 1,048,576

A compressed rowgroup can hold up to 1,048,576 rows. That number gives you a useful benchmark when you start looking at rowgroup health. If your compressed rowgroups are consistently much smaller, or a significant number of rows have not been compressed at all, the next question is why. SQL Server gives you that answer. A rowgroup may still be sitting in the delta store, or it may have been compressed before it reached the maximum size. Those are different conditions, and the distinction matters when you decide what, if anything, needs to be fixed.

The delta store

Small inserts do not go directly into compressed columnstore format. They land in a delta rowgroup, which is a rowstore B-tree. For bulk loads, 102,400 rows is the important threshold: a sufficiently large batch can bypass the delta store and go directly into a compressed rowgroup, while smaller batches are inserted into the delta store.

That does not mean every small batch remains there indefinitely. As delta rowgroups fill, they close and the tuple mover can compress them. But the load pattern still matters. An application that continually inserts small batches can leave rows in OPEN or CLOSED delta rowgroups -- meaning some of the data behind that columnstore index is still physically stored as rowstore.

That is the first thing I want to know when I troubleshoot one.

Trimming

The other condition is a rowgroup that did get compressed, but closed before reaching 1,048,576 rows. SQL Server calls that trimming, and trim_reason_desc tells you why it happened.

A trimmed rowgroup is not automatically a problem. Some reasons are simply a consequence of how the data was loaded. Others point to memory pressure, dictionary limits, or maintenance activity. The important part is that you do not have to guess why the rowgroup is smaller than expected.

trim_reason_desc	What it means
NO_TRIM	The rowgroup reached the maximum row count.
BULKLOAD	The bulk-load batch size limited the row count
REORG	Forced compression as part of a REORG
DICTIONARY_SIZE	Dictionary size limited how many rows could be compressed together
MEMORY_LIMITATION	Available memory limited the size of the compressed rowgroup
RESIDUAL_ROW_GROUP	Remaining rows at the end of an index build
STATS_MISMATCH, SPILLOVER, AUTO_MERGE	Additional documented trim reasons -- see the DMV reference

This is where the DMV becomes more useful than simply counting rows. Two tables can both have undersized compressed rowgroups for completely different reasons. BULKLOAD points back to the size of the load. MEMORY_LIMITATION tells you SQL Server could not build the larger rowgroup with the memory available. DICTIONARY_SIZE tells you the dictionary itself became the limiting factor.

Same symptom -- smaller rowgroups -- but very different explanations. That is why I want the trim reason before I start trying to fix anything.

The query

This is the query I want first. Run it against the database and let the rowgroups tell you what condition you actually have:

SELECT 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.trim_reason_desc,
       rg.transition_to_compressed_state_desc,
       rg.size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats rg JOIN sys.indexes i
  ON  rg.object_id = i.object_id
  AND rg.index_id  = i.index_id
ORDER BY table_name, rg.row_group_id;

Four columns give me most of what I need. state_desc tells me whether the rowgroup is OPEN, CLOSED, or COMPRESSED. total_rows tells me how many rows made it into that rowgroup. trim_reason_desc tells me why a compressed rowgroup closed before reaching the maximum. And deleted_rows tells me how much logically deleted data is still sitting inside it.

Taken together, those values answer the questions I started with -- Are the rows actually compressed? If they are, what do the rowgroups look like? And if they are smaller than expected, why?

Watching it happen

The easiest way to see the difference is to give SQL Server the same data two different ways.

I am going to load 500,000 identical source rows into two clustered columnstore tables. The first gets the rows in ten 50,000-row batches. The second gets all 500,000 rows in one statement. Nothing else changes.

I am not measuring elapsed time or logical reads here. That is not the question. I just want to see where SQL Server puts the rows and what the rowgroup DMV says about them.

-- Scratch database only. Row counts are capped in the INSERTs.
USE tempdb;
GO

DROP TABLE IF EXISTS dbo.CCI_Trickle;
DROP TABLE IF EXISTS dbo.CCI_OneShot;
DROP TABLE IF EXISTS dbo.LoadStaging;
GO

CREATE TABLE dbo.LoadStaging
(
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderTotal DECIMAL(9,2) NOT NULL
);
GO

-- 500,000 staging rows, hard capped.
INSERT dbo.LoadStaging (OrderID, CustomerID, OrderDate, OrderTotal)
SELECT TOP (500000)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
       ABS(CHECKSUM(NEWID())) % 5000,
       DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 900, '2024-01-01'),
       (ABS(CHECKSUM(NEWID())) % 100000) / 100.0
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c;
GO

Table 1 -- the trickle load

The first table gets its 500,000 rows in ten batches of 50,000. Each individual load is below the 102,400-row threshold for bypassing the delta store, so these rows enter through delta rowgroups rather than going directly into compressed columnstore rowgroups.

Notice what I am not assuming. Ten inserts do not automatically produce ten delta rowgroups. SQL Server can continue adding rows to an existing OPEN delta rowgroup until it fills. The DMV will show us how those 500,000 rows were actually organized.

CREATE TABLE dbo.CCI_Trickle
(
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderTotal DECIMAL(9,2) NOT NULL,
    INDEX cci_CCI_Trickle CLUSTERED COLUMNSTORE
);
GO

DECLARE @Batch INT = 0;

WHILE @Batch < 10
BEGIN
    INSERT dbo.CCI_Trickle (OrderID, CustomerID, OrderDate, OrderTotal)
    SELECT OrderID, CustomerID, OrderDate, OrderTotal
    FROM dbo.LoadStaging
    WHERE OrderID >  (@Batch * 50000)
      AND OrderID <= ((@Batch + 1) * 50000);

    SET @Batch = @Batch + 1;
END
GO

Table 2 -- the same rows, one statement

The second table gets exactly the same 500,000 rows, but this time they arrive in a single load. Because the batch is well above the 102,400-row threshold, SQL Server can bypass the delta store and build compressed rowgroups directly.

That does not mean I expect one 500,000-row compressed rowgroup. SQL Server still controls how the load is divided into rowgroups. What matters for this test is that the rows are eligible to go directly into compressed columnstore storage rather than entering through the delta store first.

CREATE TABLE dbo.CCI_OneShot
(
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderTotal DECIMAL(9,2) NOT NULL,
    INDEX cci_CCI_OneShot CLUSTERED COLUMNSTORE
);
GO

INSERT dbo.CCI_OneShot (OrderID, CustomerID, OrderDate, OrderTotal)
SELECT OrderID, CustomerID, OrderDate, OrderTotal
FROM dbo.LoadStaging;
GO

Now run the diagnostic against both tables. This is what mine returned:

There it is.

Both tables contain exactly 500,000 rows. The one-shot load produced a COMPRESSED rowgroup immediately. It is smaller than the 1,048,576-row maximum, and SQL Server tells us exactly why: BULKLOAD. The incoming batch contained 500,000 rows, so that batch determined the size of the compressed rowgroup.

The trickle-loaded table is a completely different story. All 500,000 rows are sitting in one OPEN delta rowgroup. The table has a clustered columnstore index, but at this moment every row we loaded into it is still physically stored in rowstore.

That is the distinction I was looking for. If all I knew was that both tables had clustered columnstore indexes and both contained 500,000 rows, I would be missing the most important difference between them. The DMV exposes it immediately.

Forcing the issue

At this point the trickle-loaded table has 500,000 rows sitting in an OPEN delta rowgroup. If I do not want to wait for SQL Server to close and compress that rowgroup on its own, I can force compression:

ALTER INDEX cci_CCI_Trickle ON dbo.CCI_Trickle
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);
GO

Now run the DMV again. The important thing to watch is whether the rowgroup moves from OPEN to COMPRESSED, and what SQL Server records in trim_reason_desc.

Here is what mine returned:

The 500,000 trickle-loaded rows are now compressed. SQL Server created a new compressed rowgroup and records REORG as the trim reason, with REORG_FORCED as the transition to compressed state. That tells me exactly how this rowgroup got here. It did not fill naturally, and it was not compressed directly by the original load. I forced the OPEN delta rowgroup into compressed columnstore storage with REORGANIZE.

And now the two tables have arrived at essentially the same physical result by two very different paths. One went directly into a compressed rowgroup because the original load was large enough. The other spent its first life entirely in the delta store until I explicitly forced compression.

DROP TABLE IF EXISTS dbo.CCI_Trickle;
DROP TABLE IF EXISTS dbo.CCI_OneShot;
DROP TABLE IF EXISTS dbo.LoadStaging;
GO

What to do with the answer

What I see	What I look at next
OPEN or CLOSED delta rowgroups holding a lot of rows	Those rows are still in rowstore. Look at the load pattern and whether REORGANIZE ... COMPRESS_ALL_ROW_GROUPS belongs in maintenance.
Many COMPRESSED rowgroups trimmed by BULKLOAD	Look at the size of the incoming batches. Smaller bulk loads can produce smaller compressed rowgroups.
trim MEMORY_LIMITATION	The rowgroup was limited by available memory during compression. Investigate the memory conditions around the load or index operation.
trim DICTIONARY_SIZE	Dictionary size stopped the rowgroup from growing further. Look at the columns and data driving dictionary pressure.
A high number of deleted_rows	Logically deleted rows are still occupying the compressed rowgroup. Evaluate whether columnstore maintenance is warranted.

The reason I keep this query in the health check kit is that it separates conditions that look identical from the outside. Seeing a clustered columnstore index on a table tells me how the table was designed. It does not tell me where the rows are actually sitting.

Our two test tables made that pretty clear. Same 500,000 rows. Same table definition. Same clustered columnstore index. One load put all 500,000 rows directly into compressed columnstore storage. The other left all 500,000 sitting in an OPEN delta rowgroup as rowstore.

A columnstore index that is wrong for the workload needs a design conversation. A columnstore index whose rows are not getting compressed needs a different conversation entirely.

Before I blame columnstore, I want to know which conversation I am having.

More to Read

MSFT Learn: sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)
MSFT Learn: Columnstore indexes -- Data loading guidance
SQLCAT: Columnstore row group merge policy and index maintenance improvements
MSSQLTips: When a Columnstore Index Does Not Make Sense