-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathsp_StatUpdate.sql
More file actions
10037 lines (9314 loc) · 473 KB
/
sp_StatUpdate.sql
File metadata and controls
10037 lines (9314 loc) · 473 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/*
sp_StatUpdate - Priority-Based Statistics Maintenance
Copyright (c) 2026 Community Contribution
https://github.com/nanoDBA/sp_StatUpdate
Purpose: Update statistics with DMV-based priority ordering and time limits.
Refreshes stale stats including those with NORECOMPUTE (flag preserved).
Based on: Ola Hallengren's IndexOptimize (MIT License)
https://ola.hallengren.com
License: MIT License
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
Version: 2.19.2026.03.09 (Major.Minor.YYYY.MM.DD)
- Version logged to CommandLog ExtendedInfo on each run
- Query: ExtendedInfo.value('(/Parameters/Version)[1]', 'nvarchar(20)')
History: 2.15.2026.03.05 - Peak hours plan cache warning (#184), NORECOMPUTE+ON PARTITIONS
syntax fix (#215), @Help completeness for all 66 parameters (#212, #213)
2.14.2026.03.04 - Bulk resolution: 42 issues — safety guards, warnings, discovery
tie-breaker, parameter validation, empty partition skip, backup
mid-run detection, new @OrphanedRunThresholdHours parameter (#148)
2.13.2026.03.04 - P3 fixes: @Databases='ALL' normalized to 'ALL_DATABASES' (#159),
empty string parameters normalized to NULL with warning (#198)
2.12.2026.03.04 - P2 fixes: PERSIST_SAMPLE_PERCENT silent override warning (#183),
ROWLOCK+READPAST deadlock retry loop with exponential backoff (#163),
Query Store forced plans per-stat warning after stat update (#168)
2.11.2026.03.04 - P1/P2 fixes: container memory warning always-on (#208),
MAXDOP gate KB reference added (#196), @Tables unqualified
name multi-schema ambiguity warning (#209)
2.10.2026.03.04 - Security/correctness fixes: @CompletionNotifyTable SQL injection (#155),
QS forced plan warning broken join (#187), @DelayBetweenStats WAITFOR overshoot (#210)
2.9.2026.03.04 - P2 bug fixes: XACT_ABORT, EngineEdition label, MAXDOP gate, CommandLog advisory, log space @FailFast, Phase 6 QS per-db, CommandLog dual-accounting, TOCTOU false failures, LIKE metachar escape
2.8.2026.03.04 - Version format update: MMDD → MM.DD for readability; no behavior change
2.8.2026.0303 - v2.8 Bug fixes for deploy, @Tables, CommandLog:
- Fix: Deploy fails on pre-v2.3 servers. SQL Server validates column
names at compile time; wrapped ParameterFingerprint and
LastStatCompletedAt references in sp_executesql (5 locations).
- Fix: @Tables='ALL' silently matched zero tables. Now normalized
to NULL (case-insensitive, whitespace-trimmed).
- Fix: CommandLog SP_STATUPDATE_START Command field showed '...'
truncation. Now shows @Databases, @Tables, @TimeLimit, @SortOrder,
and QS params (conditional).
2.8.2026.0302 - v2.8 Comprehensive issue sweep (31 issues resolved):
- Feat: Query Store forced plan invalidation warning (#32, p1-critical).
After stats updates, automatically checks sys.query_store_plan for
forced plans on updated tables. Warns via @WarningsOut if found.
- Feat: @IncludeIndexedViews parameter (#49). Y = include statistics
on indexed views (views with clustered index). Default N.
- Feat: @LogSkippedToCommandLog parameter (#58). Writes CommandLog
entries for TOCTOU-skipped stats. For compliance audit trails.
- Feat: @ReturnDetailedResults parameter (#25). Returns second result
set with per-statistic detail for PowerShell/automation.
- Feat: @CompletionNotifyTable parameter (#64). Insert notification
row on all exit paths for ETL dependency chains. Auto-creates table.
- Feat: Transaction log space check (#61). Warns when log >90% full
during FULLSCAN operations. Prevents unwarned log growth.
- Feat: Stretch Database auto-skip (#55). Tables with remote data
archive are automatically excluded (deprecated feature, causes timeouts).
- Fix: Azure SQL MI vs DB distinction (#39). EngineEdition 5 (SQL DB)
vs 8 (MI) now detected separately with platform-specific warnings.
- Add: RLS detection (#60). Debug mode warns when Row-Level Security
policies exist on tables being updated (biased histogram risk).
- Add: Wide statistics detection (#59). Debug mode warns for stats
with >8 columns (tempdb/memory pressure risk during FULLSCAN).
- Add: Filtered index mismatch detection (#38). Debug mode warns when
stat filter_definition differs from index filter_definition.
- Add: Columnstore context detection (#28). Debug mode warns about
CCI modification_counter underreporting after bulk loads.
- Add: Computed column detection (#30). Debug mode warns about stats
on non-persisted computed columns (higher scan cost).
- Doc: @Help operational notes added for scheduling (#9), parallel
mode (#12), edition behavior (#36), PERSIST_SAMPLE_PERCENT (#11),
RCSI (#62), locking (#63), modification_counter semantics (#57),
quick start (#15, #16), consultant workflow (#46), Azure (#39).
2.7.2026.0302 - v2.7 AG redo queue pause and tempdb pressure check:
- Feat: @MaxAGRedoQueueMB / @MaxAGWaitMinutes (#18). On AG primaries,
pauses stats when secondary redo queue exceeds threshold. Wait loop
with 30s recheck interval respects both @MaxAGWaitMinutes and @TimeLimit.
Non-AG instances skip check. Stop reason: AG_REDO_QUEUE.
- Feat: @MinTempdbFreeMB (#34). Per-stat tempdb free space check.
When breached: @FailFast=1 aborts with TEMPDB_PRESSURE, else warns
and continues. Important for FULLSCAN and Azure SQL.
- Debug banner now shows AG primary status, redo queue depth, and tempdb
free space. Warnings added for elevated redo queue (>100 MB) and low
tempdb (<100 MB) at startup.
2.6.2026.0302 - v2.6 Multi-database direct mode:
- Fix: @Statistics and @StatisticsFromTable now respect @Databases
parameter (#44). Previously, DIRECT modes queried sys.stats in the
current database context only, silently ignoring @Databases. Now both
modes iterate all selected databases via dynamic SQL with USE, matching
the DISCOVERY mode pattern. Backwards compatible: when @Databases is
NULL, behavior is identical (loops once over current DB).
- Fix: DIRECT_TABLE column gap. Mode 1A was missing 5 columns that
DIRECT_STRING included: auto_created, histogram_steps, has_filter,
filter_definition, unfiltered_rows. Both modes now INSERT identical
column sets.
2.5.2026.0302 - v2.5 Operational intelligence:
- Add: CommandLog index advisory (#31). Warns when dbo.CommandLog
lacks a nonclustered index on CommandType. Provides exact CREATE
INDEX DDL. Suppressed when a suitable index already exists.
- Add: Resource Governor detection (#35). Detects current session
workload group and MAX_DOP cap. Warns (always, not just debug)
when @MaxDOP > RG MAX_DOP — a silent production footgun.
- Doc: @LockTimeout semantics clarified (#51). @Help now documents
NULL vs -1 vs 0 vs positive values with recommendations.
- Fix: Version history dates corrected (v2.3 entries had wrong date).
- Closed: #53 (ALL_DATABASES already excludes system DBs), #41
(NORECOMPUTE already preserved), #14 (misfiled — diag tool param).
2.4.2026.0302 - v2.4 Code quality and LLM navigability:
- Add: 19 region markers for LLM-friendly section navigation.
Enables surgical edits without full-file reprocessing.
- Fix: Collation-aware string comparisons (#48). All user-param-to-DMV-column
comparisons now include COLLATE DATABASE_DEFAULT. Affects @Databases LIKE,
@Tables IN, @ExcludeTables LIKE, @ExcludeStatistics LIKE, @Statistics joins,
@StatisticsFromTable joins. Prevents silent match failures on CS instances.
- Add: Per-phase timing in staged discovery (#40). Debug mode now reports
elapsed milliseconds for each of 6 discovery phases. Enables identification
of slow phases on databases with large catalogs (100K+ stats).
2.3.2026.0227 - Bug fix batch (Issues #27, #33, #47, #50, #52, #66):
- Fix: sp_releaseapplock missing on queue CATCH RETURN path
(connection pool poison when queue init fails). (#27)
- Fix: @@TRANCOUNT check now returns immediately instead of
deferring to validation block. UPDATE STATISTICS acquires
Sch-M locks that escalate unpredictably inside caller
transactions. (#47)
- Fix: SET options enforcement added — proactively sets
ANSI_WARNINGS and ARITHABORT ON at startup (auto-reverts on
proc return). Prevents error 1934 from JDBC/linked server
connections. ANSI_NULLS captured at CREATE time. (#50)
- Fix: @Databases CTE MAXRECURSION raised from 500 to 0
(unlimited). Supports instances with 1000+ databases. (#66)
- Fix: ALL_DATABASES now filters with HAS_DBACCESS() to skip
SINGLE_USER databases held by other sessions. Per-database
TRY/CATCH prevents inaccessible databases from aborting the
entire run. (#52)
- Doc: @ExcludeStatistics/@ExcludeTables @Help text now documents
LIKE wildcard semantics (_ = single-char, [_] for literal). (#33)
2.3.2026.0226 - v2.3 Code Review Implementation:
Trivial: MAXRECURSION 1000, sub-second durations, dm_exec_sessions,
ROWLOCK+READPAST, MAX_GRANT_PERCENT=25, TOCTOU message, hardware
context extension, JSON summary in ExtendedInfo END.
Low: @QueryStoreMetric AVG_CPU, @HoursStaleThreshold, @StopByTime,
@SkipTablesWithColumnstore, phase loss logging, CommandLog schema
check, @Help SAMPLING topic.
Medium: Stat-level LastStatCompletedAt, parameter fingerprint,
@CheckPermissionsOnly.
2.2.2026.0220 - Adversarial Code Review (2 rounds):
Round 1 - Critical bug fixes:
- Fix: DIRECT_TABLE mode crash - INSERT column count mismatch for
is_published, is_tracked_by_cdc, temporal_type columns.
- Fix: DIRECT_STRING mode missing columns - added auto_created,
histogram_steps, has_filter, filter_definition, unfiltered_rows,
is_published, is_tracked_by_cdc, temporal_type to INSERT column list.
- Fix: @WarningsOut OUTPUT not populated when @Debug=0.
- Fix: Staged discovery fallback SELECTs missing 3 replication columns.
- Add: Re-entrancy guard via sp_getapplock (sp_HeapDoctor pattern).
- Add: TOCTOU check before UPDATE STATISTICS execution.
Round 2 - Additional fixes:
- Fix: Applock not released on 3 DIRECT_TABLE early-exit paths.
- Fix: @stats_processed counted TOCTOU-skipped stats (batch limit drift).
- Fix: Legacy discovery @DaysStaleThreshold missed never-updated stats
(NULL last_updated). Wrapped in ISNULL(..., 9999) like staged path.
- Fix: Incremental partition queries ran in wrong database context.
Added USE QUOTENAME(@current_database) to partition DMV queries.
- Fix: @WhatIfOutputTable SQL injection - added character blocklist.
- Fix: Orphan cleanup infinite loop when RunLabel XML is NULL.
- Fix: FORMAT() CLR dependency replaced with CONVERT-based formatting.
- Fix: All @Preset overrides broken - non-NULL parameter defaults caused
IS NULL checks to never fire. Changed to unconditional SET.
2.1.2026.0219 - Phase 2 Extended Awareness:
- SYSDATETIME() consistency: All date/time functions now use SYSDATETIME()
instead of mixed GETDATE()/SYSDATETIME() for sub-second consistency.
- Azure SQL detection: Added @is_azure_sql flag and DTU/vCore consumption
warning for Azure SQL Database/MI/Edge environments.
- Hardware context: Debug mode shows CPU cores, memory, NUMA nodes, uptime.
Warns when uptime < 24h (QS/usage stats may be incomplete).
- RCSI awareness: Detects per-database Read Committed Snapshot Isolation
and snapshot isolation settings in debug output.
- Replication/CDC/Temporal awareness: Discovery now captures is_published,
is_tracked_by_cdc, and temporal_type. Progress output shows REPLICATED,
CDC, TEMPORAL flags for affected tables.
- Backup detection: Warns when backup operations are running (I/O competition).
- @MaxConsecutiveFailures parameter: Stops execution after N consecutive
failures to prevent cascading issues from shared resource problems.
- @WarningsOut OUTPUT parameter: Collects warnings (LOW_UPTIME, BACKUP_RUNNING,
AZURE_SQL) for programmatic access without parsing messages.
- @StopReasonOut OUTPUT parameter: Returns why execution stopped (COMPLETED,
TIME_LIMIT, BATCH_LIMIT, FAIL_FAST, CONSECUTIVE_FAILURES, etc.).
2.0.2026.0212 - Phase 1 Environment Intelligence (v2.0 roadmap):
- CE Version/Compat Level: Shows per-database compatibility level and
effective CE version (70, 120, 130+) in debug output.
- Trace Flag Detection: Detects statistics-relevant trace flags (2371,
2389, 2390, 4139, 9481) and shows warnings for obsolete/misconfigured
flags. TF 2371 note for SQL 2016+ CL 130+, TF 9481 db-scoped config
recommendation, TF 2389/2390 modernization note.
- Database-Scoped Config: Detects LEGACY_CARDINALITY_ESTIMATION setting
and notes when CE is forced to legacy mode.
- SQL 2022 AUTO_DROP: Notes when AUTO_DROP feature is available.
- Fix: Phase validation now detects unexpected row loss between staged
discovery phases and falls back to legacy single-query mode. Prevents
processing incomplete data when DMV cross-applies fail silently.
(#2)
- Fix: Truncated partitions no longer force full RESAMPLE. Stale
partitions with data are updated via ON PARTITIONS(); empty/truncated
partitions are skipped. (#4/26)
- Fix: XE session script corrected - added sp_statement_starting and
sql_statement_starting events for during-execution visibility, fixed
invalid XE predicate syntax (IN() not supported, wait_type needs
numeric map_key values), replaced query_canceled with attention event,
added lock_escalation event, added start/complete correlation query.
(#8)
1.9.2026.0206 - Feat: Added Status and StatusMessage columns to summary result set
for easy Agent job alerting (SUCCESS/WARNING/ERROR). Early-return
path (0 qualifying stats) now also returns the result set.
(Issue #2)
- Perf: Phase 6 Query Store enrichment now uses batched CTE + JOIN
instead of CROSS APPLY per row. Changes O(n) separate QS queries
to O(1) batched query with GROUP BY. Significant improvement on
databases with many statistics and large Query Store catalogs.
(Issue #4)
1.9.2026.0129 - Fix: Changed @long_running_stats table variable PK from CLUSTERED to
NONCLUSTERED to eliminate "maximum key length 900 bytes" warning.
(4 sysname columns = 1024 bytes exceeds clustered limit but is fine
for nonclustered indexes which support up to 1700 bytes in SQL 2016+)
1.9.2026.0128 - Additional Code Review Fixes:
- New @Preset parameter: NIGHTLY_MAINTENANCE, WEEKLY_FULL, OLTP_LIGHT,
WAREHOUSE_AGGRESSIVE for common configuration patterns
- @GroupByJoinPattern parameter (default Y): Updates commonly-joined tables together
to prevent "optimization cliff". Falls back silently if QS disabled/empty.
- @CleanupOrphanedRuns default changed from N to Y (with 24h threshold)
- LOCK_TIMEOUT now restores original session value instead of hardcoding -1
- XML entity decoding now includes " and ' for complete coverage
- READPAST hint added to parallel mode queue for reduced lock contention
- @WhatIfOutputTable validates column DATA TYPES, not just names
- Query Store READ_ONLY warning when using @QueryStorePriority
- ##sp_StatUpdate_Progress global temp table for external monitoring
(opt-in via @ExposeProgressToAllSessions due to security - visible to all sessions)
- Debug mode now explains threshold interaction logic
1.8.2026.0128 - Code Review Fixes (P1 & P2 issues from CODE_REVIEW_ANALYSIS.md):
- P1 #24: @LongRunningSamplePercent now caps at ~10M rows sampled.
- P1 #22: Added row-count validation between staged discovery phases.
- P1 #27: FOR XML PATH error aggregation decodes XML entities
- P1 #23: LOCK_TIMEOUT reset after UPDATE STATISTICS
- P1 #26: Incremental partition targeting cross-references sys.partitions.
- P2 #4: @DeadWorkerTimeoutMinutes default reduced from 30 to 15 minutes.
- P2 #10: @WhatIfOutputTable validates schema when table exists.
- P2 #20: Query Store Phase 6 operations skipped when QS disabled on DB.
1.7.2026.0127 - BREAKING: @ModificationThreshold default 1000 → 5000 (less aggressive)
1.6.2026.0127 - RunLabel in individual stat ExtendedInfo for run correlation
1.6.2026.0128 - Staged Discovery (Performance):
New @StagedDiscovery parameter (default Y). 6-phase discovery
eliminates O(n²) DMV joins by: (1) collect basic candidates from
sys.stats, (2) batch enrich with sys.dm_db_stats_properties,
(3) pre-calculate tier thresholds (no inline SQRT), (4) apply
threshold filters, (5) add page counts for qualifying only,
(6) add Query Store data if enabled. Expected 6-12x improvement
on databases with 10K+ statistics. Use @StagedDiscovery=N for
legacy single-query behavior if needed.
- Adaptive Sampling for Long-Running Stats:
New @LongRunningThresholdMinutes and @LongRunningSamplePercent
parameters. Queries CommandLog for stats that historically took
longer than the threshold (or were killed) and automatically
applies a lower sample rate. Addresses the common problem of
large tables with 100% sample rates that never complete within
maintenance windows. Example: @LongRunningThresholdMinutes=240
forces 10% sample on stats that historically took >4 hours.
- New @ExcludeTables parameter: Exclude tables by pattern (supports %)
to skip problematic or low-priority tables entirely.
- New @CollectHeapForwarding parameter (default N): Controls whether
to query sys.dm_db_index_physical_stats for heap forwarding counts.
Default N for performance; set Y if you need forwarding diagnostics.
- New @WhatIfOutputTable parameter: When @Execute=N, writes generated
commands to specified table instead of just printing. Useful for
automation and command review. Table is auto-created if it doesn't
exist with columns: SequenceNum (IDENTITY), DatabaseName, SchemaName,
TableName, StatName, Command, ModificationCounter, DaysStale, PageCount.
- New @DeadWorkerTimeoutMinutes parameter (default 30): Enhanced dead
worker detection for parallel mode. In addition to checking
dm_exec_requests for session existence, considers worker dead if
no progress for N minutes (handles blocked/hung workers).
- Discovery now captures auto_created flag (sys.stats.auto_created)
and logs AutoCreated in ExtendedInfo XML. Enables deprioritization
of auto-created stats which SQL Server can recreate if needed.
- Discovery captures histogram step count (steps from dm_db_stats_properties)
and logs HistogramSteps in ExtendedInfo for diagnostic insight.
- New @CleanupOrphanedRuns parameter (default N): When Y, finds
SP_STATUPDATE_START entries without matching END (killed runs) and
inserts END markers with StopReason='KILLED' for clean audit trail.
- New @SortOrder = 'AUTO_CREATED': Processes user-created stats first
(auto_created=0), then auto-created stats. Useful when time-limited
to prioritize manually defined statistics over SQL Server auto-created.
- @WhatIfOutputTable auto-creates table if it doesn't exist (no longer
requires user to pre-create with specific schema).
- Created tools/sp_StatUpdate_XE_Session.sql: Extended Events session
for troubleshooting. Captures UPDATE STATISTICS commands, errors,
wait stats (LCK_M_SCH_*, CXPACKET), and long-running statements.
- Code quality: Replaced cursor-based error reporting with FOR XML
aggregation (SQL 2016 compatible). All validation errors now reported
in a single RAISERROR call instead of per-error cursor iteration.
1.5.2026.0120 - CRITICAL: Fixed @ExcludeStatistics filter not working. The OR between
threshold logic blocks had incorrect operator precedence, causing
table/exclusion filters to only apply when @ThresholdLogic='AND'.
Wrapped both OR and AND threshold blocks in parentheses.
- Fix: @TargetNorecompute='N' now correctly filters to regular stats
only (no_recompute=0). Previously included all stats.
- Fix: Time/batch limit messages now use severity 10 (informational)
instead of severity 16 (error). Prevents SQL Agent jobs from
reporting failure when procedure stops gracefully at configured
limits. Return code remains 0 for successful execution; non-zero
only when actual stat update failures occur.
- Removed @StatisticsResample parameter (unnecessary/confusing).
RESAMPLE is now used automatically when appropriate: (1) when
PERSIST_SAMPLE_PERCENT is configured, (2) for incremental stats,
(3) for memory-optimized tables on SQL 2014. "Do no harm" approach.
1.5.2026.0119 - Core logic fixes and new parameters based on code review analysis:
(1) Incremental stats partition targeting: Now queries
sys.dm_db_incremental_stats_properties to find which partitions
have modifications and only updates those (ON PARTITIONS clause).
Previously updated ALL partitions, defeating the purpose of
incremental statistics. SQL 2016 compatible (FOR XML PATH fallback).
(2) Query Store join reorder: Filter by object_id FIRST through
sys.query_store_query, then join to plans. Reduces intermediate
result set size on large Query Store catalogs.
(3) New @ExcludeStatistics parameter: Exclude stats by name pattern
(supports % wildcard), e.g., '_WA_Sys%' to skip auto-created stats.
(4) New @ProgressLogInterval parameter: Log SP_STATUPDATE_PROGRESS
to CommandLog every N stats for Agent job monitoring visibility.
1.4.2026.0119d - Code review fixes:
(1) Tiered threshold cliff effect at 500/501 rows - added SQRT
alternative to first tier to smooth the transition.
(2) FilteredStatsStaleFactor now uses selectivity-adjusted threshold
instead of ratio check (ratio measures selectivity, not staleness).
(3) @parameters_string TRIM to prevent duplicate queues from whitespace.
(4) Query Store state check - only query if actual_state IN (1,2)
for READ_ONLY/READ_WRITE modes (avoids errors on disabled QS).
(5) Style: Changed 1/0 to 1 in EXISTS checks for clarity.
1.4.2026.0119c - Bug fix: Arithmetic overflow (8115) in FILTERED_DRIFT sort order
and Query Store metric calculations. Changed int to bigint for
large value handling; added IIF() cap for ratio calculations.
1.4.2026.0119b - Bug fix: @StatsInParallel=Y table claiming bug. @claimed_tables
table variable was not cleared between loop iterations, causing
SELECT TOP 1 to return stale data from previous claim attempts.
Workers would exit early with COMPLETED after processing 1 stat.
1.4.2026.0119 - Query Store prioritization: @QueryStorePriority cross-references
sys.query_store_plan to prioritize statistics used by active queries.
@QueryStoreMinExecutions and @QueryStoreRecentHours control thresholds.
New QUERY_STORE sort order for query-driven maintenance.
Filtered statistics handling: @FilteredStatsMode (INCLUDE/EXCLUDE/
ONLY/PRIORITY) and @FilteredStatsStaleFactor detect filtered stats
with selectivity drift (unfiltered_rows/rows ratio) on partitioned data.
New FILTERED_DRIFT sort order. ExtendedInfo now logs HasFilter,
FilterDefinition, UnfilteredRows, FilteredDriftRatio, QSPlanCount,
QSTotalExecutions, QSLastExecution. QualifyReason now includes
QUERY_STORE_PRIORITY and FILTERED_DRIFT.
1.3.2026.0119 - Multi-database support: Ola Hallengren-style keywords
(SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES,
AVAILABILITY_GROUP_DATABASES), wildcards (%), exclusions (-).
@IncludeSystemObjects parameter for system object statistics.
AG secondaries excluded automatically (warning, not error).
Bug fixes: RESAMPLE + PERSIST_SAMPLE_PERCENT conflict (1052),
RAISERROR bit parameter (2748), PERSIST invalid usage (153).
Code review hardening: STRING_SPLIT replaced with recursive CTE
for deterministic ordering, persisted_sample_percent respected,
OUTPUT parameters for automation, TRY/CATCH around CommandLog,
OPTION (RECOMPILE) on discovery queries, @commandlog_exists
caching, non-zero return code on failures for Agent jobs,
severity 16 on time/batch limits, MAXRECURSION 0 (unlimited).
1.2.2026.0117 - Tiger Toolbox 5-tier adaptive thresholds (@TieredThresholds),
AND/OR threshold logic (@ThresholdLogic),
version-aware PERSIST_SAMPLE_PERCENT and MAXDOP.
Style refactor, @Help parameter,
incremental stats (@UpdateIncremental),
AG awareness, heap handling (index_id=0, PageCount),
memory-optimized tables, saner defaults.
1.0.2026.0113 - Initial release with versioning, RUN_HEADER/FOOTER logging
Key Features:
- RUN_HEADER/RUN_FOOTER logging to detect killed runs
- Queue-based parallelism for large databases
- Priority ordering (worst stats first)
- Incremental statistics support (ON PARTITIONS)
- RESAMPLE option for preserving sample rates
- Availability Group awareness (prevents write attempts on secondaries)
- Memory-optimized table handling
- Heap forwarding pointer tracking
DROP-IN COMPATIBILITY with Ola Hallengren's SQL Server Maintenance Solution:
https://ola.hallengren.com
REQUIRED:
- dbo.CommandLog table (https://ola.hallengren.com/scripts/CommandLog.sql)
Used for all logging. Set @LogToTable = 'N' if you don't have it.
OPTIONAL (for @StatsInParallel = 'Y'):
- dbo.Queue table (https://ola.hallengren.com/scripts/Queue.sql)
- dbo.QueueStatistic table (auto-created on first parallel run)
NOT REQUIRED:
- dbo.CommandExecute - sp_StatUpdate handles its own command execution
Note: NORECOMPUTE flag is PRESERVED on update (not cleared).
To clear NORECOMPUTE, manually DROP + CREATE the statistic.
*/
IF OBJECT_ID(N'dbo.sp_StatUpdate', N'P') IS NULL
BEGIN
EXECUTE (N'CREATE PROCEDURE dbo.sp_StatUpdate AS RETURN 138;');
END;
GO
ALTER PROCEDURE
dbo.sp_StatUpdate
(
/*
============================================================================
MODE 1: DIRECT - Pass specific statistics (skips DMV discovery)
Three input methods (checked in order):
1. @StatisticsFromTable - Name of temp/permanent table with stats list
2. @Statistics - Comma-separated 'Schema.Table.Stat' values
3. NULL for both - Use discovery mode
============================================================================
*/
@Statistics sysname = NULL, /*'Schema.Table.Stat' or 'Table.Stat', comma-separated for multiple*/
@StatisticsFromTable sysname = NULL, /*table name: '#MyStats', '##GlobalStats', 'dbo.StatsQueue'*/
/*
============================================================================
MODE 2: DISCOVERY - DMV-based candidate selection (when @Statistics is NULL)
============================================================================
*/
/*
PRESETS (v1.9): Common configurations in a single parameter.
When @Preset is specified, it sets defaults for related parameters.
You can still override individual parameters after the preset is applied.
Available presets:
NIGHTLY_MAINTENANCE - Balanced nightly job (1hr limit, tiered thresholds)
WEEKLY_FULL - Weekly comprehensive update (4hr limit, lower thresholds)
OLTP_LIGHT - Minimal impact for OLTP (30min, high thresholds, delay between stats)
WAREHOUSE_AGGRESSIVE - Data warehouse (no time limit, low thresholds, FULLSCAN)
*/
@Preset nvarchar(30) = NULL, /*NULL = use individual parameters. NIGHTLY_MAINTENANCE, WEEKLY_FULL, OLTP_LIGHT, WAREHOUSE_AGGRESSIVE*/
@Databases nvarchar(max) = NULL, /*NULL = current database, SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES (excludes system), AVAILABILITY_GROUP_DATABASES, comma-separated, wildcards (%), exclusions (-)*/
@Tables nvarchar(max) = NULL, /*NULL = all tables, or comma-separated 'Schema.Table'*/
@ExcludeTables nvarchar(max) = NULL, /*comma-separated patterns to exclude (supports % wildcard), e.g., 'dbo.OrderHistory%' to skip archive tables*/
@ExcludeStatistics nvarchar(max) = NULL, /*comma-separated patterns to exclude (supports % wildcard), e.g., '_WA_Sys%' to skip auto-created stats*/
@TargetNorecompute nvarchar(10) = N'BOTH', /*Y = only NORECOMPUTE stats (primary use case - SQL auto-update ignores these), N = only regular stats, BOTH = all stats (default)*/
@ModificationThreshold bigint = 5000, /*minimum modification_counter (note: when @TieredThresholds=1, tiered thresholds apply first; this value only affects large tables where tiered threshold is higher than this floor)*/
@ModificationPercent float = NULL, /*alternative: min mod % of rows (SQRT-based)*/
@TieredThresholds bit = 1, /*1 = use Tiger Toolbox 5-tier adaptive thresholds based on table size (0-500: 500 mods, 501-10K: 20%, 10K-100K: 15%, 100K-1M: 10%, 1M+: 5%). 0 = use fixed @ModificationThreshold*/
@ThresholdLogic nvarchar(3) = N'OR', /*OR = any threshold qualifies (default), AND = all thresholds must be met*/
@DaysStaleThreshold integer = NULL, /*minimum days since last update. Note: counts calendar day boundaries (midnight crossings), not 24-hour periods. Use @HoursStaleThreshold for precise time-based thresholds.*/
@HoursStaleThreshold int = NULL, /*alternative to @DaysStaleThreshold using hours (DATEDIFF(HOUR,...)). Provides precise time-based threshold. Cannot be specified simultaneously with @DaysStaleThreshold.*/
@MinPageCount bigint = 0, /*minimum table page count to process. 0 = no filter, 125 = ~1MB, 125000 = ~1GB. Use to skip tiny tables*/
@IncludeSystemObjects nvarchar(1) = N'N', /*Y = include system object statistics (sys.* tables/views)*/
@IncludeIndexedViews nvarchar(1) = N'N', /*Y = include statistics on indexed views (sys.stats on views with clustered index). N = user tables only (default). UPDATE STATISTICS on indexed views is valid SQL. (#49)*/
@SkipTablesWithColumnstore nchar(1) = N'N', /*Y = skip tables that have a nonclustered columnstore index (type 5 or 6 in sys.indexes). Use when plan stability is paramount - updating rowstore stats on NCCI tables may change batch mode execution plans.*/
@TemporalCoSchedule nvarchar(1) = N'Y', /*Y = when a system-versioned temporal table qualifies, also schedule history table stats. Prevents cross-table cardinality inconsistency for FOR SYSTEM_TIME queries. N = independent scheduling. (#201)*/
@AscendingKeyBoost nvarchar(1) = N'Y', /*Y = identity/ascending key columns qualify with any modifications, bypassing normal threshold (#143). Detects via sys.identity_columns on leading stat column. N = normal threshold only.*/
/*
============================================================================
FILTERED STATISTICS (edge case handling for partitioned data)
============================================================================
*/
@FilteredStatsMode nvarchar(10) = N'INCLUDE', /*INCLUDE = process all (default), EXCLUDE = skip filtered stats, ONLY = only process filtered stats, PRIORITY = boost filtered stats that show selectivity drift*/
@FilteredStatsStaleFactor float = 2.0, /*for PRIORITY mode: trigger update when unfiltered_rows/rows exceeds this factor (detects filter predicate distribution changes)*/
/*
============================================================================
QUERY STORE PRIORITIZATION (query-driven stat maintenance)
============================================================================
*/
@QueryStorePriority nvarchar(1) = NULL, /*Y = boost priority for stats on tables actively used by Query Store plans. N = ignore QS data. NULL = default (normalized to N'N' when no preset, or to N'Y' by OLTP_LIGHT preset). P2e fix v2.4: changed from N'N' to NULL to allow presets to override.*/
@QueryStoreMetric nvarchar(20) = N'CPU', /*resource metric for priority: CPU (total CPU ms, default), DURATION (elapsed time), READS (logical I/O), EXECUTIONS (count), AVG_CPU (total_cpu_ms/execution_count - favors expensive single-execution queries over frequent cheap ones)*/
@QueryStoreMinExecutions bigint = 100, /*minimum plan executions to boost priority*/
@QueryStoreRecentHours integer = 168, /*plans executed in last N hours (default: 7 days). Intentionally short - recent query activity more relevant than 30-day history*/
/*
============================================================================
UPDATE BEHAVIOR (both modes)
============================================================================
*/
@StatisticsSample integer = NULL, /*sample percent: NULL = let SQL Server decide (recommended), 1-100 = explicit %, 100 = FULLSCAN*/
@PersistSamplePercent nvarchar(1) = N'Y', /*Y = add PERSIST_SAMPLE_PERCENT = ON (SQL 2016 SP1 CU4+) to remember sample rate*/
@PersistSampleMinRows bigint = 1000000, /*Minimum sampled rows for RESAMPLE_PERSIST to fire. If computed sampled rows (row_count × persisted_pct%) fall below this, RESAMPLE is skipped to avoid low-quality histograms. NULL = no floor check.*/
@MaxDOP integer = NULL, /*MAXDOP for UPDATE STATISTICS (SQL 2016 SP2+ / SQL 2017 CU3+). NULL = server default*/
@MaxGrantPercent int = 10, /*Memory grant cap percent (1-100) for the candidate discovery SELECT ONLY. NOTE: Does NOT cap UPDATE STATISTICS memory (hint not valid on that statement). For stats memory control, use Resource Governor or reduce @StatisticsSample. Applied as OPTION(MAX_GRANT_PERCENT) on the internal ranking query. NULL = no hint. Default 10 limits memory monopolization during candidate enumeration.*/
/*
============================================================================
INCREMENTAL STATISTICS (partitioned tables only)
============================================================================
*/
@UpdateIncremental bit = 1, /*1 = use ON PARTITIONS() for incremental stats*/
/*
============================================================================
EXECUTION CONTROL (both modes)
============================================================================
*/
@TimeLimit integer = 3600, /*seconds (default: 1 hour, NULL = unlimited)*/
@StopByTime nvarchar(8) = NULL, /*absolute wall-clock stop time (format: HH:MM or HH:MM:SS). Computes remaining seconds from now to specified time today. Overrides @TimeLimit when specified.*/
@MaxSecondsPerStat int = NULL, /*P2c fix (v2.4): When @StopByTime/@TimeLimit is set, check CommandLog history. If estimated duration for a stat exceeds both this cap AND remaining seconds, skip with warning. NULL = no per-stat cap. Only skips if CommandLog history exists (conservative).*/
@BatchLimit integer = NULL, /*max stats to update per run*/
@SortOrder nvarchar(50) = N'MODIFICATION_COUNTER', /*priority: MODIFICATION_COUNTER, DAYS_STALE, RANDOM, PAGE_COUNT, QUERY_STORE, FILTERED_DRIFT, AUTO_CREATED*/
@DelayBetweenStats integer = NULL, /*seconds to wait between stats updates. Use during OLTP hours to reduce contention; NULL = no delay*/
@MaxConsecutiveFailures integer = NULL, /*stop after N consecutive failures (prevents cascading issues from shared resource problems). NULL = no limit*/
@MaxAGRedoQueueMB integer = NULL, /*AG primary only: pause stats when any secondary redo queue exceeds this MB. NULL = disabled. Checks dm_hadr_database_replica_states before each stat update.*/
@MaxAGWaitMinutes integer = 10, /*max minutes to wait for AG redo queue to drain before stopping (stop reason: AG_REDO_QUEUE). Only used when @MaxAGRedoQueueMB is set.*/
/*
============================================================================
JOIN PATTERN GROUPING (v1.9)
Addresses the "optimization cliff" concern: partial updates
can create INCONSISTENT stats between joined tables, causing wrong join orders.
When enabled, stats on commonly-joined tables are updated together.
============================================================================
*/
@GroupByJoinPattern nvarchar(1) = N'Y', /*Y (default) = group stats by Query Store join patterns (update joined tables together, prevents optimization cliffs). Falls back silently to priority ordering if QS disabled/empty. N = process by priority only*/
@JoinPatternMinExecutions int = 100, /*minimum plan executions to consider when detecting join patterns*/
/*
============================================================================
ADAPTIVE SAMPLING FOR LONG-RUNNING STATS
Addresses stats that historically take too long (e.g., >4 hours with 100% sample)
by automatically applying a lower sample rate based on CommandLog history.
============================================================================
*/
@LongRunningThresholdMinutes int = NULL, /*stats that took longer than this in CommandLog get forced sample rate. Requires existing CommandLog history - first run with this enabled will find nothing. (NULL = disabled)*/
@LongRunningSamplePercent int = 10, /*sample percent to use for long-running stats (default: 10%)*/
/*
============================================================================
PERFORMANCE OPTIMIZATION
============================================================================
*/
@StagedDiscovery nvarchar(1) = N'Y', /*Y = use 6-phase staged discovery (faster for large DBs), N = legacy single-query discovery*/
@CollectHeapForwarding nvarchar(1) = N'N', /*Y = query dm_db_index_physical_stats for heap forwarding counts (slow), N = skip*/
@MinTempdbFreeMB bigint = NULL, /*minimum tempdb free space (MB) before each stat update. NULL = disabled. When breached: @FailFast=1 aborts, else warns and continues. Especially important for FULLSCAN and Azure SQL.*/
/*
============================================================================
LOGGING & OUTPUT (both modes)
============================================================================
*/
@LockTimeout integer = NULL, /*seconds to wait for schema locks. NULL = session default (typically infinite), -1 = infinite, 0 = fail immediately on any contention, positive = seconds. Recommended: 30-300 for parallel mode*/
@LogToTable nvarchar(1) = N'Y', /*Y = log to dbo.CommandLog (requires table), N = no logging*/
@LogSkippedToCommandLog nvarchar(1) = N'N', /*Y = write CommandLog entries for skipped statistics (TOCTOU/threshold-filtered) with Command='SKIPPED'. For compliance audit trails. (#58)*/
@ProgressLogInterval int = NULL, /*log progress to CommandLog every N stats (for Agent job monitoring)*/
@Execute nvarchar(1) = N'Y', /*Y = execute, N = print only (dry run)*/
@ReturnDetailedResults bit = 0, /*1 = return second result set with per-statistic detail (database, schema, table, stat, command, duration_ms, result). For automation/PowerShell integration. (#25)*/
@CompletionNotifyTable nvarchar(500) = NULL, /*table to receive notification row on all exit paths (RunLabel, StartTime, EndTime, StopReason, counts). Auto-created if not exists. For ETL dependency chains. (#64)*/
@WhatIfOutputTable nvarchar(500) = NULL, /*table to receive commands when @Execute = N (for automation)*/
@CheckPermissionsOnly nchar(1) = N'N', /*Y = check required permissions (VIEW ANY DATABASE, VIEW DATABASE STATE, ALTER on tables, INSERT on CommandLog) and report missing ones. Returns without executing any stats updates. Diagnostic mode only.*/
@FailFast bit = 0, /*1 = abort on first error*/
@Debug bit = 0, /*1 = verbose output*/
@ExposeProgressToAllSessions nvarchar(1) = N'N', /*Y = create ##sp_StatUpdate_Progress global temp table (SECURITY NOTE: visible to ALL sessions on server). N = disabled (use @ProgressLogInterval for secure monitoring)*/
@CleanupOrphanedRuns nvarchar(1) = N'Y', /*Y = mark orphaned SP_STATUPDATE_START entries (no END) as KILLED. v1.9: Default changed from N to Y*/
@OrphanedRunThresholdHours int = 48, /*#148: Hours before a START entry with no END is considered orphaned. Default 48h to accommodate multi-day maintenance runs.*/
/*
============================================================================
PARALLEL EXECUTION (Ola Hallengren Queue pattern)
When @StatsInParallel = 'Y':
- First worker populates dbo.QueueStatistic with qualifying stats
- All workers claim work via UPDATE ... WHERE StatStartTime IS NULL
- Dead workers detected via sys.dm_exec_sessions (sleeping workers between stats appear in dm_exec_sessions but not dm_exec_requests)
- Run same EXECUTE from multiple sessions/jobs for parallelism
Prerequisites:
- dbo.Queue table (https://ola.hallengren.com/scripts/Queue.sql)
- dbo.QueueStatistic table (auto-created on first parallel run)
============================================================================
*/
@StatsInParallel nvarchar(1) = N'N', /*Y = use queue-based parallel processing*/
@MaxWorkers int = NULL, /*max concurrent parallel workers (#181). Before claiming work, count active sessions in QueueStatistic. If >= @MaxWorkers, exit cleanly with RAISERROR. NULL = unlimited.*/
@DeadWorkerTimeoutMinutes int = 15, /*consider worker dead if no progress for N minutes (NULL = only check dm_exec_sessions). P2 #4: Reduced from 30 to 15 min.*/
/*
============================================================================
HELP & VERSION OUTPUT
============================================================================
*/
@Help nvarchar(50) = N'0', /*1 = show help in SSMS result set. Named topics: 'SAMPLING' = sampling priority order documentation*/
@Version varchar(20) = NULL OUTPUT, /*returns procedure version*/
@VersionDate datetime = NULL OUTPUT, /*returns procedure version date*/
/*
============================================================================
SUMMARY OUTPUT (for automation - avoids parsing result sets)
============================================================================
*/
@StatsFoundOut integer = NULL OUTPUT, /*total qualifying stats discovered*/
@StatsProcessedOut integer = NULL OUTPUT, /*stats attempted (succeeded + failed)*/
@StatsSucceededOut integer = NULL OUTPUT, /*stats updated successfully*/
@StatsFailedOut integer = NULL OUTPUT, /*stats that failed to update*/
@StatsRemainingOut integer = NULL OUTPUT, /*stats not processed (time/batch limit)*/
@DurationSecondsOut integer = NULL OUTPUT, /*total run duration in seconds*/
@WarningsOut nvarchar(max) = NULL OUTPUT, /*collected warnings for programmatic access*/
@StopReasonOut nvarchar(50) = NULL OUTPUT /*why execution stopped: COMPLETED, TIME_LIMIT, BATCH_LIMIT, FAIL_FAST, CONSECUTIVE_FAILURES, etc.*/
)
WITH RECOMPILE
AS
BEGIN
/*#region 02-INIT: SET options, version constants */
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
/*
============================================================================
VERSION AND CONSTANTS
============================================================================
*/
DECLARE
/* VERSION: Update BOTH @procedure_version AND @procedure_version_date together.
Also update the header comment "Version:" line at the top of the file. */
@procedure_version varchar(20) = '2.19.2026.03.09',
@procedure_version_date datetime = '20260309',
@procedure_name sysname = OBJECT_NAME(@@PROCID),
@procedure_schema sysname = OBJECT_SCHEMA_NAME(@@PROCID);
/*
Set output parameters
*/
SELECT
@Version = @procedure_version,
@VersionDate = @procedure_version_date;
/*#endregion 02-INIT */
/*#region 03-HELP: @Help parameter output */
/*
============================================================================
HELP SECTION
============================================================================
*/
IF @Help IN (N'1', N'Y')
BEGIN
/*
Introduction
*/
SELECT
introduction =
N'Hi, I''m sp_StatUpdate!' UNION ALL
SELECT
N'I help you update statistics with priority ordering and time limits.' UNION ALL
SELECT
N'I can handle NORECOMPUTE stats, incremental stats, and memory-optimized tables.' UNION ALL
SELECT
N'for more info, visit: https://github.com/nanoDBA/sp_StatUpdate' UNION ALL
SELECT
N'' UNION ALL
SELECT
N'version: ' + @procedure_version + N' (' + CONVERT(nvarchar(10), @procedure_version_date, 120) + N')' UNION ALL
SELECT
N'v2.1 Phase 2: Extended Awareness (replication, CDC, temporal, consecutive failures)' UNION ALL
SELECT
N'v2.2: Adversarial review fixes (DIRECT mode crash, re-entrancy guard, TOCTOU check)' UNION ALL
SELECT
N'v2.3: Bug fixes (#27 applock leak, #47 open tran halt, #50 SET validation, #52 DB access, #66 MAXRECURSION)' UNION ALL
SELECT
N'v2.4: Region markers, collation-aware comparisons (#48), discovery phase timing (#40)' UNION ALL
SELECT
N'v2.5: CommandLog index advisory (#31), Resource Governor detection (#35), @LockTimeout docs (#51)' UNION ALL
SELECT
N'v2.6: Multi-database direct mode -- @Statistics and @StatisticsFromTable respect @Databases (#44)' UNION ALL
SELECT
N'v2.7: AG redo queue pause (#18), tempdb pressure check (#34)' UNION ALL
SELECT
N'v2.8: Comprehensive issue sweep — 31 issues resolved (QS forced plans, indexed views, log space, RLS/columnstore/filter detection)';
/* P3f (v2.4): QUICK START section — printed before parameter list */
RAISERROR(N'=============================================', 10, 1) WITH NOWAIT;
RAISERROR(N'sp_StatUpdate — QUICK START', 10, 1) WITH NOWAIT;
RAISERROR(N'=============================================', 10, 1) WITH NOWAIT;
RAISERROR(N'The fastest path: use a @Preset for a pre-tuned configuration.', 10, 1) WITH NOWAIT;
RAISERROR(N'', 10, 1) WITH NOWAIT;
RAISERROR(N' NIGHTLY_MAINTENANCE Smart staleness + MAXDOP 4 + StopByTime-aware', 10, 1) WITH NOWAIT;
RAISERROR(N' WEEKLY_FULL FULLSCAN on all user tables, no time limit', 10, 1) WITH NOWAIT;
RAISERROR(N' OLTP_LIGHT Sample-only, MAXDOP 2, skips large tables', 10, 1) WITH NOWAIT;
RAISERROR(N' WAREHOUSE_AGGRESSIVE FULLSCAN + parallel, dedicated maintenance windows', 10, 1) WITH NOWAIT;
RAISERROR(N'', 10, 1) WITH NOWAIT;
RAISERROR(N'Examples:', 10, 1) WITH NOWAIT;
RAISERROR(N' EXEC sp_StatUpdate @Preset = N''NIGHTLY_MAINTENANCE'', @Database = N''MyDB'';', 10, 1) WITH NOWAIT;
RAISERROR(N' EXEC sp_StatUpdate @Preset = N''WEEKLY_FULL'', @Execute = N''N''; -- dry run first', 10, 1) WITH NOWAIT;
RAISERROR(N'', 10, 1) WITH NOWAIT;
RAISERROR(N'Then use individual parameters below to fine-tune.', 10, 1) WITH NOWAIT;
RAISERROR(N'=============================================', 10, 1) WITH NOWAIT;
/*
Parameter documentation
*/
SELECT
parameter_name =
ap.name,
data_type =
t.name +
CASE
WHEN t.name IN (N'varchar', N'nvarchar', N'char', N'nchar')
THEN N'(' +
CASE
WHEN ap.max_length = -1
THEN N'max'
WHEN t.name IN (N'nvarchar', N'nchar')
THEN CONVERT(nvarchar(10), ap.max_length / 2)
ELSE CONVERT(nvarchar(10), ap.max_length)
END + N')'
ELSE N''
END,
description =
CASE ap.name
WHEN N'@Statistics'
THEN N'DIRECT MODE: comma-separated stat names (Schema.Table.Stat or Table.Stat)'
WHEN N'@StatisticsFromTable'
THEN N'DIRECT MODE: table name containing stats list (#temp, ##global, or dbo.Table)'
WHEN N'@Preset'
THEN N'NIGHTLY_MAINTENANCE, WEEKLY_FULL, OLTP_LIGHT, WAREHOUSE_AGGRESSIVE - sets common defaults'
WHEN N'@Databases'
THEN N'DISCOVERY: database(s) - NULL=current, SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES (excludes system), AVAILABILITY_GROUP_DATABASES, wildcards (%), exclusions (-)'
WHEN N'@Tables'
THEN N'DISCOVERY MODE: table filter (NULL = all, comma-separated Schema.Table)'
WHEN N'@ExcludeTables'
THEN N'DISCOVERY MODE: exclude tables by LIKE pattern (% = multi-char, _ = single-char wildcard, [_] for literal underscore)'
WHEN N'@ExcludeStatistics'
THEN N'DISCOVERY MODE: exclude stats by LIKE pattern (% = multi-char, _ = single-char wildcard, [_] for literal underscore)'
WHEN N'@IncludeSystemObjects'
THEN N'Y = include system object statistics (sys.* tables/views), N = user objects only'
WHEN N'@TargetNorecompute'
THEN N'Y = only NORECOMPUTE stats, N = only regular stats, BOTH = all stats'
WHEN N'@ModificationThreshold'
THEN N'minimum modification_counter to qualify for update'
WHEN N'@ModificationPercent'
THEN N'SQRT-based formula (mod_counter >= percent * SQRT(row_count))'
WHEN N'@TieredThresholds'
THEN N'1 = use Tiger Toolbox 5-tier adaptive thresholds (ignores @ModificationPercent)'
WHEN N'@ThresholdLogic'
THEN N'OR = any threshold qualifies, AND = all must be met'
WHEN N'@DaysStaleThreshold'
THEN N'minimum days since last update to qualify'
WHEN N'@MinPageCount'
THEN N'minimum used_page_count (125000 = ~1GB tables only)'
WHEN N'@StatisticsSample'
THEN N'sample percent: NULL = SQL Server decides, 100 = FULLSCAN'
WHEN N'@PersistSamplePercent'
THEN N'Y = add PERSIST_SAMPLE_PERCENT = ON to preserve sample rate'
WHEN N'@MaxDOP'
THEN N'MAXDOP for FULLSCAN operations (SQL Server 2016 SP2+)'
WHEN N'@MaxGrantPercent'
THEN N'Memory grant cap (1-100%) for candidate discovery query ONLY. Does NOT cap UPDATE STATISTICS memory (not valid syntax). To cap stats memory, use Resource Governor or reduce @StatisticsSample.'
WHEN N'@UpdateIncremental'
THEN N'1 = use ON PARTITIONS() for incremental stats on partitioned tables'
WHEN N'@TimeLimit'
THEN N'maximum seconds to run (NULL = unlimited, default: 18000 = 5 hours)'
WHEN N'@BatchLimit'
THEN N'maximum number of stats to update per run'
WHEN N'@SortOrder'
THEN N'priority ordering: MODIFICATION_COUNTER, DAYS_STALE, PAGE_COUNT, RANDOM, QUERY_STORE, FILTERED_DRIFT, AUTO_CREATED (user stats first)'
WHEN N'@GroupByJoinPattern'
THEN N'Y (default) = group stats by Query Store join patterns (prevents optimization cliffs, falls back if QS empty), N = priority only'
WHEN N'@JoinPatternMinExecutions'
THEN N'minimum plan executions to detect join patterns (default 100)'
WHEN N'@FilteredStatsMode'
THEN N'INCLUDE = all stats, EXCLUDE = skip filtered, ONLY = filtered only, PRIORITY = boost filtered with drift'
WHEN N'@FilteredStatsStaleFactor'
THEN N'PRIORITY mode threshold multiplier for selectivity-adjusted threshold (default 2.0)'
WHEN N'@QueryStorePriority'
THEN N'Y = prioritize stats used by Query Store plans, N = ignore'
WHEN N'@QueryStoreMetric'
THEN N'CPU = total CPU time (default), DURATION = elapsed time, READS = logical I/O, EXECUTIONS = count, AVG_CPU = avg CPU per execution (total_cpu_ms/execution_count, favors expensive single-run queries)'
WHEN N'@QueryStoreMinExecutions'
THEN N'minimum plan executions to boost priority (default 100)'
WHEN N'@QueryStoreRecentHours'
THEN N'only consider plans executed in last N hours (default 168 = 7 days)'
WHEN N'@DelayBetweenStats'
THEN N'seconds to wait between stats updates (pacing)'
WHEN N'@LongRunningThresholdMinutes'
THEN N'stats that took longer than this in CommandLog get forced sample rate (NULL = disabled)'
WHEN N'@LongRunningSamplePercent'
THEN N'sample percent for long-running stats (default 10%)'
WHEN N'@StagedDiscovery'
THEN N'Y = 6-phase staged discovery (faster for large DBs), N = legacy single query'
WHEN N'@CollectHeapForwarding'
THEN N'Y = collect heap forwarding pointer counts (slow), N = skip for performance'
WHEN N'@LockTimeout'
THEN N'seconds to wait for schema locks. NULL = session default, -1 = infinite wait, 0 = fail immediately on any contention, positive = seconds before timeout. Recommended: 30-300 for parallel mode.'
WHEN N'@LogToTable'
THEN N'Y = log to dbo.CommandLog table'
WHEN N'@ProgressLogInterval'
THEN N'log SP_STATUPDATE_PROGRESS to CommandLog every N stats (Agent job monitoring). Requires @LogToTable=Y. Progress entries include StatsProcessed, StatsSucceeded, StatsFailed, StatsTotal, ElapsedSeconds in ExtendedInfo XML.'
WHEN N'@Execute'
THEN N'Y = execute commands, N = print only (dry run)'
WHEN N'@WhatIfOutputTable'
THEN N'table to receive commands when @Execute = N (for automation scripts)'
WHEN N'@FailFast'
THEN N'1 = abort on first error, 0 = continue processing'
WHEN N'@Debug'
THEN N'1 = verbose diagnostic output'
WHEN N'@ExposeProgressToAllSessions'
THEN N'Y = create ##sp_StatUpdate_Progress (SECURITY: visible to ALL sessions). Use @ProgressLogInterval for secure monitoring.'
WHEN N'@CleanupOrphanedRuns'
THEN N'Y (default) = mark orphaned START entries >24h old (killed runs) with END marker'
WHEN N'@HoursStaleThreshold'
THEN N'alternative to @DaysStaleThreshold using precise hours (DATEDIFF HOUR). Note: @DaysStaleThreshold counts calendar midnight crossings, not 24-hour periods. Cannot be combined with @DaysStaleThreshold.'
WHEN N'@MaxSecondsPerStat'
THEN N'ADVISORY per-stat scheduling gate (NOT a hard timeout). Skips a stat when its estimated duration (from CommandLog history) exceeds BOTH this cap AND remaining window time. A stat with no CommandLog history always runs. Does not kill running UPDATE STATISTICS. NULL = disabled.'
WHEN N'@PersistSampleMinRows'
THEN N'minimum absolute sampled rows before RESAMPLE_PERSIST fires. If row_count * persisted_pct% < this value, RESAMPLE is skipped to avoid low-quality histograms. NULL = no floor check. Default 1000000.'
WHEN N'@StopByTime'
THEN N'wall-clock stop time (HH:MM or HH:MM:SS). Computes remaining seconds from now to specified time today and uses as @TimeLimit. Overrides @TimeLimit when specified.'
WHEN N'@SkipTablesWithColumnstore'
THEN N'Y = skip tables with nonclustered/clustered columnstore indexes (type 5/6 in sys.indexes). Use when plan stability is critical — updating rowstore stats alongside NCCIs may change batch mode execution plans.'
WHEN N'@TemporalCoSchedule'
THEN N'Y = when a system-versioned temporal table qualifies, also schedule its history table stats (#201). Prevents cardinality inconsistency for FOR SYSTEM_TIME queries that join current + history tables. N = independent scheduling.'
WHEN N'@AscendingKeyBoost'
THEN N'Y = identity/ascending key columns qualify with any modifications, bypassing normal threshold (#143). Detects via sys.identity_columns on the stat''s leading column. Prevents histogram staleness for insert-heavy tables. N = normal threshold only.'
WHEN N'@CheckPermissionsOnly'
THEN N'Y = check required permissions (VIEW ANY DATABASE, VIEW DATABASE STATE, INSERT on CommandLog) and report missing ones. No stats updates are executed. Diagnostic mode.'
WHEN N'@StatsInParallel'
THEN N'Y = use queue-based parallel processing (requires Queue tables)'
WHEN N'@MaxWorkers'
THEN N'max concurrent parallel workers (#181). Before joining queue, counts active sessions. If >= limit, exits cleanly (StopReason=MAX_WORKERS, return 0). NULL = unlimited. Use to prevent blocking storms on OLTP systems.'
WHEN N'@DeadWorkerTimeoutMinutes'
THEN N'parallel mode: consider worker dead if no progress for N minutes (default 15). Uses LastStatCompletedAt heartbeat column for accurate per-stat tracking. Units: minutes (same as @MaxAGWaitMinutes). Only applies when @StatsInParallel=Y.'
WHEN N'@Help'
THEN N'1 = show this help information. Named topics: SAMPLING = sampling priority order documentation.'
WHEN N'@Version'
THEN N'OUTPUT: returns procedure version string'
WHEN N'@VersionDate'
THEN N'OUTPUT: returns procedure version date'
WHEN N'@StatsFoundOut'
THEN N'OUTPUT: total qualifying stats discovered'
WHEN N'@StatsProcessedOut'
THEN N'OUTPUT: stats attempted (succeeded + failed)'
WHEN N'@StatsSucceededOut'
THEN N'OUTPUT: stats updated successfully'
WHEN N'@StatsFailedOut'
THEN N'OUTPUT: stats that failed to update'
WHEN N'@StatsRemainingOut'
THEN N'OUTPUT: stats not processed (time/batch limit)'
WHEN N'@DurationSecondsOut'
THEN N'OUTPUT: total run duration in seconds'
WHEN N'@WarningsOut'
THEN N'OUTPUT: collected warnings (LOW_UPTIME, BACKUP_RUNNING, AZURE_SQL, etc.)'
WHEN N'@StopReasonOut'
THEN N'OUTPUT: why execution stopped (COMPLETED, TIME_LIMIT, BATCH_LIMIT, FAIL_FAST, CONSECUTIVE_FAILURES, AG_REDO_QUEUE, TEMPDB_PRESSURE, LOG_SPACE_HIGH)'
WHEN N'@MaxConsecutiveFailures'
THEN N'stop after N consecutive failures (prevents cascading issues from shared resource problems)'
WHEN N'@MaxAGRedoQueueMB'
THEN N'AG primary only: pause stats when any secondary redo queue exceeds threshold MB. Checks dm_hadr_database_replica_states before each stat. Non-AG instances skip check. NULL = disabled.'
WHEN N'@MaxAGWaitMinutes'
THEN N'max minutes to wait for AG redo queue to drain before stopping with AG_REDO_QUEUE. Interacts with @TimeLimit (whichever is reached first wins). Default 10.'
WHEN N'@MinTempdbFreeMB'
THEN N'minimum tempdb free space (MB) before each stat update. When breached: @FailFast=1 aborts with TEMPDB_PRESSURE, else warns and continues. Important for FULLSCAN and Azure SQL. NULL = disabled.'
WHEN N'@IncludeIndexedViews'
THEN N'Y = include statistics on indexed views (views with clustered index). N (default) = user tables only. UPDATE STATISTICS on indexed views is valid SQL syntax.'
WHEN N'@LogSkippedToCommandLog'
THEN N'Y = write CommandLog entries for TOCTOU-skipped stats (Command=SKIPPED) and @MaxSecondsPerStat-skipped stats. For compliance audit trails. Requires @LogToTable=Y. Skipped stats also appear in @ReturnDetailedResults if enabled. N (default) = skip silently.'
WHEN N'@ReturnDetailedResults'
THEN N'1 = return second result set with per-statistic detail (database, schema, table, stat, mods, priority, processed). For PowerShell/automation.'
WHEN N'@CompletionNotifyTable'
THEN N'table to receive notification row on all exit paths (RunLabel, times, counts, StopReason). Auto-created if not exists. For ETL dependency chains. NULL = disabled.'
ELSE N'undocumented parameter'
END,
valid_inputs =
CASE ap.name
/* String filters */
WHEN N'@Statistics' THEN N'NULL, schema.table.stat CSV (e.g., dbo.Orders.IX_Date)'
WHEN N'@StatisticsFromTable' THEN N'NULL, table name (e.g., #MyStats, dbo.StatsQueue)'
WHEN N'@Databases' THEN N'NULL, name/CSV/keyword (USER_DATABASES, ALL_DATABASES, SYSTEM_DATABASES, %wildcard%, -exclusion)'
WHEN N'@Tables' THEN N'NULL, ALL, schema.table CSV (supports % wildcards)'
WHEN N'@ExcludeTables' THEN N'NULL, schema.table CSV (supports % wildcards)'
WHEN N'@ExcludeStatistics' THEN N'NULL, stat name CSV (supports % wildcards, e.g., _WA_Sys%)'
/* Y/N toggles */
WHEN N'@IncludeSystemObjects' THEN N'Y, N'
WHEN N'@IncludeIndexedViews' THEN N'Y, N'
WHEN N'@TargetNorecompute' THEN N'Y, N, BOTH'
WHEN N'@SkipTablesWithColumnstore' THEN N'Y, N'
WHEN N'@TemporalCoSchedule' THEN N'Y, N'
WHEN N'@AscendingKeyBoost' THEN N'Y, N'
WHEN N'@QueryStorePriority' THEN N'Y, N'
WHEN N'@GroupByJoinPattern' THEN N'Y, N'
WHEN N'@StagedDiscovery' THEN N'Y, N'
WHEN N'@CollectHeapForwarding' THEN N'Y, N'
WHEN N'@LogToTable' THEN N'Y, N'
WHEN N'@LogSkippedToCommandLog' THEN N'Y, N'
WHEN N'@Execute' THEN N'Y, N'
WHEN N'@PersistSamplePercent' THEN N'Y, N'
WHEN N'@StatsInParallel' THEN N'Y, N'
WHEN N'@CleanupOrphanedRuns' THEN N'Y, N'
WHEN N'@ExposeProgressToAllSessions' THEN N'Y, N'
WHEN N'@CheckPermissionsOnly' THEN N'Y, N'
/* Enums */
WHEN N'@Preset' THEN N'NULL, NIGHTLY_MAINTENANCE, WEEKLY_FULL, OLTP_LIGHT, WAREHOUSE_AGGRESSIVE'
WHEN N'@SortOrder' THEN N'MODIFICATION_COUNTER, DAYS_STALE, PAGE_COUNT, RANDOM, QUERY_STORE, FILTERED_DRIFT, AUTO_CREATED'
WHEN N'@FilteredStatsMode' THEN N'INCLUDE, EXCLUDE, ONLY, PRIORITY'
WHEN N'@QueryStoreMetric' THEN N'CPU, DURATION, READS, EXECUTIONS, AVG_CPU'
WHEN N'@ThresholdLogic' THEN N'OR, AND'
/* Numeric */
WHEN N'@ModificationThreshold' THEN N'0-N (floor for modification_counter)'
WHEN N'@ModificationPercent' THEN N'NULL, 0.01-100 (percent of rows)'
WHEN N'@TieredThresholds' THEN N'0, 1'
WHEN N'@DaysStaleThreshold' THEN N'NULL, 1-N days'
WHEN N'@HoursStaleThreshold' THEN N'NULL, 1-N hours'
WHEN N'@MinPageCount' THEN N'0-N (125000 ≈ 1 GB)'
WHEN N'@FilteredStatsStaleFactor' THEN N'0.1-N (ratio of unfiltered/filtered rows)'
WHEN N'@QueryStoreMinExecutions' THEN N'1-N (plan execution count floor)'
WHEN N'@QueryStoreRecentHours' THEN N'1-N hours'
WHEN N'@JoinPatternMinExecutions' THEN N'1-N (default 100)'
WHEN N'@StatisticsSample' THEN N'NULL, 1-100 (100 = FULLSCAN)'
WHEN N'@MaxDOP' THEN N'NULL (server default), 0-N'
WHEN N'@MaxGrantPercent' THEN N'NULL (server default), 0.01-100'
WHEN N'@UpdateIncremental' THEN N'0, 1'
WHEN N'@TimeLimit' THEN N'NULL (unlimited), 1-N seconds'
WHEN N'@StopByTime' THEN N'NULL, HH:MM (e.g., 05:00, 23:59)'
WHEN N'@BatchLimit' THEN N'NULL (no limit), 1-N stats'
WHEN N'@DelayBetweenStats' THEN N'NULL, 1-N seconds'
WHEN N'@LongRunningThresholdMinutes' THEN N'NULL (disabled), 1-N minutes'
WHEN N'@LongRunningSamplePercent' THEN N'1-100'
WHEN N'@LockTimeout' THEN N'NULL (session default), -1 (infinite), 0-N seconds'
WHEN N'@ProgressLogInterval' THEN N'NULL, 1-N (e.g., 10, 50, 100)'
WHEN N'@MaxConsecutiveFailures' THEN N'NULL, 1-N (e.g., 3, 5, 10)'
WHEN N'@MaxAGRedoQueueMB' THEN N'NULL (disabled), 0-N MB'
WHEN N'@MaxAGWaitMinutes' THEN N'1-N minutes'
WHEN N'@MinTempdbFreeMB' THEN N'NULL (disabled), 0-N MB'
WHEN N'@MaxSecondsPerStat' THEN N'NULL (disabled), 1-N seconds'
WHEN N'@PersistSampleMinRows' THEN N'NULL (no floor), 1-N rows'
WHEN N'@OrphanedRunThresholdHours' THEN N'1-N hours'
WHEN N'@MaxWorkers' THEN N'NULL (unlimited), 1-N workers'
WHEN N'@DeadWorkerTimeoutMinutes' THEN N'NULL, 1-N minutes'
WHEN N'@ReturnDetailedResults' THEN N'0, 1'
WHEN N'@FailFast' THEN N'0, 1'
WHEN N'@Debug' THEN N'0, 1'
WHEN N'@Help' THEN N'0, 1'
/* Table/string references */
WHEN N'@WhatIfOutputTable' THEN N'NULL, table name (auto-created if not exists)'
WHEN N'@CompletionNotifyTable' THEN N'NULL, table name (auto-created if not exists)'
/* OUTPUT parameters */
WHEN N'@Version' THEN N'OUTPUT nvarchar(30)'
WHEN N'@VersionDate' THEN N'OUTPUT datetime'
WHEN N'@StatsFoundOut' THEN N'OUTPUT int'
WHEN N'@StatsProcessedOut' THEN N'OUTPUT int'
WHEN N'@StatsSucceededOut' THEN N'OUTPUT int'
WHEN N'@StatsFailedOut' THEN N'OUTPUT int'
WHEN N'@StatsRemainingOut' THEN N'OUTPUT int'
WHEN N'@DurationSecondsOut' THEN N'OUTPUT int'
WHEN N'@WarningsOut' THEN N'OUTPUT nvarchar(max)'
WHEN N'@StopReasonOut' THEN N'OUTPUT nvarchar(50)'
ELSE N''
END,
defaults =
CASE ap.name
WHEN N'@Statistics'
THEN N'NULL (use discovery mode)'
WHEN N'@StatisticsFromTable'
THEN N'NULL'
WHEN N'@Databases'
THEN N'NULL (current database)'
WHEN N'@Tables'
THEN N'NULL (all tables)'
WHEN N'@ExcludeTables'
THEN N'NULL (no exclusions)'
WHEN N'@ExcludeStatistics'
THEN N'NULL (no exclusions)'
WHEN N'@IncludeSystemObjects'
THEN N'N'
WHEN N'@TargetNorecompute'
THEN N'BOTH'
WHEN N'@ModificationThreshold'
THEN N'5000'
WHEN N'@ModificationPercent'