Skip to content

v3.5.0 - @CriticalTables feature (gh-508)

Choose a tag to compare

View all tags
@nanoDBA nanoDBA released this 23 Apr 20:32
· 59 commits to main since this release
v3.5.0
0b43e7a

@CriticalTables feature -- per-table sample rate override with optional priority boost

Addresses plan instability observed when switching to Query Store / CPU-based stats ordering on large fact tables hit by procedure-scoped recompile workloads. Problem had two parts:

  1. QS ordering changed which stats were fresh at any moment -- creating inconsistent cardinality estimates between joined tables
  2. Auto-sample on 100M+ row tables -- produced inadequate histograms for critical workloads

Both are fixable without splitting maintenance into two jobs.

Three new parameters

Parameter Type Default Purpose
@CriticalTables nvarchar(max) NULL Comma-delimited table patterns (supports %)
@CriticalSamplePercent tinyint NULL 1-100 (100=FULLSCAN) for critical tables only
@CriticalTablesFirst nchar(1) N'N' Y = process critical tables before everything else

Example usage

-- Critical tables get FULLSCAN and run first; everything else uses QS/CPU ordering
EXEC dbo.sp_StatUpdate
    @Databases             = N'YourDatabase',
    @Preset                = N'NIGHTLY',
    @CriticalTables        = N'dbo.FactSales, dbo.Bridge%',
    @CriticalSamplePercent = 100,
    @CriticalTablesFirst   = N'Y';

Behaviors

  • Sample override: Critical-table stats get the forced sample rate; other tables use normal @StatisticsSample / preset defaults.
  • Priority boost: @CriticalTablesFirst = 'Y' adds is_critical DESC before the normal sort, so critical tables are always processed first regardless of @SortOrder.
  • Auto-persist: PERSIST_SAMPLE_PERCENT = ON is automatically added for critical tables when @CriticalSamplePercent is set, so SQL Server's auto-update between runs respects the rate.
  • Observability: Per-stat ExtendedInfo XML includes IsCritical and CriticalSampleOverride. Run-level XML logs the three parameters. Parameter fingerprint updated for parallel-mode compatibility.

Validation

  • @CriticalSamplePercent without @CriticalTables -> error
  • @CriticalTablesFirst = 'Y' without @CriticalTables -> error
  • @CriticalSamplePercent outside 1-100 -> error
  • @CriticalTablesFirst values other than Y/N -> error

Interaction with existing features

  • @ExcludeTables wins over @CriticalTables (excluded tables are never processed, even if marked critical)
  • @LongRunningThresholdMinutes wins over @CriticalSamplePercent (adaptive sampling takes precedence -- a historically slow stat needs a lower sample, not a higher one)
  • Works with all modes: DISCOVERY, DIRECT_TABLE (parallel mop-up), DIRECT_STRING (@Statistics), serial + parallel mop-up

Tests

  • Existing regression suites: 90/90 PASS on SQL 2019 / 2022 / 2025 (V3Extended, V3Fixes, V3Coverage)
  • New tests/Test-CriticalTables.ps1: 12 tests covering pattern matching, sample override, priority ordering, validation, exclusion interaction, and ExtendedInfo content

Issues closed

gh-508 (epic), gh-509, gh-510, gh-511, gh-512, gh-513, gh-514

Assets 2
Loading