It’s beyond my capability to suggest code using sys.dm_db_column_store_row_group_physical_stats to more correctly estimate when columnstore indexes should be rebuilt. In my case I excluded columnstore indexes from rebuild actions by patching the procedure as follows.
-- Which actions are allowed?
IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) AND (@CurrentAllowPageLocks = 1 OR @CurrentIndexType = 5)
AND @CurrentIsColumnStore = 0 -- ignore columnstore index frag (wrong frag determination)
BEGIN
INSERT INTO @CurrentActionsAllowed ([Action])
VALUES ('INDEX_REORGANIZE')
END
IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)
AND @CurrentIsColumnStore = 0 -- ignore columnstore index frag (wrong frag determination)
BEGIN
INSERT INTO @CurrentActionsAllowed ([Action])
VALUES ('INDEX_REBUILD_OFFLINE')
END
Defrag information returned by sys.dm_db_index_physical_stats() in INDEX_OPTIMIZE is inaccurate for columnstore indexes: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-column-store-row-group-physical-stats-transact-sql?view=sql-server-ver15 . Thus these indexes were being rebuilt unnecessarily, often requiring significant system resources and locking users out (because ONLINE is not an option).
It’s beyond my capability to suggest code using sys.dm_db_column_store_row_group_physical_stats to more correctly estimate when columnstore indexes should be rebuilt. In my case I excluded columnstore indexes from rebuild actions by patching the procedure as follows.