Updates_20261001 - The Nightmare Before Compaction

sp_QueryStoreCleanup got most of the new features this month. A long removal can now run in two sessions, the procedure can compact Query Store's internal tables afterwards, and report mode returns a summary. Most of the other procedures got bug fixes from a round of reviews.

Changes in behavior

• sp_QueryStoreCleanup: with a text filter, deduplication now removes only the queries that match the filter. Before, it removed every copy of a duplicated hash, including copies that did not match. (#893)
• sp_QuickieStore: the wait stats total result set and the WaitStatsTotal log table showed sums in the avg, min, max, and last columns. They now show an average, a minimum, a maximum, and the latest value. In regression mode, each period gets its own row. Rows that older versions logged keep their old values, so do not compare avg, min, max, or last across the upgrade. (#894)
• sp_QuickieStore and sp_QueryReproBuilder: wait stats now cover the whole search window. Before, they came only from the latest interval of each plan, and they skipped waits with a minimum of 0 ms. Expect higher wait numbers. (#890)
• sp_QuickieCache: @find_duplicate_plans returns one row for each query hash in each database. Before, it merged the databases that ran the same query text into one row. (#880)
• sp_LogHunter: a @start_date later than @end_date now raises an error. Before, the procedure returned an empty result that looked like a search with no matches. (#878)

sp_QueryStoreCleanup

This procedure is not in the combined installer. Install it from its own folder.

• New @sort_direction (ASC or DESC) sets the removal order by query_id. Two sessions can work one removal list from opposite ends. On a Query Store with about 800,000 queries, one session removed 14.6 queries a second, and two sessions removed 20.2. (#875)
• New @compact_tables. After the removal, it compacts each index on Query Store's internal tables with DBCC INDEXDEFRAG, and it reports each size before and after. It also runs on a READ_ONLY Query Store, where it skips the removal. (#885, #886)
• @report_only = 1 returns a summary row and one row for each query hash, not one row for each query. (#882)
• The text search runs under a binary collation and still ignores case. On about 745,000 queries, a run with the default targets took 37 seconds instead of 154. (#882, #887)
• Queries with a forced Query Store hint are protected from removal, as queries with a forced plan already were. (#867)
• On SQL Server 2022 and later, the procedure removes PSP parent queries after their variants. It skips a parent while its variants are still in Query Store. (#883, #884)
• @min_age_days rejects 0 and negative values. (#870)
• The removal list keeps its HASH JOIN hint at every compatibility level. (#867, #887, #890)
• The procedure has its own test harness, and CI runs it. (#889)

sp_QuickieStore

• Regression mode no longer multiplies execution counts and totals. Each period now has its own last values and its own waits. (#890)
• The WaitStatsByQuery and WaitStatsTotal log tables get a from_regression_baseline_time_period column. The procedure adds the column to existing tables. (#892, #894)
• With @get_all_databases = 1, some joins matched on plan_id or query_id but not on the database. When two databases had the same IDs, their rows mixed. (#873)
• The text search runs under a binary collation, and @escape_brackets works with it. A @query_text_search run on about 745,000 queries took 104 seconds instead of 172. (#882)

sp_QueryReproBuilder

• Wait stats use AVG, MIN, and MAX where they used SUM. (#877)
• The SET DATEFORMAT line in the generated script named the wrong format, because the mapping was off by one. (#877)
• A connection with all tracked SET options off no longer produces an invalid SET ON; line. (#877)

sp_QuickieCache

• NULL parameters take their defaults. Before, @top = NULL failed with Msg 1014, and a NULL threshold returned no queries. (#895)
• @database_name = N'master', or another system database, now searches that database, even with @ignore_system_databases = 1. (#895)
• Percentile ranks compare each query with the whole workload, not only with the other candidates. (#876)
• @find_duplicate_plans uses @start_date and @end_date, as the other modes do. (#876)
• object_name is quoted the same way for every query type. (#879)

sp_HealthParser

• The wait counts in "waits by count" were about 12 times too high with the default 60-minute buckets. Each system_health snapshot reports counts since the server started, and the procedure added the snapshots together. It now takes the latest count in each bucket. (#888)
• "Waits by duration" returns one row for each wait type in each bucket. (#869)

sp_HumanEvents

• @session_id_filter and @username_filter removed every blocked process report, because the report comes from a system session. They no longer apply to the blocking event, and the procedure tells you so. (#871, #888)
• @cleanup = 1 no longer creates and starts a session only to remove it. (#871)
• @skip_plans = 1 with table logging failed with Msg 102 for query sessions. (#881)
• The blocking chain query has a cycle guard. (#881)

sp_PerfCheck

• The file I/O checks show the server name for a UNC path. Before, every UNC path showed as \\. (#874)
• The Query Store size check showed the wrong message for a size from 1000 to 1023 MB. (#874)
• The auto-growth check showed empty details for a file that is not a data or log file. It now checks only data and log files. (#874)
• The help says that the nine thresholds accept 0. (#897)

sp_PressureDetector

• The wait stats results show max_wait_time_ms. The procedure adds the column to an existing _Waits log table.

Docs and CI

• The root README lists 12 parameters that it did not list before. (#896)
• CI installs sp_QuickieCache and runs it in each of its modes. (#895)

---

All eleven procedures move to .10, version date 20261001. That includes sp_QueryStoreCleanup, which moves from 1.6 to 1.10. (#898)