…-plan

sp_QuickieStore: optimize representative text and plan fetch in @find_high_impact

…un after #hi_interesting

Steps 2b/2c (query_id staging + representative text) were placed before
Step 2 (top N → #hi_interesting), so #hi_id_staging_queries joined an
empty #hi_interesting table. This caused NULL primary_window,
query_id_list, and empty query_sql_text.

Moved Steps 3b/3c to after Step 3 (scoring) where #hi_interesting is
populated.

Tested on SQL2022 and SQL2016 — all columns populated correctly.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

sp_QuickieStore: fix @find_high_impact step ordering

- Remove maintenance NOT EXISTS subquery from Step 1 aggregate and
  Step 3c representative text (~12s savings). These hashes are already
  filtered out by the main procedure's maintenance exclusion before
  reaching @find_high_impact.
- Remove query_plan IS NOT NULL from the final OUTER APPLY (~5s savings).
  TRY_CONVERT handles NULL plans gracefully in the output.

Tested on SQL2022.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

…filters

sp_QuickieStore: remove redundant filters from @find_high_impact

The OUTER APPLY correlated on query_hash through query_store_query
for each output row. Now uses #hi_id_staging_queries (already populated)
to skip query_store_query entirely — 1 DMV instead of 2, correlating
against a known-small temp table.

Tested on SQL2022.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

…aging

sp_QuickieStore: use staged query_ids in final plan OUTER APPLY

The OUTER APPLY was joining #hi_id_staging_queries → query_store_plan
by query_id. Now uses #hi_id_staging_plans which has the plan_ids
directly, allowing a PK seek on query_store_plan instead of a
query_id join.

Tested on SQL2022.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

sp_QuickieStore: use plan_id PK seek in final OUTER APPLY

The JOIN pattern in the OUTER APPLY wasn't getting a good plan.
EXISTS with the semi-join against #hi_id_staging_plans lets the
optimizer pick a better strategy for the correlated subquery.

Tested on SQL2022.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

sp_QuickieStore: use EXISTS in final plan OUTER APPLY

The initial aggregate query joined all 4 Query Store DMVs together
(qsq → qsp → qsrs → qsrsi), causing the optimizer to pick bad plans
with TVFs taking 8+ seconds.

Split into two stages following the pattern used elsewhere in the proc:
- Step 1a: qsrs + qsrsi → #hi_plan_stats (aggregate to plan_id level)
- Step 1b: #hi_plan_stats + qsp + qsq → #hi_query_stats (roll up to
  query_hash, driving from a small pre-aggregated temp table)

Tested on SQL2022 (StackOverflow2013) and SQL2016 (StackOverflow2010).

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

…gate

sp_QuickieStore: split Step 1 aggregate to avoid 4-way DMV join

Step 1a now stages matching runtime_stats_interval IDs into
#hi_intervals (tiny result set from 1 DMV), then Step 1b aggregates
runtime_stats with EXISTS against the temp table instead of joining
the two DMVs directly.

Tested on SQL2022 and SQL2016.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

sp_QuickieStore: stage interval IDs for @find_high_impact runtime stats

Adds clustered primary keys to 7 temp tables to give the optimizer
better cardinality and join strategy information:
- #hi_plan_stats (plan_id)
- #hi_query_stats (query_hash)
- #hi_interesting (query_hash)
- #hi_primary_window (query_hash)
- #hi_query_waits (query_hash)
- #hi_query_identifiers (query_hash)
- #hi_output (query_hash)

Tables without PKs are those with composite keys or multiple rows per
hash (staging tables, time_buckets, wait_staging, representative_text).

Tested on SQL2022 and SQL2016.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

sp_QuickieStore: add clustered PKs to @find_high_impact temp tables