v3.7.0 - QS forced-plan attribution, WAIT_CPU, budget guard, filtered-stats API restored (zks1/6aa2/sss6/o7gf/u6n7/l4ui + diag w5p2)

sp_StatUpdate 3.7.0.2026.07.03

Forced-plan failure attribution (gh-533)

Before touching any statistic, the proc snapshots force_failure_count for every forced Query Store plan per database (MANUAL-only on SQL 2022+ via plan_forcing_type, so Automatic Plan Correction noise is excluded). After the run it computes the delta:

• Rising counts emit a QS_FORCED_PLAN_FAILURE_DELTA warning naming per-database deltas.
• The END Summary XML gains a ForcedPlanFailureDelta element (omitted when zero).
• Databases whose baseline capture failed transiently are excluded from the delta, so pre-existing failures are never misattributed to the run.

You can now attribute "forced plan started failing overnight" to the stats job — or rule it out.

@QueryStore = WAIT_CPU (gh-534)

Prioritizes by CPU-category waits only (sys.query_store_wait_stats wait_category = 1), complementing WAITS (all stat-influenceable categories).

Budget-overshoot guard (gh-539)

Before each stat, estimated duration (MAX of the last 10 successful CommandLog updates — one 4-hour outlier must count, so not AVG) is checked against the remaining @TimeLimit/@StopByTime budget; stats that cannot fit are deferred instead of blowing through the maintenance window.

Denial artifact (gh-551)

A run refused with ALREADY_RUNNING now writes a durable SP_STATUPDATE_DENIED CommandLog row identifying the denied session and the lock holder (session, login time, lock acquire time). Suppressed with @LogToTable = N'N'.

Filtered-statistics API restored (gh-554)

FILTERED_DRIFT now outranks QUERY_STORE_PRIORITY in QualifyReason, and @FilteredStatsMode / @FilteredStatsStaleFactor are public parameters again — the v3.0 API collapse had absorbed them with no preset exposing EXCLUDE/ONLY/PRIORITY, leaving those modes and the FILTERED_DRIFT sort order unreachable. v3 API is now 42 input + 11 OUTPUT parameters.

Docs & polish

• @Help preset topic rewritten as a bounded override contract (gh-553).
• IO-corruption warning deduped; fresh-stat physical row counts aggregated once per object.

sp_StatUpdate_Diag 2026.07.03.1

I8/RS13 DataStatus (gh-532): the QS Performance Correlation surfaces no longer go silently empty on sparse Query Store history. Sparse I8 findings carry a machine-readable [DataStatus: AVAILABLE|INSUFFICIENT_HISTORY|UNAVAILABLE; QSCpuRuns: N; TrackedStats: N] suffix, and RS 13 returns a single [NO DATA] sentinel row (full column list, with what-to-enable guidance) instead of zero rows — including the empty-CommandLog first run and @SingleResultSet mode.

Since the last published release (v3.5.5)

Interim tagged versions, one line each — see README Version History for detail:

• v3.6.0 — telemetry contract batch: WarningsCodes in END Summary XML, COMPLETED_WITH_SKIPPED_DBS recorded correctly, CONSECUTIVE_FAILURES_ELEVATED, AZURE_SQL_EDGE, per-stat ETA suffix.
• v3.5.9 — fresh (never-computed) statistics finally qualify (NEVER_UPDATED); new @AbortOnIntegrityError; @QueryStore = WAITS category fix.
• v3.5.8 — four early-return paths now write real END rows (no more false KILLED criticals); FINGERPRINT_CONFLICT refusal was dead code, now enforced.
• v3.5.7 — parallel queue leadership re-claim fix (recycled SPIDs / idle pooled leaders caused silent no-op runs).
• v3.5.6 — parallel fingerprint validation for queue joiners; CONTEXT_INFO restored on early returns.

Verification

Release gate on SQL Server 2025 (RTM-CU2): filtered-stats pack 10/10, denial+guards 4/4, QS forced-baseline/WAIT_CPU 6/6, fresh-stat 4/4, warnings contract 5/5, V3Fixes 11/11, QS enrichment safety 7/8 (+1 version-gated skip), diagnostic suite 254/254.

v3.5.5 - Re-entrancy guard ALREADY_RUNNING fix (i7by)

sp_StatUpdate.sql (v3.5.5)

sp_StatUpdate-i7by (false ALREADY_RUNNING after validation-error exit): when a caller wraps EXEC sp_StatUpdate in TRY/CATCH, the CATCH intercepts the proc's severity-16 validation RAISERROR before the proc reaches its own DELETE cleanup, leaving a stale row in dbo.StatUpdateLock. A second call in the same session then saw the stale row, found the holding session "alive" in sys.dm_exec_sessions (it IS the caller), and falsely raised ALREADY_RUNNING. Three-part fix in the re-entrancy guard:

