Skip to content

v4.20 - 4/20 Code Review Sweep Release

Choose a tag to compare

View all tags
@erikdarlingdata erikdarlingdata released this 20 Apr 15:58
· 48 commits to main since this release
v4.20
446730e

April code-review sweep: 77 non-merge commits since Updates_20260401. All 11 stored procs bumped to X.5 / 20260420.

New

sp_QuickieCache (new proc)

Plan-cache companion to sp_QuickieStore. Surfaces the most resource-intensive plans in sys.dm_exec_query_stats with the same scoring dimensions QuickieStore uses for Query Store. Supports @find_single_use_plans and @find_duplicate_plans modes, @sort_order, @database_name scoping, and health-findings output aligned to the proc family.

sp_QuickieStore — log-to-table mode (#762)

sp_QuickieStore can now persist its output to permanent tables instead of returning it to the client. Point it at a monitoring database, schedule it, and build trendlines on top.

Five new parameters: @log_to_table, @log_database_name, @log_schema_name, @log_table_name_prefix, @log_retention_days. When @log_to_table = 1, results flow into nine logging tables following the sp_HealthParser logging pattern:

  • RuntimeStats — per-plan execution / CPU / duration / IO / memory / grants / tempdb / rows
  • CompilationStats — compile counts, compile CPU/duration, optimization levels
  • ResourceStats — per-interval resource consumption aggregated across plans
  • WaitStatsByQuery — wait-time breakdown per plan
  • WaitStatsTotal — workload-level wait summary
  • PlanFeedback — Query Store plan feedback rows (2022+)
  • QueryHints — stored hints applied to queries (2022+)
  • QueryVariants — parameter-sensitive plan variants (2022+)
  • QueryStoreOptions — the target database's Query Store configuration snapshot

Retention controlled by @log_retention_days (default 30). Dedup via @mdsql_template so repeated runs on overlapping windows don't double-insert. This is the biggest feature in the release — it turns sp_QuickieStore from an interactive tool into a monitoring building block.

sp_HumanEventsBlockViewer — top blocking queries (#760)

New finding that surfaces the queries that caused the most blocking damage, not just the sessions. Extracts the blocker's sql_handle from the execution stack, sums the wait time inflicted on blocked sessions, and reports each query's percentage of total blocking wait time. Deduplicates across monitor loops via MAX per victim transaction; only queries responsible for ≥ 10% of total blocking time are shown.

Fixes

sp_IndexCleanup

  • Rule 1 protects UNIQUE indexes from dedup via the primary-duplicate path
  • Rule 3 guards narrower unique indexes from supersession by wider unique covers
  • Rule 7 match fix
  • Rule 7.5 refuses to recommend changes that would break FK references

sp_QueryStoreCleanup

  • Reject READ_ONLY (state=1) and ERROR (state=3) databases before running

sp_QuickieStore

  • Wait-time column aggregations use MIN/MAX/AVG correctly (were all MIN)
  • Regression comparator weights by count_executions
  • Removed TOP (5) wait-stats per-interval pre-filter that dropped relevant waits
  • Treat as 2022-class when any 4 of 5 QS views exist (handles preview/CTP SKUs)
  • @regression_where_clause REPLACE fragility flagged

sp_PerfCheck

  • Deadlock check uses DATEDIFF(SECOND, ...) (was MILLISECOND; overflowed on sub-day uptime)
  • LPIM recommendation gated off Azure MI and AWS RDS (LPIM not available there)
  • dm_os_memory_health_history read gated on VIEW SERVER STATE
  • Stolen-memory counter filter tightened
  • Added DSC configuration_id = 17 (ISOLATE_SECURITY_POLICY_CARDINALITY)
  • check_id 1002 (max memory near physical RAM) priority 1002 → 20
  • Pagelatch/uptime scalar assignments split into two SELECTs (avoids self-reference)

sp_HumanEvents

  • @seconds_sample = 0/NULL coerced to 1
  • Cleanup LIKE anchored to this proc's session prefix only
  • Wait-type filter no longer truncated by spurious SUBSTRING(..., 0, 8000)
  • @gimme_danger now honored in table-logging wait insert
  • View-recreation guard logic un-inverted

sp_HumanEventsBlockViewer

  • Recursive blocking-tree CTE guards against blocker→blocked cycles
  • database_name / currentdbname normalized to sysname
  • RTRIM(int) replaced with CONVERT(nvarchar, int)
  • @target_type = 'ring_buffer' compare made case-insensitive
  • @timestamp_column UTC convention documented
  • event_file auto-detect preference over ring_buffer documented as intentional

sp_PressureDetector

  • Decimal precision preserved in size/memory GB arithmetic
  • Sample-mode percent_signal_waits computed from the raw delta

sp_LogHunter

  • Double quotes escaped in @custom_message
  • @custom_message_only validation + canary date fallback

sp_HealthParser

  • @pending_task_threshold honored in scheduler shreds
  • Deadlock XE filter: OR → AND between @dbid and @database_name
  • #tc wait average weighted by waits count
  • 2017+ XE time filter aligned to half-open interval

Cross-cutting

  • SUBSTRING(@sql, N, M) length-vs-endpos chunk-print bug fixed across multiple procs in debug output
  • ORDER BY ap.parameter_id added to @help output across all procs
  • Arithmetic overflow in high-impact wait stats formatting fixed

Repo

  • GitHub Sponsors funding link added
  • README shields.io badges (repo + social)
Assets 2
Loading