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

@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

v3.4.1 - @LockTimeout public parameter (gh-508)

What's new

Promotes `@LockTimeout` to a public parameter, allowing DBAs to override the preset-driven lock timeout without editing preset definitions.

```sql
EXEC dbo.sp_StatUpdate
@databases = N'USER_DATABASES',
@LockTimeout = 300; -- 5 minute wait; NULL = preset decides, -1 = forever, 0 = no wait
```

Why

The 2026-04-22 diagnostic run surfaced 7x Error 1222 (lock request timeout exceeded) on a single high-volume server. Previously `@i_lock_timeout` was only settable via presets (OLTP_LIGHT sets 10s; all others NULL). Making `@LockTimeout` a public parameter lets fleet-wide Agent jobs apply a uniform lock-wait budget.

Behavior

Follows the same public-override pattern as `@ModificationThreshold`:

Value	Meaning
`NULL` (default)	Preset decides (no override)
`-1`	`SET LOCK_TIMEOUT -1` -- wait forever
`0`	`SET LOCK_TIMEOUT 0` -- no wait, fail immediately on conflict
`N > 0`	`SET LOCK_TIMEOUT N` seconds

Testing

126/126 across SQL 2019, 2022, 2025 (compile + V3Extended + V3Fixes + V3Coverage).

Upgrade

Drop-in replacement for v3.4.0. No schema changes. No breaking changes.

---

Full changelog: v3.4.0...v3.4.1

v3.4.0 - CommandLog intelligence + parallel LPT scheduling (gh-498..507)

What's new

v3.4.0 adds three new discovery phases that mine existing CommandLog data for smarter, faster statistics maintenance -- no new tables, no new schema, no new DDL.

CommandLog delta qualification (gh-502)

Phase 3B queries CommandLog for the last known ModificationCounter per stat and computes the delta (net new modifications since last update). Phase 4 uses delta instead of raw counter for threshold comparison.

• Stats with delta = 0 are already current and skip qualification entirely -- no wasted work
• Stats with no CommandLog history fall back to raw counter (no regression)
• Debug output: Phase 3B (CommandLog delta): N stats enriched, M with delta=0

QS score cache (gh-503)

Phase 5B fetches cached QSPriorityBoost, QSTotalCpuMs, and QSLastExecution from CommandLog. Stats with fresh cached scores (within @i_qs_recent_hours) skip the expensive Phase 6 QS DMV joins entirely.

• On the second nightly run, Phase 6 becomes near-zero for stable workloads
• Debug output: Phase 5B (QS cache): N of M stats have fresh cached QS scores

Modification velocity sort order (gh-507)

New @SortOrder = 'MODIFICATION_VELOCITY' ranks stats by mods/hour computed from CommandLog delta and elapsed time. High-velocity stats (bulk loads, truncate-and-reload) sort ahead of slow-accumulating ones with the same raw counter.

EXEC dbo.sp_StatUpdate
    @Databases = N'USER_DATABASES',
    @SortOrder = N'MODIFICATION_VELOCITY';

Parallel LPT scheduling (gh-505)

Queue population now uses longest-processing-time-first (LPT) scheduling when CommandLog has >= 3 historical runs per table. Estimates total seconds per table (avg_seconds_per_stat * stats_count) and sorts longest-first to minimize makespan. Prevents worker starvation where one worker gets stuck on a slow table at the end while others sit idle.

Diag: perpetually skipped stats (gh-504)

New W13 PERPETUALLY_SKIPPED warning in sp_StatUpdate_Diag detects stats discovered but never updated across N consecutive runs due to time limits. Reports average last ProcessingPosition vs total discovered and recommends @SortOrder = MODIFICATION_VELOCITY or increased @TimeLimit.

Also closed

Issue	Status
gh-498	Already in v3.3.5 (parallel forced plan check dedup)
gh-499	Already in v3.3.5 (parallel progress global totals)
gh-500	Already in v3.3.5 (forced plan check rewrite)
gh-501	Superseded by gh-503 (CommandLog approach eliminates need for new table)
gh-506	Deferred (per-stat sample derivation already fast)