• S (primary): a lock row held by the caller's own session (SessionID = @@SPID) is unconditionally stale - the prior call has by definition ended, since we are now executing in that same session. Reclaim without probing the DMVs (the probes would match our own active request).
• A: for DIFFERENT sessions, the dead-holder reclaim now also fires when the session has no active request AND last_request_end_time is more than 60s in the past (catches async TCP teardown where a SPID lingers briefly in sys.dm_exec_sessions after client disconnect).
• D: TTL guard - any lock row with AcquiredAt older than 8 hours reclaims unconditionally regardless of session state.

Genuine concurrency is unaffected: a different session with an active sp_StatUpdate request still blocks as intended.

sp_StatUpdate_Diag.sql (2026.06.08.2)

sp_StatUpdate-g4c0: registers the QS_PARALLEL_UNRELIABLE category (introduced in 2026.06.08.1) in the diagnostic checks catalog as I9b / INFO. Cosmetic, no behavior change.

Test gate (all PASS on SQL 2019 / 2022 / 2025)

Suite	Result
V3Core	16/16
V3Fixes	10/10
V3Coverage	11/11
Diag (StatUpdateDiag)	221/221

The diag suite gained an 8-assertion "W5 Parallel-QS Scenario" section (sp_StatUpdate-hr21) that exercises the new 2026.06.08.x branches end to end: QS_PARALLEL_UNRELIABLE INFO emission, QS_NOT_EFFECTIVE suppression, I10 QS retention with parallel-artifact rationale, TimeLimit jitter normalization (21598/21599 to 21600), and the W9-gated @CriticalTables hint. Additional targeted verification: the i7by repro (TRY/CATCH validation error followed by same-session calls) now completes with NO_QUALIFYING_STATS instead of ALREADY_RUNNING, and a fresh-stat case (3 consecutive @Execute = Y runs in one session, 8 successful UPDATE STATISTICS CommandLog rows) leaves dbo.StatUpdateLock empty after each run.

v3.5.4 - Parallel QS ordering fix + first-time FULLSCAN cap (mhje, mknv)

sp_StatUpdate.sql (v3.5.4)

sp_StatUpdate-mhje (parallel QS ordering): parallel TablePriority population had gh-505 LPT (COALESCE(est_total_seconds, 0) DESC) as the PRIMARY sort and min_priority ASC (the user-intent rank that embeds @SortOrder = QUERY_STORE) as the fallback. On mature fleets with CommandLog history (3+ runs per table), LPT silently overrode QS prioritization: diag RS12 evidence showed QS-top stats landing at ProcessingPosition 285-296 instead of 1-10 across a 11-server fleet. The ORDER BY is now swapped: min_priority is PRIMARY and LPT acts as a tiebreaker within priority bands, preserving the gh-505 wall-clock benefit without inverting user intent.

sp_StatUpdate-mknv (first-time FULLSCAN cap): new @FirstTimeFullScanCapRows bigint = NULL parameter (NULL = disabled, non-breaking). When set, stats with row count above the threshold AND no successful CommandLog entry within retention get their FULLSCAN capped to a sampled rate using the same CEILING(10M / row_count * 100) formula adaptive sampling uses (minimum 1%). The @CriticalSamplePercent override still wins. Addresses the fleet pattern where auto-created _WA_Sys stats on 770M-15.9B row tables ran ~3085s unbounded FULLSCANs on first encounter, before adaptive sampling had any history to consult.

sp_StatUpdate_Diag.sql (2026.06.08.1)

sp_StatUpdate-6x80 (W5/I10 QS-parallel refinements):

• W5 is now parallel-aware: when ALL QS-priority runs use @StatsInParallel = Y and QS CPU data IS observed in stat updates, the zero-QSPlanCount branch emits a softer INFO QS_PARALLEL_UNRELIABLE instead of the QS_NOT_EFFECTIVE WARNING (per-worker ProcessingPosition counters make the original signal unreliable in parallel mode).
• I10 RECOMMENDED_CONFIG only drops @QueryStore and reverts @SortOrder on genuine QS absence, not on the parallel artifact.
• I10 TimeLimit jitter normalization: when the spread across recent non-killed runs is under 300s (typical of @StopByTime-derived values like 21598/21599), the recommended call rounds to the nearest minute (21600).
• I10 appends a commented @CriticalTables suggestion listing the top contended tables when W9 LOCK_TIMEOUT_INEFFECTIVE has fired.

Test gate

Compile + targeted behavior smoke (cap fires, WITH SAMPLE 10 PERCENT replaces FULLSCAN on a 6.6M-row no-history stat) verified on SQL 2019 / 2022 / 2025 at release time. The full regression matrix was run at v3.5.5, which contains these changes unmodified - see the v3.5.5 release for the suite table.

v3.5.3 - @total_stats Msg 208 fix on non-parallel runs (sp_StatUpdate-isa2)

sp_StatUpdate.sql (v3.5.3)

