Background
Erik asked for a real performance-tuning pass on plan_correction's own SQL (#2717's investigation had concluded "no further tuning headroom" after the #2673 temp-table-staging fix, and #2720 only detached the collector from the sequential body -- it never touched the query's own cost). This issue is that follow-through.
Live evidence (2026-09-01, multi-45, database Demo, via Query Store)
plan_correction's actual shipped query (query_id 42500238, matching PlanCorrectionCollector.PayloadBodyText's second SELECT):
- execution_count: 28 (5.8h window)
- avg_duration_ms: 5,004.65
- avg_cpu_ms: 4,237.21 -- 85% of duration is CPU, not waits/IO
- avg_logical_reads: 381.5 -- trivial IO for a 5-second query
- avg_rowcount: 18
381 logical reads and 18 output rows cannot account for 4.2 seconds of CPU through normal execution -- this is a compile-cost signature, not an execution-cost signature.
Root cause
Both statements in PayloadBodyText carry OPTION(RECOMPILE). But this query:
- Takes zero runtime parameters -- it's built and executed as static literal T-SQL (sp_executesql with no parameter placeholders), so there is nothing for RECOMPILE to protect against parameter-sniffing on.
- Reads sys.dm_db_tuning_recommendations, which the collector's own doc comment already states has no statistics -- the optimizer uses a fixed heuristic guess, not a histogram-driven estimate, so a cached plan's cardinality assumption doesn't go stale the way a stats-driven plan would.
- Processes a set the same doc comment describes as "invariably small" -- the engine only keeps a handful of live recommendations -- so plan shape has no meaningful sensitivity to data volume across executions, servers, or time.
RECOMPILE here buys nothing and costs a full compile of a multi-JOIN, multi-CROSS-APPLY, JSON_VALUE-heavy query on every database, on every server, every cycle -- the exact pattern OPTION(RECOMPILE) exists to protect against in other collectors, applied to a query with none of the risk factors RECOMPILE is for.
What I checked and could NOT verify
- Could not pull the live execution plan for this exact query_id/plan_id via analyze_query_store_plan -- the store returned "No stored Query Store plan found... Query Store plan capture may be disabled for this database, or the plan has been purged". So this fix is based on the CPU/duration/reads/rowcount signature and the query's structural properties (zero params, no-stats DMV), not a plan-level compile-time breakdown SQL Server doesn't expose separately from execution time for a RECOMPILE'd query.
- Fleet-wide total_sql_ms for plan_correction was NOT yet trending down as of 2026-09-01 ~19:00 UTC despite Erik confirming the upstream application-side decimal-parameter-instability fix is already merged and rolled. Separate, real observation -- worth re-checking get_collector_cost(collector_name=plan_correction) again in a day or two once old bloated Query Store data has aged out.
Fix
Remove OPTION(RECOMPILE) from both statements in PlanCorrectionCollector.PayloadBodyText, letting the plan cache and reuse a single compiled plan across every database/server/cycle instead of paying full compilation cost every single time. Low risk, trivially revertible, purely a plan-caching change -- no functional/output difference.
Background
Erik asked for a real performance-tuning pass on plan_correction's own SQL (#2717's investigation had concluded "no further tuning headroom" after the #2673 temp-table-staging fix, and #2720 only detached the collector from the sequential body -- it never touched the query's own cost). This issue is that follow-through.
Live evidence (2026-09-01, multi-45, database Demo, via Query Store)
plan_correction's actual shipped query (query_id 42500238, matching PlanCorrectionCollector.PayloadBodyText's second SELECT):
381 logical reads and 18 output rows cannot account for 4.2 seconds of CPU through normal execution -- this is a compile-cost signature, not an execution-cost signature.
Root cause
Both statements in PayloadBodyText carry OPTION(RECOMPILE). But this query:
RECOMPILE here buys nothing and costs a full compile of a multi-JOIN, multi-CROSS-APPLY, JSON_VALUE-heavy query on every database, on every server, every cycle -- the exact pattern OPTION(RECOMPILE) exists to protect against in other collectors, applied to a query with none of the risk factors RECOMPILE is for.
What I checked and could NOT verify
Fix
Remove OPTION(RECOMPILE) from both statements in PlanCorrectionCollector.PayloadBodyText, letting the plan cache and reuse a single compiled plan across every database/server/cycle instead of paying full compilation cost every single time. Low risk, trivially revertible, purely a plan-caching change -- no functional/output difference.