Test results

Suite	SQL 2019	SQL 2022	SQL 2025
Compile	6/6	6/6	6/6
V3Extended	16/16	16/16	16/16
V3Fixes	10/10	10/10	10/10
V3Coverage	10/10	10/10	10/10
Main total	42/42	42/42	42/42
Diag	213/213	213/213	206/211*

*5 SQL 2025 failures are pre-existing data-dependent (accumulated killed run records, QS test data) -- not related to this release.

Upgrade

Drop-in replacement for v3.3.x. No schema changes. No new parameters. Recompile-safe.

The new phases activate automatically when dbo.CommandLog exists and has historical UPDATE_STATISTICS entries. First run after upgrade behaves identically to v3.3.x (no history yet). Benefits compound from the second run onward.

---

Full changelog: v3.3.4...v3.4.0

v3.3.4 - AG-secondary parallel pre-flight regression fix (gh-497)

Bug fix

gh-497 / gh-428 follow-up: Removed server-level AG-secondary hard-error from parallel pre-flight.

Symptom

Since v3.3.0, `@StatsInParallel = 'Y'` failed with severity-16 ERROR whenever the server hosted any AG-secondary replica -- even when non-AG `USER_DATABASES` were also present on the same instance. This regressed v2 behavior of silently skipping unreadable AG databases and continuing against the remainder.

Root cause

gh-428 Check 1 gated on `@is_ag_secondary_server = 1` (server-level state) instead of on whether any eligible non-AG database remained after DB-parse filtering. Region 04-DB-PARSE already excludes AG-secondary databases from `@tmpDatabases` and short-circuits when nothing eligible remains, making Check 1 either dead code or a regression depending on the deployment topology.

Fix

Removed the redundant Check 1 block in the parallel pre-flight. Kept Check 2 (orphan `QueueStatistic` row backlog warning).

Upgrade

Drop-in replacement for v3.3.0 / v3.3.1 / v3.3.2 / v3.3.3. No schema changes. Recompile-safe.

Version note

Release tags v3.3.2 and v3.3.3 were used for diag-only releases while `sp_StatUpdate.sql` stayed at v3.3.1 internally. v3.3.4 is the first aggregate release where the release tag matches the internal `@procedure_version` again.

---

Full changelog: v3.3.3...v3.3.4

v3.3.3 - Diag C1 Evidence truncation fix

Fix

`sp_StatUpdate_Diag` aborted on servers with many killed runs with:

```
Msg 2628, Level 16, State 1
String or binary data would be truncated... column 'Evidence'.
Truncated value: 'Run dates: 2026-04-16 23:00:05, ...'
```

C1 KILLED_RUNS concatenated every killed-run StartTime via STRING_AGG into the `Evidence nvarchar(2000)` column. With 90+ killed runs the 20-char-per-timestamp list overflows.

Change (diag v2026.04.20.2)

Capped to TOP 10 most recent StartTimes. Prefix switches to `'Run dates (10 most recent of N): ...'` when more than 10 exist so the reader knows the list is truncated.

Closes #496.

v3.3.2 - Diag PK_runs fix

Fix

`sp_StatUpdate_Diag` aborted with:

```
Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'PK_runs'.
Cannot insert duplicate key in object 'dbo.#runs'.
```

Root cause: the `INSERT INTO #runs` LEFT JOINs `SP_STATUPDATE_START` to `SP_STATUPDATE_END` on `RunLabel`. `CommandLog` can contain multiple ENDs per RunLabel (orphan-cleanup KILLED record from gh-425 plus a later real END) or duplicate STARTs (same-second retries). The existing dedup at line ~1373 would have handled this, but the PK constraint blocked the INSERT before dedup could run.

Change (diag v2026.04.20.1)

