Skip to content

Releases: erikdarlingdata/DarlingData

Updates_20260501 - May 2026 release

29 Apr 21:01
1a25760

Choose a tag to compare

May 2026 release. 8 substantive commits since v4.20. All 11 stored procs bumped to X.6 / 20260501.

New

sp_QueryReproBuilder — @query_plan_xml parameter

Feed a single query plan XML directly and skip Query Store entirely. Useful for iterating on a slow-to-parse plan in isolation.

When @query_plan_xml is supplied:

  • Database-existence and Query-Store-existence checks are skipped
  • The Query Store population region is bypassed (IF @query_plan_xml IS NULL)
  • A synthetic plan_id/query_id = -1 row is seeded into #query_store_plan, #query_store_query, #query_store_query_text, #query_store_runtime_stats, and #query_context_settings so synthetic rows can't collide with real Query Store data
  • Statement text is extracted from StmtSimple/@StatementText to feed #query_store_query_text
  • Parameter extraction, warnings, embedded-constants, and repro-build phases all run unmodified against the synthetic data

The generated repro's USE database; scaffold is gated on @database_name being supplied so synthetic-mode output doesn't emit USE NULL;.

sp_QuickieStore — @primary_window filter on @find_high_impact

Narrow @find_high_impact results to queries whose majority activity falls in a single window. Accepts any prefix of business / off-hours / weekend (case-insensitive — b / o / w is enough). Validated up front: errors out unless @find_high_impact = 1 and the value starts with b, o, or w. Filter applied in the final dynamic SELECT against the existing primary_window classification (its >50% rule). Queries whose primary_window is Spread are excluded by design.

sp_QuickieStore + sp_QuickieCache — resource_metrics XML rollup

The eight individual total_* columns plus max_dop in @find_high_impact (sp_QuickieStore) and twelve total/max columns in sp_QuickieCache's main result set are replaced with a single clickable resource_metrics XML column. Built natively with FOR XML PATH(N'metrics'), TYPE and attribute-path aliases — no STRING_AGG, no string concatenation. The XML also surfaces avg/min/max per-execution metrics that were previously computed but not projected.

Shape (sp_QuickieStore):

<metrics>
  <cpu total_ms avg_ms min_ms max_ms/>
  <duration total_ms avg_ms min_ms max_ms/>
  <physical_reads total_mb avg_mb min_mb max_mb/>
  <writes total_mb avg_mb min_mb max_mb/>
  <memory total_mb avg_mb min_mb max_mb/>
  <tempdb total_mb avg_mb/>
  <executions total/>
  <rows total avg/>
  <parallelism max_dop/>
</metrics>

Share columns (cpu_share, duration_share, etc.) remain dedicated sortable columns rather than folded into the XML. The underlying total_* storage is unchanged so debug dumps are unaffected.

sp_PressureDetector — average I/O size per file

Adds avg_read_kb and avg_write_kb to the file metrics output, computed from sys.dm_io_virtual_file_stats.num_of_bytes_read / num_of_reads (and write counterparts). Values flow through the snapshot temp table, the @log_to_table path, and both the snapshot and delta CTE branches.

A high avg_read_stall_ms with a small avg_read_kb points at random small reads (index seeks, lookups). The same stall with a large avg_read_kb points at large sequential pulls (read-ahead, scans, restores). Different remediations, same wait — surfacing the size disambiguates.

Fixes

