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.

v3.0 — Preset-first API

What's New in v3.0

Preset-first API

• 33 public parameters (was 58 in v2) -- simpler, less error-prone
• 30 v2 params absorbed into @i_ internal variables driven by 5 presets: DEFAULT, NIGHTLY, WEEKLY_FULL, OLTP_LIGHT, WAREHOUSE
• Explicit params still override preset values

Queue-table coordination (replaces applock)

• Entry re-entrancy guard now uses dbo.StatUpdateLock row-level mutex instead of sp_getapplock
• Dead sessions auto-reclaim via (SessionID, LoginTime) liveness tuple -- no more ALREADY_RUNNING cascades when sessions die
• dbo.QueueStatistic gains ClaimLoginTime column for SPID-reuse safety in parallel mode (failover, restart)

Performance

• WAITS metric: O(N+M) pre-aggregated CTE replaces O(N*M) correlated subquery in Phase 6 QS enrichment
• FORMAT() replaced with CONVERT() in process loop (CLR overhead eliminated)

Safety

• Log-shipping STANDBY databases auto-excluded from discovery
• CDC-tracked tables emit FULLSCAN warning in debug mode
• MAX_GRANT_PERCENT hint no longer incorrectly gated on MAXDOP version flag

Diagnostic tool (sp_StatUpdate_Diag)

• I10 RECOMMENDED_CONFIG generates v3-style EXEC calls
• All ExampleCalls updated for v3 parameter names
• Version bumped to 2026.04.11.1

Breaking Changes

See V3 Migration Guide for the complete param migration table.

Key changes:

• 25 parameters removed (absorbed into presets)
• @QueryStorePriority + @QueryStoreMetric merged into @QueryStore
• @DaysStaleThreshold + @HoursStaleThreshold merged into @StaleHours
• @TargetNorecompute default changed from Y to BOTH

Test Results

Suite	SQL 2019	SQL 2022	SQL 2025
Compile	PASS	PASS	PASS
V3Extended (16 tests)	14P + 2I	14P + 2I	14P + 2I
V3Fixes (10 tests)	10P	10P	10P
V3Coverage (10 tests)	10P	10P	10P
V3QAGaps (16 tests)	16P	16P	16P
V3QAGaps2 (13 tests)	-	13P	-

🤖 Generated with Claude Code

v2.37 — fix: WAITS enrichment unbounded XML parsing

Critical fix for QS-enabled runs (#371, #372)

What happened

The WaitPlanTableRefs CTE (added in v2.35 for @QueryStoreMetric = 'WAITS') ran on every QS-enabled run regardless of @QueryStoreMetric, performing unbounded XML plan parsing against sys.query_store_plan. On databases with large Query Store catalogs, this caused 6+ hour discovery phases.

Root cause

1. No metric gate: WAITS enrichment checked only IF OBJECT_ID(N'sys.query_store_wait_stats') IS NOT NULL -- ran on SQL 2017+ regardless of whether @QueryStoreMetric = 'WAITS'
2. No TopPlans limit: Inner subquery scanned ALL plans with wait categories (3, 12, 15) without applying @QueryStoreTopPlans
3. No time-window filter: Did not filter by @QueryStoreRecentHours

Fix

• Gate WAITS enrichment on @QueryStoreMetric = 'WAITS' only
• Apply TOP (@QueryStoreTopPlans) ranked by total wait time to inner subquery
• Add @QueryStoreRecentHours time-window filter
• Both staged and legacy discovery paths fixed
• Phase 6 debug message now gated on QS enabled (#372)

Who is affected

Anyone running with @QueryStorePriority = 'Y' on SQL 2017+ (regardless of @QueryStoreMetric value). The fix eliminates the WAITS XML parsing overhead entirely for non-WAITS metrics.

New tests

• tests/Test-QSEnrichmentSafety.ps1 -- 8 tests: Phase 6 timing guards, metric-gating, TopPlans enforcement, all 10 metric values compile
• 3 Q-series tests added to tests/Test-AllVersions.ps1
• Tested on SQL 2019, 2022, 2025 -- all pass