sp_StatUpdate-isa2: @total_stats was initialized by a CASE inside a multi-variable DECLARE whose parallel branch reads the optional dbo.QueueStatistic table. A CASE in a DECLARE is a single always-compiled statement, so SQL Server name-resolved the QueueStatistic branch even on non-parallel runs and failed with Msg 208 when the table was absent (it is auto-created only when @StatsInParallel = Y). @total_stats is now assigned via IF/ELSE so the QueueStatistic reference is only resolved in parallel mode where the table exists.

This affected any non-parallel run on an instance where dbo.QueueStatistic had never been created (the common case). The bug was masked on long-lived test containers where parallel mode had previously run and auto-created the table.

Test gate (all PASS on SQL 2019 / 2022 / 2025)

Suite	Result
ProcCompiles	PASS
V3Core	16/16
V3Fixes	10/10
V3Coverage	11/11
V3Extended	16/16
Diag (StatUpdateDiag)	213/213
Diag (ChecksCoverage)	11 PASS / 0 FAIL / 1 SKIP

v3.5.2 - Parallel-run false KILLED + completion% fixes (trla/772k/3j5l)

Three parallel-mode false-positive fixes across the proc and the diagnostic engine.

sp_StatUpdate.sql (v3.5.2)

• sp_StatUpdate-trla: orphaned-run cleanup now applies a lower bound to the orphan START candidate hunt, considering only STARTs within the same window as #orphan_end_labels (minus @i_orphaned_run_threshold_hours + 24h). A START older than that window whose real END also predated the window was not found in #orphan_end_labels and received a fabricated KILLED end, falsely flagging long-completed parallel runs as killed.

sp_StatUpdate_Diag.sql (2026.05.28.1)

• sp_StatUpdate-772k: #runs dedup tiebreaker is now ORDER BY StartTime DESC, IsKilled ASC, EndTime DESC. The prior EndTime DESC ordering could pick a synthetic KILLED orphan-cleanup END over the real END (the KILLED row often has a later EndTime), falsely flagging completed parallel runs as killed. IsKilled ASC guarantees the real END (IsKilled = 0) wins.
• sp_StatUpdate-3j5l: completion_pct now returns 100.0 when StopReason IN (COMPLETED, PARALLEL_COMPLETE). In parallel mode StatsFound is the whole-queue total while StatsProcessed is this worker's slice, so the raw ratio understated completion and dragged down the 40%-weighted run-health grade.

Tested on SQL Server 2019, 2022, and 2025.

v3.5.1 - Failed stats CommandLog logging fix (gh-515)

Bug fix: failed UPDATE STATISTICS rows now traceable in CommandLog

Reported by: @nanoDBA in #515

What was broken

When sp_StatUpdate hit an error during UPDATE STATISTICS, the per-stat CATCH branch did write a CommandLog row (via the existing two-phase pre-exec INSERT + post-exec UPDATE pattern), but the failure UPDATE only set EndTime / ErrorNumber / ErrorMessage -- it left ExtendedInfo NULL.

Consequences:

• Failure rows had no RunLabel (RunLabel is denormalized into ExtendedInfo XML, not a column)
• Filtering CommandLog by run label + ErrorNumber <> 0 returned zero rows
• sp_StatUpdate_Diag's C2 (REPEATED_FAILURES) check extracts RunLabel from XML and silently missed every failure -- post-incident debugging was impossible from CommandLog alone, only the aggregate StatsFailed count survived

What changed

The real-failure ELSE branch in the per-stat CATCH (@stats_failed += 1 path) now builds the same ExtendedInfo XML schema as the success path -- including RunLabel, ObjectId, StatsId, ModificationCounter, RowCount, PageCount, all QS metrics, QualifyReason, Mode, EffectiveSamplePct, SampleSource, Version, etc. -- and the failure UPDATE now sets ExtendedInfo.

Wrapped in TRY/CATCH so a XML build error falls back to NULL ExtendedInfo without breaking the failure UPDATE.

TOCTOU rows (errors 208, 15009, 2767 -- object/stat dropped between discovery and exec) intentionally keep NULL ExtendedInfo so they don't aggregate as real failures in diag C2. These are tracked separately via @stats_toctou per the existing #222 design.

Verification

• End-to-end: synthesized 4 failure rows mirroring the post-fix CATCH XML schema, ran sp_StatUpdate_Diag -- C2 surfaces Statistic fails consistently: gh515_synth.dbo.BigFact.IX_BigFact_col1 -- Failed in 4 run(s). Errors: 1222
• Test suites on SQL 2022 CU24: V3Core 16/16, V3Extended 16/16, V3Fixes 10/10, V3Coverage 11/11 (including new T80 regression test verifying RunLabel round-trips through ExtendedInfo XML and diag's GROUP BY/HAVING contract finds the failures)
• Note: SQL 2019 / SQL 2025 containers were not reachable on the test host at release time; XML construction uses standard FOR XML PATH which is identical across SQL 2017+ and the change is non-version-gated

Commit

92e9e21 -- v3.5.1: failed stats now write CommandLog ExtendedInfo with RunLabel (gh-515)

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