I'm using [vColumnstoreDensity] to monitor the health of my columnstore indexes.

I've noticed that I've got one index that looks strange.

As far as my understanding goes, the columnstore index will fill a rowgroup before starting the next rowgroup. For best index performance, fuller row groups are better.

Having said all of this, I have one table that I'm having trouble understanding. Even after an index rebuild using the largest resource class (to give the maximum amount of memory possible to the build process) the [vColumnstoreDensity] view still shows this index as being spread across many partially full rowgroups.












This is a clustered columnstore index.

  • @JohnEisbrener Sorry! It's a clustered columnstore index.
    – Neil P
    Jun 19, 2017 at 15:07
  • Did you follow the recommended process of reorging the index twice, as outlined here? e.g. ALTER INDEX CCI_name ON Table REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) followed by ALTER INDEX CCI_name ON Table REORGANIZE Jun 19, 2017 at 15:15

1 Answer 1


There is an Extended Event that fires when a columnstore group is being 'cut', I think the event is column_store_index_build_process_segment. This event will have a 'trim' reason, and you should look for two possible trim causes:

  • memory (unlikely for your case), if the index build does not have enough memory to build a segment.
  • dictionary size (the likely cause), this happens if the dictionaries used to encode the data in a column segment reach the maximum size (16Mb).

Of course, to capture this event you need to set up an XE session during the index build (the linked article shows how).

You could also look at the post-build artifacts, specifically at the dictionaries, and see if the secondary dictionaries associated with the small segment are already at full size (16Mb). This would indicate the likely cause of trim to be full dictionary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.