• Removed `CONSTRAINT PK_runs` from `#runs` `CREATE TABLE`.
• Added `UX_runs_RunLabel` unique index after the dedup CTE -- same uniqueness guarantee and index benefit for downstream joins, just enforced post-dedup.
• Dedup `ORDER BY` now `StartTime DESC, EndTime DESC` so a real END is preferred over a KILLED orphan record when both exist for the same RunLabel.

Bundled

Also includes the v3.3.1 proc-deploy fix for `dbo.QueueStatistic` schema upgrade (pre-`ALTER PROCEDURE` migration batches for `ClaimLoginTime` and `LastStatCompletedAt`).

Upgrade path

Drop-in replacement. Re-deploy `sp_StatUpdate_Diag.sql` and `sp_StatUpdate.sql` -- both are idempotent.

v3.3.1 - Deploy fix: QueueStatistic schema migration

Deploy fix

ALTER PROCEDURE body has static (non-dynamic-SQL) references to ClaimLoginTime (bd -h9a) and LastStatCompletedAt (v2.3). Upgrades from v2.26 or earlier failed compilation with Invalid column name 'ClaimLoginTime' because in-proc runtime migrations fire only when @StatsInParallel = Y -- too late for compile-time column binding.

Fix

Added two batches between the stub CREATE and the ALTER PROCEDURE that ALTER TABLE ADD each missing column if the existing dbo.QueueStatistic table lacks it.

• Idempotent; no-op on fresh installs and already-upgraded schemas.
• Each column in its own GO batch so a failure on one doesn't block the other.
• In-proc runtime migrations retained as belt-and-suspenders.

Fresh installs are unaffected -- the table is created with the correct schema at first parallel-mode run.

Upgrade path

Drop-in replacement. No parameter changes, no behavior changes. Re-deploy sp_StatUpdate.sql over any v2.26+ installation and the schema self-heals before compilation.

v3.3.0 - 5 additive enhancements

Additive enhancements (5 issues)

• gh-423: @JobName input param -- optional caller tag written to CONTEXT_INFO on entry, restored on exit. Visible in Extended Events sessions and sys.dm_exec_requests.
• gh-425: @DeadWorkerTimeoutMinutes NULL coercion (30 min floor) + cross-QueueID stale-row sweep on parallel leader path. Prevents phantom worker inflation from killed prior runs.
• gh-426: @WarningsCodesOut OUTPUT param -- pipe-delimited stable code tokens (e.g. AG_REDO_ELEVATED|TEMPDB_LOW) paired with human-readable @WarningsOut for programmatic parsing.
• gh-427: @SkipTablesWithNCCI / @SkipTablesWithCCI params replace the single internal columnstore skip flag with per-type controls. Default: skip NCCI (Y), update CCI (N).
• gh-428: Parallel pre-flight -- AG-secondary guard (hard block) and orphan-row backlog warning (>100 rows) run before queue init when @StatsInParallel = Y.

Post-landing fix

SET CONTEXT_INFO rejects NULL. Both restore sites (early-return and final exit) now guard with IF NULL → SET CONTEXT_INFO 0x to support callers with no prior context.

Test results

Compile 3/3, V3Extended 48/48, V3Fixes 30/30, V3Coverage 30/30 across SQL 2019/2022/2025. Total: 111/111 PASS.

