Columnstore Health

Overview

Columnstore is where the slowest queries in a data warehouse live. Its performance depends almost entirely on rowgroup quality, that quality degrades silently with ordinary use, and the fix is a maintenance command most people do not know exists.

The Columnstore Health report: a comb of rowgroups against the ideal line
The whole report. One column per rowgroup in load order, the dashed line the 1,048,576 row ideal, deleted rows shaded inside each column.

Where to find it

In the tree, under a database, Indexing → Columnstore Health. Hidden below SQL Server 2016.

The toolbar switches between By index, Rowgroups and Trim reasons.

A database with no columnstore index gets a sentence rather than an empty grid, because on most databases that is the correct and permanent answer.

Requirements

Requirement Why
SQL Server 2016 or newer sys.dm_db_column_store_row_group_physical_stats arrived then, and it is what carries trim reason.
VIEW DATABASE STATE The rowgroup statistics are database scoped.
A columnstore index Otherwise there is nothing to report and the page says so.

The four things it measures

The fourth is the one that turns the page from an observation into a fix.

1. Rowgroup state mix

Compressed against open against closed against tombstone.

Rows sitting in an open delta rowgroup are rowstore. No compression, and no batch mode. A table with thirty per cent of its rows in the delta store is not really a columnstore table at query time, however it is defined, and that is the sentence the banner uses when it finds one.

2. Average rows per rowgroup

Against the 1,048,576 row ideal. This is the single number that predicts scan performance. A table averaging a hundred thousand rows per rowgroup is doing roughly ten times the rowgroup metadata work it should be.

The average excludes delta rowgroups. An open one is still filling, and counting it against the ideal would report every healthy table as degraded for as long as it is loading.

3. Deleted row bloat

deleted_rows against total_rows. Deleted rows are still read on every scan and then discarded, so a rowgroup at forty per cent deleted costs full price and delivers sixty per cent.

4. Trim reason

Why the rowgroup closed short. Each one has a genuinely different answer, which is why this is a view of its own rather than a column nobody reads.

Trim reason Normal? What to do
NO_TRIM Yes The rowgroup filled properly.
BULKLOAD No The load batch was smaller than a rowgroup. Raise the batch size to 1,048,576 rows or more and they fill on their own.
MEMORY_LIMITATION No The build did not get the grant it asked for and closed early. See Memory Grants and Spills.
DICTIONARY_SIZE No A high cardinality string column filled the dictionary before the rowgroup filled. Narrow it, or take it out of the index.
STATS_MISMATCH No The estimated row count was wrong when the rowgroup was built.
SPILLOVER Yes The remainder of a load that did not divide evenly.
RESIDUAL_ROW_GROUP Yes The last rowgroup of a load.
REORG Yes Produced by a REORGANIZE.
MERGE_BUDGET Yes A merge could not take everything it wanted.

SPILLOVER and RESIDUAL_ROW_GROUP are ordinary remainders. The page marks them normal rather than reporting them as findings, which is what a page that treated every non NO_TRIM value as a problem would do.

The verdicts

Verdict When
Poor Nothing compressed at all, or deleted rows or the delta store dominate, or rowgroups are far below the ideal.
Degraded Rowgroups meaningfully below the ideal, or a substantial deleted share.
Watch A small delta or deleted share worth keeping an eye on.
Healthy Nothing to do.

A table smaller than one rowgroup is never judged on its average. It cannot fill a rowgroup, and reporting it as though it failed to would flag every small columnstore table on the instance.

Reading the chart

One column per rowgroup, in load order, with the deleted share shaded inside the column and the 1,048,576 row ideal drawn as a dashed line across it.

Load order rather than sorted, because the shape is the finding: a comb that is tall at the left and short at the right is a loader that changed, and sorting the columns by height would destroy exactly that reading.

Delta rowgroups are drawn in their own colour so they cannot be mistaken for short compressed ones. They are two different problems with two different fixes.

The comb draws the most recent 400 rowgroups. The grid counts all of them, and the footer says so when the cut applies.

Reading the grids

By index carries the verdict, the rowgroup count, the average, the deleted and delta shares, the dominant trim reason and the fix.

