[ENHANCEMENT (P3-Nice-to-have)]: Columnstore modification_counter underreporting warning fires after priority assignment — too late to influence scheduling

[nanoDBA]

Summary

The CCI (Columnstore Clustered Index) modification_counter underreporting warning (#28) is emitted during debug output after discovery has already assigned priorities based on those unreliable counter values. CCI tables with recent bulk loads may receive artificially low priority and be skipped entirely before the warning fires.

Current Behavior

Lines ~5816–5840: the CCI detection check (@Debug=1 only) identifies tables with columnstore indexes and emits:

"Columnstore: N table(s) with columnstore indexes — modification_counter may underreport after bulk loads"

However, at this point in execution, #stats_to_process has already been populated and filtered by @ModificationThreshold. CCI tables with a low modification_counter (common after bulk loads that bypass delta stores) will have been filtered out of the candidate list before this warning fires. The warning cannot influence the priority of already-excluded candidates.

Expected Behavior / Proposed Fix

During discovery (when populating #stats_to_process), apply a minimum priority floor for CCI tables with evidence of recent delta store activity:

-- During staging: boost CCI tables with delta store row groups
-- (high delta row count = recent bulk load that didn't flush to compressed segments yet)
UPDATE stp
SET    stp.modification_counter = GREATEST(stp.modification_counter, @ModificationThreshold)
FROM   #stats_to_process AS stp
WHERE  EXISTS (
    SELECT 1 FROM <db>.sys.column_store_row_groups csrg
    WHERE  csrg.object_id = stp.object_id
    AND    csrg.state = 1  -- OPEN delta store
    AND    csrg.row_count > 10000
);

Alternatively, add a separate CCI-aware check before the modification threshold filter that includes CCI tables with open delta stores regardless of modification_counter.

The @WarningsOut string should include CCI_DELTA_STORE_DETECTED when this condition fires (not gated on @Debug=1).

Technical Detail

modification_counter in sys.dm_db_stats_properties increments on DML that goes through delta stores. Bulk loads using TABLOCK or BATCHSIZE > 102400 bypass delta stores and go directly to compressed row groups. The modification_counter receives no credit for these rows. A CCI table can receive 100M bulk-loaded rows and show modification_counter=0, failing every threshold check. sys.dm_db_column_store_row_group_physical_stats delta store row counts are the correct staleness signal for this class of CCI bulk load.

Test Cases

• Bulk load 10M rows into a CCI table using INSERT INTO ... WITH (TABLOCK). Verify modification_counter = 0. Run sp_StatUpdate with @ModificationThreshold = 5000. Verify CCI table IS included via delta store detection.
• Verify CCI_DELTA_STORE_DETECTED appears in @WarningsOut.
• Verify no CCI tables with no delta store activity are incorrectly force-included.

References

• sys.dm_db_stats_properties — modification_counter column
• sys.dm_db_column_store_row_group_physical_stats — state column (0=INVISIBLE, 1=OPEN, 2=CLOSED, 3=COMPRESSED, 4=TOMBSTONE)
• SQL Server docs: Columnstore indexes and statistics maintenance

[nanoDBA]

Resolved in v2.3: @SkipTablesWithColumnstore + always-on warning + @Help docs.