Columnstore Index maintenance and finding fragmentation We are using clustered columnstore indexes and partitions in SQL Server 2014. Some tables have a lot of updates and we find ourselves with a lot of deleted rows for some partitions. In our tests, a rebuild reduces the table size to a third and improves 10% performance.

Besides checking sys.column_store_row_groups for deleted_rows, is there any other indicator for columnstore indexes fragmentation we can use to identify which partitions/tables should be rebuilt?
My production experience with columnstore indexes is on SQL Server 2016 and later versions but I believe everything in this answer also applies to SQL Server 2014. The simplest answer is that you can look at the ratio of row count and space used by each partition if you prefer to not use the sys.column_store_row_groups dmv. The more complicated answer is that there are many different types of possible fragmentation for a columnstore index. The most important ones to you depend on your data and workload.

Compressed deleted rows - this is the one that you mentioned in your question. The deleted rows take up space and serve no useful purpose.

Rows in delta rowgroups - these are rows that haven't been compressed (yet) - if you end up with too many you can see an impact to query performance.

Compressed rowgroups not of the maximum size (1048576 rows) - for a variety of reasons you can end up with compressed rowgroups with a row count less than 1048576. Microsoft claims that tables typically get the best compression if the rowgroup row count is maximized. In my experience this varies depending on the data. I see no measurable difference with our data model.

Misaligned segments - you may be loading your data in an order to get highly selective rowgroup elimination on key columns. If data ends up out of the preferred order, such as after a maintenance operation, then it can be considered to be fragmented.