[ENHANCEMENT (P3-Nice-to-have)]: Filtered statistics: modification_counter is table-wide — filtered stats may be over-prioritized when only out-of-filter rows change

[nanoDBA]

Summary

modification_counter for filtered statistics counts ALL table modifications regardless of whether the changed rows fall within the filter predicate. A filtered statistic on WHERE status = 'ACTIVE' may be repeatedly over-prioritized when only status = 'ARCHIVED' rows are modified, wasting UPDATE STATISTICS cycles on unchanged filtered distributions.

Current Behavior

sys.dm_db_stats_properties.modification_counter is table-wide for all statistics (filtered and unfiltered). sp_StatUpdate's priority calculation (lines ~4765–4791) applies the same modification threshold to all statistics without distinguishing whether modifications fall within or outside the filter predicate. The @Help text (line ~1109) documents this:

"modification_counter reflects TOTAL table modifications... For filtered statistics, the counter may be high even when the filter predicate's data distribution hasn't changed."

However, no alternative priority mechanism exists for filtered statistics. @FilteredStatsMode=PRIORITY uses @FilteredStatsStaleFactor (drift detection) but this requires historical histogram comparison, not available at discovery time.

Expected Behavior / Proposed Fix

When @FilteredStatsMode=PRIORITY, add a supplementary detection path:

1. Compare unfiltered_rows (total rows) vs the estimated rows in the filtered range using sys.dm_db_stats_histogram
2. Boost priority only when the filtered histogram's step counts show meaningful change relative to the previous update

At minimum, add to @Help:

For tables with high-velocity changes on non-filtered rows, consider using @FilteredStatsMode=PRIORITY with a high @FilteredStatsStaleFactor (e.g., 0.20) to reduce over-prioritization of filtered statistics. Alternatively, use @ExcludeStatistics to exclude specific filtered stat patterns and manage them separately.

Add FILTERED_STATS_OVER_PRIORITIZED to @WarningsOut when filtered statistics represent >30% of the candidate list and @FilteredStatsMode=INCLUDE.

Technical Detail

Auto-created filtered statistics (_WA_Sys_*) are common on partitioned and status-column tables. In a table with 10M rows and 100K active + 9.9M archived, inserting 1 row into archived increments modification_counter by 1 for the filtered WHERE status='ACTIVE' stat — which is technically correct (counter changed) but the filtered distribution is unchanged. At scale, this creates noise in the priority queue.

Test Cases

• Create filtered stat on WHERE status = 'ACTIVE'. Insert 10,000 rows with status = 'ARCHIVED'. Verify modification_counter for the filtered stat increases. Run sp_StatUpdate with @FilteredStatsMode=INCLUDE. Verify the stat is included as a candidate despite no changes in the filtered range.
• Verify @Help documents the table-wide modification_counter caveat for filtered stats.
• Run with @FilteredStatsMode=PRIORITY. Verify drift detection (not just modification_counter) drives priority for filtered stats.

References

• sys.dm_db_stats_properties — modification_counter (table-wide)
• sys.dm_db_stats_histogram — filtered histogram step inspection
• sp_StatUpdate @FilteredStatsMode parameter
• sp_StatUpdate @FilteredStatsStaleFactor parameter

[nanoDBA]

Resolved in v1.4: @FilteredStatsMode=PRIORITY with 500B ORDER BY boost and drift ratio in ExtendedInfo.