Updates_20261101 - I Know What You Queried Last Summer

This release makes the averages in sp_QuickieStore and sp_QueryReproBuilder match Query Store, and it adds a new @top parameter to sp_QueryReproBuilder. sp_IndexCleanup no longer disables an index that backs a foreign key, and it judges unused indexes on each database's own usage window. Two patch sets (164 patches, with review fixes) corrected smaller problems in nine procedures and made sp_QuickieStore, sp_QueryReproBuilder and sp_HealthParser faster.

Changes in behavior

• sp_QuickieStore and sp_QueryReproBuilder: average columns are now weighted by executions. Query Store keeps one row for each plan in each time interval, and each row holds an average for that interval. The procedures used to average those averages, so an interval with 1 execution counted as much as an interval with 99. The total_ columns are the average times the executions, so they were wrong by the same factor. In one test, a plan with 1 execution at 2,282 ms CPU and 99 at 74 ms showed an average of 1,177.91 ms. It now shows 96.10 ms, and the totals match Query Store. Wait averages use the same weighting. The average sorts (cpu, duration and the others) now rank plans by the weighted average, so the default sort can return different plans. (#922, #927)
• sp_QuickieStore: the executions sort and every total sort now rank plans by the sum over all intervals. Before, they used the busiest single interval. The wait category sorts (lock waits, cpu waits and the others) also sum over the window. @wait_filter no longer cuts the list to @top plans before the other filters run. The main query applies @top once, after all filters. Expect different plans from these sort orders. (#922)
• sp_QuickieStore and sp_QueryReproBuilder: dates are read in one way. A date with no offset, or with +00:00, is server local time. A date with any other offset is that exact moment. Before, a date with its own offset was shifted by the server's UTC difference a second time. On a server that is not on UTC, that moved the search window. A caller who writes +00:00 to mean UTC now gets server local time. sp_HealthParser follows the same rule and no longer reads a date with no offset as UTC. sp_HumanEventsBlockViewer takes datetime2 and still drops offsets. (#922)
• sp_QuickieStore and sp_QueryReproBuilder: num_physical_io_reads is now a count of read operations. The old columns multiplied it by 8 KB and called it MB. The columns are now avg_, last_, min_, max_ and total_num_physical_io_reads. sp_QuickieStore also has total_num_physical_io_reads_by_query_hash. An existing _RuntimeStats log table gets the new columns. The old _mb columns stay, and they stay NULL for new rows. sp_QueryReproBuilder has no log tables. (#922)
• sp_QuickieStore: the last_, min_ and max_ columns for time and memory, and the memory grant totals, were bigint and cut small values to 0. They are now float. executions_per_second is a decimal, so a plan that runs less than once a second no longer shows 0. New log tables get the new types. A log table that you created earlier keeps its bigint columns and cuts the values to whole numbers. (#922)
• sp_QuickieStore: @sort_order = 'plan count by hashes' returns one row for each query hash. Before, it returned every plan of the top hashes. In one test that was 13,303 rows for 10 hashes. The row shows the plan with the most executions in the window, then the most recent plan, then the highest plan_id. New columns count the plans, query ids, query texts and other items for each hash. plan_count_reasons explains in words why a hash has so many plans. (#922)
• sp_QuickieStore and sp_QueryReproBuilder: execution_type_desc names every execution type that the row covers, for example Regular, Exception. Before, a plan with both regular and failed executions showed only Regular. (#922)
• sp_QuickieStore: with @regression_comparator = 'absolute' and a wait sort, the change column is now in milliseconds. It was divided by 1,000. With @find_high_impact = 1, the writes list now holds only queries that wrote data, so it can be shorter than @top. (#922, #926)
• sp_QueryReproBuilder: new parameter @top limits the run to the most recently executed plans. The default is 10 plans. If you pass @include_plan_ids, @include_query_ids, @include_query_hashes, @include_plan_hashes, @include_sql_handles or @procedure_name, the default returns every matching plan. An explicit @top always applies. To get more than 10 plans from a plain run, pass a larger @top. A value below 1 returns a message. (#922, #926)
• sp_QueryReproBuilder: new parameter @include_maintenance (bit, default 0). Index and statistics maintenance plans (index builds, statistics updates, StatMan, DBCC) are now left out of the plan list, as in sp_QuickieStore. They stay in when you name plans, queries or a procedure, or set @include_maintenance = 1. (#926)
• sp_QueryReproBuilder: @top and @include_maintenance sit after @query_text_search_not and before @query_plan_xml. A call that passes parameters by position past @query_text_search_not now sends its values to the wrong parameters. Use parameter names. (#922, #926)
• sp_QueryReproBuilder: the generated script writes all seven SET options (ANSI_PADDING, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT and NUMERIC_ROUNDABORT) as ON or OFF. Before, it wrote only the options that were ON. A script pasted into SSMS now runs with the settings that Query Store captured, and not the SSMS defaults. (#922)
• sp_HealthParser: an unknown @what_to_check value raises an error. Before, any typo ran all, the slowest check. @warnings_only = 1 no longer filters blocking. The separate pending task result set is gone. Pending task entry points now show in "cpu task details", with new columns pending_user_requests and pending_entry_points. An existing cpu task log table gets the columns on the first logging run. Pending task rows now follow @warnings_only. The memory conditions output no longer has the name column, which always held the same value. (#922, #926)
• sp_HumanEvents: the name of a one-shot session now ends with the spid of the run, as in HumanEvents_<event_type>_<guid>_<spid>. A running session is dropped only when that spid no longer runs sp_HumanEvents. @cleanup = 1 without an output database and schema now raises an error. Before, it skipped the cleanup and started a normal capture. (#922)
• sp_HumanEvents: the logged HumanEvents_Queries view averaged NULLs as zeros, so every average was half its real value when plans were collected. It is corrected. HumanEvents_Queries_np now returns one row for each pair of hashes. Existing installs get the new view text through the procedure's view update step. Do not compare these averages across the upgrade. (#922)
• sp_HumanEventsBlockViewer: @target_type accepts file and ring buffer, trims spaces, and raises an error for an unknown value. Before, an unknown value read nothing and reported "0 total events". Setting only one of @target_table and @target_column now selects table mode, and the validation then fails. Before, the parameter was ignored. (#922)
• sp_IndexCleanup: the procedure never emits a DISABLE for an index that backs a foreign key. The KEPT - FOREIGN KEY row shows an index that the procedure kept for this reason. The pair scoring now includes foreign keys and IGNORE_DUP_KEY, so the choice between two duplicates no longer depends on the index name. (#902, #913, #916, #917, #920)
• sp_IndexCleanup: the DATABASE summary row has new columns usage_since and usage_since_reason. Unused index rules now use the latest of the server start, the last restore and the creation date of each database. A database with 7 days or less of usage skips the unused index rule, and the other databases in the run do not. The TABLE rows divide daily_write_ops_saved, daily_lock_waits_saved and daily_latch_waits_saved by that window and not by the server uptime. (#907, #925)
• sp_IndexCleanup: the output has fewer MERGE SCRIPT rows, because a merge that changes nothing is no longer emitted. The per-partition compression scripts now give one row for each partition. (#906, #914, #922)
• sp_PerfCheck: check 7103 now reports a log growth increment over 64 MB, and a log with auto-growth off. It no longer reports 1 MB to 64 MB. Check 6001 needs 60 seconds of total wait time before a long average wait counts. Check 5003 rates DBCC FREEPROCCACHE for a single plan handle as Informational. Check 3003 counts only files with more than 1000 reads or writes, and it reports total stall over total I/O. Check 6101 (signal wait ratio) now leaves idle waits out, so it can fire on SQL Server 2019 and later. (#922)
• sp_PressureDetector: the perfmon panel shows per-second rates only for cumulative counters, so gauges such as Total Server Memory no longer show a per-second value. In the default file panel, total_avg_stall_ms is total stall over total I/O. It was the sum of the read and write averages. The tempdb panel shows the current file sizes and not the startup sizes. (#922)
• sp_QuickieCache: the resource shares and the grant totals use corrected totals, so shares are higher than before. @find_single_use_plans returns one row for each plan, and statement rows are grouped by database as well as query_hash. (#922)

sp_QuickieStore

Bug fixes (items under Changes in behavior are not repeated here):

• With @get_all_databases = 1, rows were lost when two databases had the same plan_id, in the results and in the log tables. Expert mode lost compilation stats rows in the same way. The feedback, hints and variants result sets now order the same way on every run. (#922, #926)
• On SQL Server 2022, @procedure_name and @query_type = 'proc' find the variants of a Parameter Sensitive Plan. Before, they returned "#query_store_runtime_stats is empty". object_name shows the parent procedure for a variant. (#922)
• Include and ignore lists: a space separates two values (50 51 used to become 5051), and a line break or tab separates values too. A list that grows past 4,000 characters once expanded no longer fails with Msg 9400. A value that is not a number is named in the message. (#922, #926)
• With @escape_brackets = 1, the escape character in the search text is escaped, so a search for a Windows path matches. The procedure accepts a quote or NULL as @escape_character, and _, [ and ] work as the escape character. (#922)
• A NULL @log_table_name_prefix uses the default prefix. Before, the run returned nothing and logged nothing. A negative @top is rejected up front. (#922)
• The footer lists a database that the run skipped as skipped. The message about a database in both include and exclude lists keeps the last letter of the name. (#922)
• Ties are broken the same way on every run, so a rerun returns the same rows. (#922)

Performance:

• @find_high_impact = 1 went from 12.3 seconds to 2.0 seconds, and plan count by hashes went from 45 seconds to 1.4 seconds on the author's test server. The default run uses about 20% less CPU. (#926)
• Most of the gain comes from reading each Query Store view once with a hash join, where nested loops read it again for every row. The maintenance plan filter tests only the start of the text, and the plan, query and text temp tables get clustered keys. (#922, #926)

sp_QueryReproBuilder

Bug fixes:

• Repro scripts for Parameter Sensitive Plan variants now run. The procedure removes the PLAN PER VALUE hint and warns with the name of the parent query. It removes the hint only for real variants. @procedure_name finds variants, and object_name shows the parent procedure. Before SQL Server 2022 nothing changes. (#922, #926)
• A plan that ran in the window and again after @end_date is no longer dropped. (#922)
• Parameter values: the procedure reads the last ParameterList of a plan. For a multi-statement plan, it reads the first statement's list. It declares plan parameters when the text has no (@...) list. Before, these cases gave ? values or Msg 102. (#922)
• A query text with a control character no longer fails the run with Msg 6841. The procedure writes SET LANGUAGE with a quoted name, and it writes datetime values that work under SET DATEFORMAT dmy. (#922)
• Lists accept line breaks and tabs, and they can expand past 4,000 characters. A bad value in a list is named in the message. all_plan_ids is in numeric order, so 9, 10 and not 10, 9. (#922, #926)
• New warnings cover StatMan and full-text population queries, which only SQL Server can run, and a parameter value that is probably cut off. A dispatcher plan warning applies only to a real dispatcher plan. (#922)

Performance:

• On the author's large test database, a run for every plan went from 95 seconds to 41 seconds. The new default of 10 plans takes 1.5 seconds. The gains come from reading each Query Store view once, converting each plan to XML once, and finding text positions with one search. (#926)

sp_HealthParser

Bug fixes:

• "Available plans for deadlocks" never returned a row. It now finds the cached plans, and each plan is listed once. (#922)
• Severe errors with database_id 0 no longer show the database of the caller. The deadlock database_name comes from the deadlock report, then DB_NAME(database_id), then UNKNOWN. (#922, #926)
• The first logging run covers the whole requested window. On a server with a negative UTC offset, it covered only about the last 19 hours of the default 7 days. (#922)
• The "ring buffer output was cut off" warning prints only when the window reaches past the newest event that came back. executions_per_second is a decimal. (#922, #926)
• Rows of one blocked process report that have the same time come out in spid and ecid order. Memory broker rows and severe errors have a fixed order for ties. (#926)

Performance:

• The default run went from 27 seconds to 8 seconds of CPU, and from 42 seconds to 12.5 seconds elapsed, on the author's test server. The procedure now reads the system_health files once for most areas and reads event values with plain paths. (#926)

sp_HumanEvents

Bug fixes:

• A second run no longer drops the first run's live session. The @debug output prints the whole session DDL in tiles of up to 4,000 characters. (#922)
• Blocking results pair each blocker's query text with its own plan. The grant columns in the blocking "available plans" result were 8 times too large. (#922)
• @client_app_name and @client_hostname now apply to the plan event. Before, plans were collected for every application and host. (#922)
• The database name of a blocked process comes from the event first. A parallel worker report with database_id 0 no longer shows the caller's database. (#922)
• With @skip_plans = 1, the results show one row for each query. (#922)

Other:

• New warnings cover a truncated ring buffer and a blocking @object_name that misses row and page locks. Blocking output has a fixed order for rows with the same time. (#922, #926)

sp_HumanEventsBlockViewer

• The header counts the reports that are shown. It counted blocker rows, so a report with two blockers counted twice. (#922)
• A report with two blockers pairs each blocker's frames with its own process. A database name comes from the report, so a report with database_id 0 no longer names the current database. (#922)
• A misspelled @database_name returns a warning. A NULL @log_table_name_prefix takes the default. Before, nothing was logged and no error came back. (#922)
• The empty-result header shows the window in local time. executions_per_second is a decimal. Output rows with the same time are in spid and ecid order. (#922, #926)

sp_IndexCleanup

Bug fixes:

• A unique index no longer loses to a non-unique duplicate. Every loser points at the final winner, and the Supersedes text names the correct indexes. (#918, #919)
• Rule 7 no longer matches a unique constraint against itself. The filtered-index finder matches whole bracketed names, so [ID] no longer matches inside [SiteID]. Rule 8 needs both key sets to be identical. (#903, #904, #908, #915)
• On an availability group secondary, the no-access message tells you to use ApplicationIntent=ReadOnly or to run on a readable secondary. It no longer advises CREATE USER and GRANT for these cases. (#910, #915)
• Rebuild scripts keep IGNORE_DUP_KEY and STATISTICS_NORECOMPUTE. They also keep page locks, row locks and incremental statistics. The output carries IGNORE_DUP_KEY to a kept index, so inserts of duplicate keys keep being skipped. Before, those inserts failed with errors 2601 or 2627 in some cases. (#905, #914, #916, #920, #922)
• If the foreign key guard keeps a loser, the winner no longer gets a MERGE SCRIPT for the includes of an index that stays. (#914)
• Scripts: a clustered columnstore table gets ONLINE = OFF (Msg 35352). A per-partition compression script no longer names FILLFACTOR (Msg 155). A filtered-index script no longer lists the filter column twice (Msg 1909). (#922)
• Advisory findings from every database stay in a multi-database run. Before, only the last database's findings survived. (#922)

Other:

• Unused index detection uses each database's own usage window (#907, #925). On an availability group primary with readable secondaries, the DATABASE row notes that reads on the secondaries are not counted.
• The test harness rule_coverage_test.py no longer depends on the server uptime. (#914)

sp_PerfCheck

• A dropped database in the default trace no longer ends the run with Msg 515. The name falls back to database_id <id>. (#922)
• The tempdb file checks, including 2004, read the current file sizes and not the sizes at startup. (#922)
• Check 7006 no longer tells you to enable Query Store in master or tempdb. Check 7008 sees the settings of a database that AUTO_CLOSE has shut. The CPU line counts physical cores from sockets times cores per socket. (#922)
• The README rows for 7103, 5003 and 6001 now match the code. (#928)

sp_PressureDetector

• The @troubleshoot_blocking edge list ignores the negative blocked values (-2, -3, -4), so a lead blocker's chain is not dropped. A blocked parallel query is not listed as a lead blocker, and each blocked session is listed once. (#922)
• In @sample_seconds mode, the procedure keeps counters that are 0 or steady, and it applies @minimum_disk_latency_ms. The memory grant panel shows the database that the request runs in. (#922)
• The @help text for @log_retention_days says that 0 keeps logs indefinitely. (#928)

sp_QuickieCache

• @find_duplicate_plans shows the statement text and not the whole batch. The Plan Duplication check counts each plan once. Before, a batch with many statements pushed the percentage past 999.99 and failed with Msg 8115. (#922)
• The Plan Duplication check counts plans for each database. The grant totals use total_grant_kb and not max_grant_kb. (#922)

Other

• Export-SqlResults has a new switch, -ReadOnlyIntent. It sets ApplicationIntent=ReadOnly for an availability group secondary that allows only read-intent connections. (#909, #911)
• CI skips the SQL Server test matrix when a pull request changes no SQL, tests or install files. A new summary job named SQL Tests reports the result. (#912, #923)
• The README files had 92 problems, and #928 fixes them. The @help text changed for sp_HealthParser, sp_HumanEventsBlockViewer, sp_PressureDetector, sp_QuickieStore and sp_QueryReproBuilder. The sp_QueryReproBuilder text now describes how dates with an offset are read, like sp_QuickieStore. (#928)
• Thank you to @stroexd, who opened #917 and wrote #913 and #920 for sp_IndexCleanup.
• The deadlock demo in two presentations now loops until the deadlock happens. Before, the loop counted rows and not passes, and it ended too early. (#921)

---

All ten bundled procedures move to minor version .11, with @version_date 20261101. sp_LogHunter moves with them, and its only change is a README fix. sp_QueryStoreCleanup, ProtectSession and TestBackupPerformance are unchanged. (#929)