Columnstore REORGANIZE command increases modification_counter on table statistics
Reorganizing a columnstore index can, in some cases, cause the modificationcounter on the table's statistics to increase. This is unexpected because while the underlying structure of the table is being changed, the data itself isn't being modified. This can lead to issues where SQL is automatically updating statistics the next time a query is executed against the table in cases where the modificationcounter increases a significant amount because of the REORGANIZE. This has been particularly problematic for our large fact table with 150+ billion rows and full scan statistics across 40+ columns.
Attached is a reproduction script with three examples:
• A columnstore index REORGANIZE that has no impact on row groups does not impact modificationcounter
• A columnstore index REORGANIZE that compresses an open row groups does not impact modificationcounter
• A columnstore index REORGANIZE that combines two compressed row groups does impact modification_counter
I have observed this behavior in 2016 and vNext but not tested it against other versions. The impact to the modificationcounter is different across these two versions, though in both cases the modificationcounter is increased as a result of the REORGANIZE.