Rowgroups is one row per rowgroup for the selected index: state, id, partition, rows, deleted, share of ideal, size and trim reason.

Trim reasons aggregates every rowgroup in the database by reason, marks each normal or not, and carries the meaning and the action in full.

REORGANIZE is almost always the right command

Nothing on this page runs anything. The scripts are generated commented out, with the difference between the two commands stated above them rather than left to be discovered.

  • REORGANIZE is online, incremental and cheap. On a modern build it merges small rowgroups and removes deleted rows, and with COMPRESS_ALL_ROW_GROUPS it forces open and closed delta rowgroups into the columnstore rather than waiting for the tuple mover.
  • REBUILD on a large columnstore is a long, memory hungry operation that wants a maintenance window and a grant large enough to build a rowgroup at a time.

People reach for the rebuild because it is the command they know, and on this problem it is almost never the one they need.

The generated script adapts to the cause. On a BULKLOAD row it says out loud that the loader is producing these rowgroups and will keep producing them, so raising the batch size fixes the cause while a REORGANIZE only tidies up what has already happened. On a MEMORY_LIMITATION row it warns that maintenance run under the same memory pressure produces the same result, and to look at Memory Grants and Spills first.

Right-click actions in the grid

Action What it does
Show the maintenance script The commented script with the readings and the cause written into it.
Copy maintenance script The same, to the clipboard.
Go to Memory Grants and Spills On a MEMORY_LIMITATION row.
Copy the query behind this report The whole batch, to run and adapt yourself.

Where the data comes from

Source What it gives
sys.dm_db_column_store_row_group_physical_stats Every rowgroup: state, rows, deleted rows, size, trim reason.
sys.indexes, sys.objects, sys.schemas Names and index types.
sys.column_store_dictionaries Dictionary sizes, which is what a DICTIONARY_SIZE trim reason points at.

Messages you may see

N% of this index’s rows are in an open or closed delta rowgroup. That is rowstore: no compression and no batch mode. At query time this is not really a columnstore table.

N of M compressed rowgroups closed with BULKLOAD. The dominant trim reason, with its meaning attached, whenever it is one of the reasons that is not normal.

Nothing compressed yet: every row is still in the delta store. A REORGANIZE with COMPRESS_ALL_ROW_GROUPS moves it across without waiting for the tuple mover.

The comb draws the most recent 400 rowgroups. The grid counts all of them.

Report Why you would go there
Table Size A columnstore that is not compressing is a size problem before it is a speed problem.
Index Fragmentation Where the rowstore half of the same maintenance decision is made.
Index Usage Whether the columnstore index is being used at all.
Memory Grants and Spills Where a MEMORY_LIMITATION trim reason leads.
Plan Regressions Whether a query got slower at the point the rowgroups degraded.

Frequently asked questions

Why is my average rows per rowgroup so far below a million? Almost always the load batch size. Inserting fewer than 102,400 rows at a time sends them to the delta store, and inserting between that and 1,048,576 produces a compressed rowgroup that is short. The Trim reasons view says which of those is happening.

Should I rebuild? Usually not. REORGANIZE with COMPRESS_ALL_ROW_GROUPS handles the delta store, the small rowgroups and the deleted rows, online. Rebuild is for the cases where that has been tried and the shape is still wrong, and it wants a maintenance window.

Why is a small table showing Healthy with tiny rowgroups? Because it has fewer than 1,048,576 rows in total and cannot fill one. Judging it against the ideal would be arithmetic without meaning.

What is reclaimable and how exact is it? It is the compressed size sitting behind the deleted rows, so it is approximate. A REORGANIZE also merges small rowgroups, which usually gives back more than the figure shown.

Why do deleted rows matter if the data is gone? Because they are not gone. A columnstore delete marks the row in a delete bitmap; the row is still stored, still read on every scan, and then discarded. That is why a REORGANIZE, which physically removes them, is the fix rather than a delete.

Why does the trim reason column say nothing for some rowgroups? Open and closed delta rowgroups do not have one. They have not been compressed yet, so nothing has trimmed them.