Research outcome (#424)

UPDATE STATISTICS is fully atomic when externally KILLed on SQL 2019/2022/2025 -- modification_counter, last_updated, STATS_DATE(), and rows all remain unchanged. @StopByTime is a launch-gate (not a KILL), so stats remain re-pickable after external kills. No code change required.

v3.2.2 - Test coverage epic + quality refactors

sp_StatUpdate v3.2.2.2026.04.17 + sp_StatUpdate_Diag 2026.04.18.1

Quality refactors (sp_StatUpdate.sql)

• gh-461: @parameters_string fingerprint now built once (was duplicated across two regions); ensures parallel-worker fingerprint comparison matches leader's exact string.
• gh-462: Mop-up discovery WHERE clause (88 lines) extracted to @mop_up_where_sql variable shared by parallel-leader and serial paths.
• gh-465: Empty 37-column sentinel SELECT extracted to @empty_disc_select variable referenced by all 6 staged-discovery bailout paths (phases 1-6).

Diag fix (sp_StatUpdate_Diag.sql)

• gh-c53: Parallel-mode false positives eliminated. W4 (overlap), W3 (backlog), C3 (time-limit exhaustion), and COMPLETION grade no longer fire when multi-worker parallel runs are correctly aggregated into a single logical run.

Test coverage epic (gh-491)

15 new test scripts closing all 6 P1 + 6 P2 + 3 P3 gap issues from the 2026-04-17 review:

Severity	Issue	Coverage
P1	H1/H2/H4	11 stop-reason assertions across 8 stop reasons
P1	H3	6 concurrent parallel-execution tests
P1	H5	Diag checks W8-W10, C5, I11-I14 assertions
P1	H6	@sortorder QUERY_STORE/FILTERED_DRIFT/AUTO_CREATED/ROWS (4 tests)
P2	M1	36 OUTPUT-param assertions across early-return paths
P2	M2	QS OFF / READ_ONLY database (7 tests)
P2	M3	@MopUpPass=Y end-to-end including parallel (16 tests)
P2	M4	@statistics multi-stat CSV (24 tests)
P2	M5	@WhatIfOutputTable NORECOMPUTE preservation (11 tests)
P2	M6	@ExcludeTables LIKE wildcards (16 tests)
P3	L1	Partitioned table with incremental stats
P3	L2	SQL 2025 DMV column token-gating on SQL 2019 (32 tests)
P3	L3	@Help content regression (69 tests)

Verification

Full Run-FullSuite matrix on SQL 2019 / 2022 / 2025:

• Compile: OK
• V3Core: 48/48
• V3Fixes: 30/30
• V3Coverage: 30/30

Files

• sp_StatUpdate.sql - v3.2.2.2026.04.17
• sp_StatUpdate_Diag.sql - 2026.04.18.1

v3.2.1 - 2026-04-17 review (41 issues across 5 phases)

v3.2.1.2026.04.17 - 2026-04-17 Code Review Batch

Comprehensive review work covering 41 issues across 5 phases. Full v3 test suite passes on SQL 2019 / 2022 / 2025 (Compile OK / V3Core 48/48 / V3Fixes 30/30 / V3Coverage 30/30).

Phase 5 - Bug fix (gh-492)

• DIRECT_STRING discovery path (@Statistics param) now honors @TargetNorecompute and @ExcludeStatistics filters, matching staged discovery Phase 1 behavior.

Phase 4 - Quality / perf batch (gh-460, 463, 464, 466-470)

• Phase 6 plan feedback query bounded by @i_qs_recent_hours + @i_qs_top_plans
• 5 COUNT_BIG(*) scans of #stats_to_process collapsed to one SUM(CASE)
• Orphan CommandLog cleanup materializes END RunLabels into a temp table
• MAX_GRANT_PERCENT hint token-substituted (no fragile literal replace)
• sys.partitions count cached per (database, object_id)
• Six per-database warning checks now surface errors via @WarningsOut + debug
• Threshold-logic explanation gated behind @Debug = 1
• Per-database warning block short-circuits when DB has zero stats

Phase 1 - Correctness batch (gh-451..459)

• Parallel early-return paths (FINGERPRINT_CONFLICT, MAX_WORKERS, QUEUE_INIT_ERROR) now set all OUTPUT params and return summary
• Second LOCK_TIMEOUT restore after forced-plan check
• @QueryStore = AVG_CPU sorts by average (not total)
• @parameter_fingerprint expanded + additional correctness fixes

Phase 2 / 3 - Diag correctness (gh-471..480)

• 10 correctness fixes in sp_StatUpdate_Diag.sql 2026.04.17.1.

Deferred

• gh-461 (param fingerprint dedup), gh-462 (mop-up WHERE dedup), gh-465 (5x empty SELECT dedup), gh-493 (test-side false positive) - larger refactors / non-proc work.