sp_HumanEventsBlockViewer — chain-lead attribution for top blocking queries (#760 follow-up)

The "Top Blocking Query" rollup previously summed each blocked-process-report's victim wait against the direct blocker's sql_handle. In a chain A blocks B blocks C, B was reported as a blocker "responsible" for C's wait — but B was itself stuck behind A. Only A actually needs tuning.

This release rewrites the rollup so every BPR's victim wait cascades up to the chain's lead blocker (the level-0 session in the monitor loop):

  • New #session_leads temp table materializes a (monitor_loop, lead_desc, session_desc) map via a recursive CTE. Anchor rows are lead blockers (sessions never appearing as a blocked_desc in the same monitor loop); recursion walks downstream keeping lead_desc constant.
  • Cycle guard mirrors the existing hierarchy CTE pattern (lead_path LIKE check, MAXRECURSION 100).
  • Fallback pass inserts any blocking_desc not reached by the recursion as its own lead — catches true cycle cases (mutual blocking before deadlock detection fires) so their waits don't silently drop.
  • BPR-level filter application (rather than chain-level) keeps cross-object waits visible in the rollup.
  • Final finding renamed Top Blocking QueryTop Lead Blocker; finding text now reads "This lead blocker accounted for ... across N blocked sessions in its chain." to make the cascaded-attribution semantic explicit.

Intermediate blockers (sessions that blocked downstream but were themselves victims) no longer appear in the rollup — their apparent blocking time has been attributed to whoever's actually holding the lock at the top of the chain.

sp_IndexCleanup — uptime in header, accurate UDF detection

  • Append a Server uptime: N days clause to the run-date header row's consolidation_rule, so the day count is visible alongside per-row warnings rather than only in the summary block. Warning variant when uptime < 14 days.
  • Replace the LIKE '%].[%(%' heuristic that flagged computed columns and check constraints as "containing UDFs". The pattern fired on string literals containing ].[, schema-qualified system functions like [sys].[fn_xxx](), and OBJECT_ID literals that include schema-qualified names. Now uses sys.sql_expression_dependencies filtered to FN/IF/TF/FS/FT object types, joined to sys.objects for the clean [schema].[name] list.

sp_QuickieStore — Azure SQL DB conversion error in @expert_mode (#767, #771, #772)

On Azure SQL DB Hyperscale (and likely other tiers), EXEC dbo.sp_QuickieStore @expert_mode = 1 failed with Msg 8114 — Error converting data type nvarchar to bigint while inserting into #tuning_recommendations.

Root cause: the string-split path that parses sys.dm_db_tuning_recommendations.details (the pre-2017-compat fallback) returns regressed_plan_id and recommended_plan_id with a leading space — the REPLACE chain inserts ': ' after every colon, and the SUBSTRING math doesn't account for it. The implicit cast on insert into #tuning_recommendations was silently rescuing the value on the box product, but Hyperscale errored on the same shape.

  • #771 (@ClaudioESSilva): added TRY_CAST(... AS bigint) to the WHERE clause so the join survived the leading-space text.
  • #772: moved the type intent to the projection — regressed_plan_id / recommended_plan_id now leave the derived table already typed via TRY_CAST(LTRIM(SUBSTRING(...)) AS bigint). The redundant TRY_CAST in the WHERE clause is removed. Splitting math is left alone.

Thanks to @ravirajch for the report and the Hyperscale repro, and to @ClaudioESSilva for the diagnosis and patch.

Repo

  • Bug-report template now asks for @debug = 1 output.

v4.20 - 4/20 Code Review Sweep Release

20 Apr 15:58

Choose a tag to compare

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)

Updates_20260401 - First things first I'll eat your brains

28 Mar 14:42

Choose a tag to compare

April 1, 2026 Release

What's New

TestBackupPerformance 1.0 (new procedure)

  • Tests backup throughput across combinations of file count (striping), compression, buffer count, and max transfer size
  • Configurable stripe counts, iterations for averaging, and NUL device support for disk-free benchmarking
  • Results stored in a permanent table for historical comparison

sp_QuickieStore 6.4

  • New @find_high_impact parameter — automated diagnostic that identifies high-impact queries, plan instability, and workload concentration
  • Workload concentration summary in @find_high_impact output
  • Physical reads clarification in @find_high_impact output
  • Start/end dates displayed in local time for @find_high_impact
  • Fix: plan/object IDs no longer leak into query_id_list in @find_high_impact
  • Expert Mode output for sys.dm_db_tuning_recommendations (thanks @ReeceGoding — issue #483)
  • Narrow plan cache lookups with statement offsets for more precise plan matching

sp_QuickieStore @find_high_impact performance & output improvements

  • Performance overhaul: ~60s → ~10s on large Query Stores
    • Split 4-way DMV joins into staged temp table steps (interval IDs → runtime stats → plan rollup → query hash)
    • Pre-staged query_ids and plan_ids reused across representative text, time bucketing, identifiers, and plan fetch
    • Static SQL INSERT for scoring/diagnostics, separate OUTER APPLY for plans
    • EXISTS semi-join patterns for interval filtering and plan fetch
    • Clustered PKs on 7 temp tables for better optimizer estimates
    • Removed redundant maintenance filters and query_plan IS NOT NULL check
  • query_sql_text now uses processing-instruction XML format (clickable in SSMS)
  • top_waits column omitted when Query Store wait stats unavailable (SQL 2016 or waits not enabled)
  • @sort_order parameter now serves as tiebreaker when impact_score ties (defaults to cpu_share)
  • Representative text query rewritten to GROUP BY integer columns instead of nvarchar(max)

sp_HealthParser 3.4

  • Added MAXDOP 1 to all SELECT INTO queries for consistent performance

sp_IndexCleanup 2.4

  • Fix: merge bug that lost include columns in subset chains
  • Fix: unique constraints now recognized as superset targets in Key Subset detection (#721)
  • Fix: heap table index detection no longer misidentifies heaps (#727)
  • Fix: UNIQUE keyword preserved in merge scripts for unique indexes and constraints (#724)
  • Performance: stage dm_db_index_usage_stats into temp table, scoped to filtered objects (~4 min → ~10s)
  • Performance: replace ISNULL(t.schema_id, v.schema_id) schema join with sys.objects in all queries
  • Tests: adversarial test suite expanded to 26 assertions

Repo

  • Issue templates converted from markdown to YAML issue forms

Version Bumps

All procedures updated to x.4 / 20260401:

Procedure Version
sp_HealthParser 3.4
sp_HumanEvents 7.4
sp_HumanEventsBlockViewer 5.4
sp_IndexCleanup 2.4
sp_LogHunter 3.4
sp_PerfCheck 2.4
sp_PressureDetector 6.4
sp_QueryReproBuilder 1.4
sp_QueryStoreCleanup 1.4
sp_QuickieStore 6.4
TestBackupPerformance 1.0

Updates_20260301

02 Mar 16:10
f999f02

Choose a tag to compare

March 1, 2026 Release

What's New

sp_HealthParser 3.3

  • New @skip_waits parameter to skip the wait stats section
  • New @use_ring_buffer parameter to use ring_buffer target instead of file target for faster collection

sp_PerfCheck 2.3

  • Rework and README accuracy fixes

sp_PressureDetector 6.3

  • 14 new perfmon counters
  • Filter out zero-rate perfmon counters

sp_QuickieStore 6.3

  • Expert Mode now always shows plan hashes
  • Expert Mode T-SQL for plan forcing, unforcing, hint setting, and hint removal (thanks @ReeceGoding — PR #684)
  • Expert Mode output for sys.database_automatic_tuning_configurations (thanks @ReeceGoding — issue #661)

Version Bumps

All procedures updated to x.3 / 20260301:

Procedure Version
sp_HealthParser 3.3
sp_HumanEvents 7.3
sp_HumanEventsBlockViewer 5.3
sp_IndexCleanup 2.3
sp_LogHunter 3.3
sp_PerfCheck 2.3
sp_PressureDetector 6.3
sp_QueryReproBuilder 1.3
sp_QueryStoreCleanup 1.3
sp_QuickieStore 6.3

What's Changed

Full Changelog: Updates_20260215...Updates_20260301

Valentine's Day Massacre (of Bugs) 💘🐛

15 Feb 15:27

Choose a tag to compare

🎉 New Stored Procedure: sp_QueryStoreCleanup

Query Store collects a lot of noise — system DMV queries, index maintenance, statistics updates, DBCC commands — all cluttering up your Query Store and making it harder to find the queries you actually care about.

sp_QueryStoreCleanup identifies and removes duplicate and noisy queries from Query Store in any database on your server.

🔍 Text pattern matching to find system and maintenance noise
🧹 Hash-based deduplication (query_hash, plan_hash, or both)
🛡️ Queries with forced plans are always protected
📊 Report-only mode to preview before removing
⏳ Optional age filter to only remove stale queries

/* Preview what would be removed */
EXEC dbo.sp_QueryStoreCleanup
    @database_name = 'YourDatabase',
    @report_only = 1;

/* Remove the noise */
EXEC dbo.sp_QueryStoreCleanup
    @database_name = 'YourDatabase';

Full documentation: sp_QueryStoreCleanup README


🐛 32 Bug Fixes Across All 10 Stored Procedures

Two rounds of deep code review identified and fixed 32 logical bugs. Every fix was validated against SQL Server 2016, 2017, 2019, 2022, and 2025 — 50/50 installs and 50/50 executions, zero regressions.

sp_QuickieStore (5 fixes)

  • Fixed cursor skipping databases when @get_all_databases = 1 (missing FETCH NEXT/CONTINUE)
  • Fixed compile memory calculation inflated 8x (_kb columns already in KB, not pages)
  • Fixed log bytes calculation deflated ~95x (wrong divisor: 100000000 → 1048576)
  • Removed duplicate TRUNCATE statements
  • Added @@DATEFIRST warning when @workdays = 1 with unsupported regional settings

sp_PressureDetector (4 fixes)

  • Fixed missing SET LOCK_TIMEOUT -1 in @cool_new_columns = 1 branch
  • Fixed DATEDIFF divide-by-zero in perfmon total_per_second
  • Fixed sampled avg_ms_per_wait using incorrect averaging (now uses proper delta calculation)
  • Fixed @prefix NULL on Azure SQL causing query failures

sp_IndexCleanup (5 fixes)

  • Fixed LEN() trailing space trim causing off-by-one in comma-separated list truncation (switched to DATALENGTH)
  • Fixed t.max_length = -1 never matching for (max) columns (should be c.max_length)
  • Plus 3 additional fixes from prior review

sp_HumanEvents (4 fixes)

  • Fixed memory filter, CATCH cleanup, QUOTENAME schema/table handling

sp_PerfCheck (3 fixes)

  • Fixed NULL @processors causing bad detail strings
  • Fixed TOKENANDPERMUSERSTORE priority gap (BETWEEN 3 AND 5BETWEEN 2 AND 5)
  • Wrapped DMV queries in VIEW SERVER STATE guard

sp_QueryReproBuilder (2 fixes)

  • Fixed version gates from @new (SQL 2019+) to @sql_2017 for columns available since SQL 2017

sp_HumanEventsBlockViewer (2 fixes)

  • Fixed table mode XML handling and DATEADD overflow guard

sp_QueryStoreCleanup (1 fix)

  • Fixed COUNT_BIG(*) false positives → COUNT_BIG(DISTINCT) (eliminated 39% false positive rate)

sp_HealthParser (1 fix)

  • Fixed XPath inputbuf extraction

sp_LogHunter (1 fix)

  • Added archive > 0 guard

🔧 Additional Improvements

Round 2 Fixes (5 additional)

  • sp_HealthParser: Added missing N'' unicode prefix on dynamic SQL string
  • sp_HumanEventsBlockViewer: Fixed debug label mismatch (#blocking_sh#blocking_xml_sh)
  • sp_PerfCheck: GETDATE()SYSDATETIME() for uptime calculation consistency
  • sp_PressureDetector: Renamed misleading physical_reads column to logical_reads (source was der.logical_reads)
  • sp_PressureDetector: Fixed SUBSTRING start position off-by-one in debug PRINT

sp_PressureDetector

  • Filter out zero-rate perfmon counters from output

Tested on SQL Server 2016, 2017, 2019, 2022, and 2025

February 2026 Bug Fixes

06 Feb 20:31

Choose a tag to compare

Bug Fixes

This release includes bug fixes across multiple stored procedures discovered during code review.

sp_IndexCleanup (2.2.5)

  • Fixed rule prioritization for Key Duplicates of MAKE UNIQUE indexes (Rule 7.6)
  • Fixed MERGE scripts for non-compressible indexes (changed INNER JOIN to LEFT JOIN)
  • Added missing semicolons to generated scripts

sp_HumanEvents (7.2.5)

  • Fixed duplicate column name blocked_process_report in blocking output (renamed XML data column to blocked_process_report_xml)

sp_HumanEventsBlockViewer (5.2.5)

  • Version bump for consistency

sp_LogHunter (3.2.5)

  • Fixed RAISERROR format specifier mismatch (%i changed to %I64d for bigint variable)

sp_PerfCheck (2.2.5)

  • Fixed file growth calculation formula
  • Fixed COUNT_BIG to integer assignment mismatch
  • Fixed WHERE clause logic error
  • Added missing AS keyword in column alias

sp_PressureDetector (6.2.5)

  • Fixed column alias mismatch in UNION (total_avg_stall vs total_avg_stall_ms)
  • Fixed bit variable compared to string 'true' instead of 1

sp_QueryReproBuilder (1.2.5)

  • Fixed temp table #procedure_object_ids used before creation when wildcard procedure name used
  • Fixed SUBSTRING error in parameter extraction when query plans lack ParameterList XML
  • Added missing semicolon after END CATCH

sp_HealthParser (3.2.5)

  • Added table logging for blocking/deadlocks
  • Fixed data types
  • Added blocked process attributes

sp_QuickieStore (6.2.5)

  • Added total sort options

Happy New Year

28 Dec 14:52

Choose a tag to compare

  • Added @target_output parameter to sp_HumanEvents, if you want to use a file instead of the ring buffer.
  • Removed the geometry stuff from sp_QuickieStore. I haven't quite figured out how to make it work yet. Also removed some vestigial checks that are no longer required in Azure.
  • Tuned WhatsUpLocks to run less terribly when there are a lot of locks
  • Added compression type to WhatsUpIndexes
  • Added new 2025 memory pressure stuff to sp_PressureDetector and sp_PerfCheck
  • Did extensive work on sp_PerfCheck to fix a lot of stuff I was unhappy with. Difficult to list it all.
  • Did extensive work on sp_IndexCleanup to make it more reliable and less complicated.

You'll note that the version dates for these is January 15th. While I am releasing this now (early), I probably won't be back looking at this stuff until January 5th or so. Hopefully you won't either!

It's lonely out in space

12 Nov 14:38

Choose a tag to compare

What's Changed

Full Changelog: Updates_20250901...Updates_20251114

This release has a ton of tiny fixes and tweaks that I've been making to complement the training stuff I've been working on.

It also introduces a new stored procedure called sp_QueryReproBuilder, that uses Query Store data to build easy-to-execute parameterized repro queries. This is a bit of an alpha release, so please be gentle when reporting issues.

Back to School

05 Sep 16:19

Choose a tag to compare

What's Changed

New Contributors

Full Changelog: Updates_20250601...Updates_20250901

Updates_20250601

31 May 17:56

Choose a tag to compare

What's Changed

  • sp_QuickieStore: Appended _ms where it was missing for avg_cpu_time by @ReeceGoding in #600
  • sp_QuickieStore: Moved validation of @sort_order to be much earlier on. by @ReeceGoding in #602
  • sp_QuickieStore: Moved special sorting columns for special sort order values to be jus… by @ReeceGoding in #604
  • sp_QuickieStore: Made plan hash, query hash, or sql handle show when the parameter for filtering them out is passed in by @ReeceGoding in #608
  • sp_QuickieStore: Documents that @format_output = 1 removes most decimals. by @ReeceGoding in #606
  • Adding support for indexed views in addition to indexes on tables. by @FirstCall42 in #610
  • sp_QuickieStore: Move regression validation to just after sort order by @ReeceGoding in #612
  • Made sp_HumanEventsBlockViewer not error out when system_health is used by @ReeceGoding in #618

Full Changelog: Updates_20250501...Updates_20250601