Plan değişimi P429

(@srv int,@dbName nvarchar(4000),@hash varbinary(8),@from datetime,@to datetime)SELECT q.ObjectName, SUM(r.count_executions) AS Executions, SUM(r.avg_cpu_time * r.count_executions) / 1000.0 AS TotalCpuMs
FROM perf.QdsQuery AS q
JOIN perf.QdsPlan AS p ON p.ServerID = q.ServerID AND p.DatabaseName = q.DatabaseName AND p.query_id = q.query_id
JOIN perf.QdsRuntimeStats AS r ON r.ServerID = p.ServerID AND r.DatabaseName = p.DatabaseName AND r.plan_id = p.plan_id
WHERE q.ServerID = @srv AND q.DatabaseName = @dbName AND q.query_hash = @hash
  AND r.IntervalStartUtc >= @from AND r.IntervalStartUtc < DATEADD(day, 1, @to) AND r.execution_type = 0
GROUP BY q.ObjectName
ORDER BY TotalCpuMs DESC

Plan change case #P429 | DB: DB-11 | Improvement | Confidence: Medium (review pending)
Query: (@srv int,@dbName nvarchar(?),@hash varbinary(?),@from datetime,@to datetime)SELECT q.ObjectName, SUM(r.count_executions) AS Executions, SUM(r.avg_cpu_time * r.count_executions) / ? AS TotalCpuMs FROM perf.QdsQuery AS q JOIN perf.QdsPlan AS p ON p.ServerID = q.ServerID AND p.DatabaseName = q.DatabaseName AND p.query_id = q.query_id JOIN perf.QdsRuntimeStats AS r ON r.ServerID = p.ServerID AND r.DatabaseName = p.DatabaseName AND r.plan_id = p.plan_id WHERE q.ServerID = @srv AND q.DatabaseName = @dbName AND q.query_hash = @hash AND r.IntervalStartUtc >= @from AND r.IntervalStartUtc < DATEADD(day, ?, @to) AND r.execution_type = ? GROUP BY q.ObjectName ORDER BY TotalCpuMs DESC
Change: 2026-10-05 22:00 UTC, plan 60966 → 60977, pattern: Transition
Impact (Query Store, per execution): duration 12.5 → 0.5 ms (-96%); CPU 12.5 → 0.5 ms (-96%); reads 938 → 201 (-79%); rows 1.2 → 1.18 (-2%)
Plan difference: Access only in the old plan: perf.QdsQuery Clustered Index Seek [PK_QdsQuery]; Access only in the new plan: perf.QdsQuery Index Seek [IX_QdsQuery_QueryHash]; perf.QdsQuery Clustered Index Seek [PK_QdsQuery] (lookup); Join only in the old plan: Nested Loops (Inner Join) ×2; Join only in the new plan: Nested Loops (Inner Join) ×3
Candidate cause: STATS — Statistic perf.QdsPlan.PK_QdsPlan was updated 2026-10-05 03:12 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_Query was updated 2026-10-05 03:12 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000002_58671BC9 was updated 2026-10-05 03:12 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000003_58671BC9 was updated 2026-10-05 03:12 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_PlanHash was updated 2026-10-05 03:12 (sampled 100%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000004_58671BC9 was updated 2026-10-05 08:58 (sampled 100%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.PK_QdsQuery was updated 2026-10-04 09:10 (sampled 96.6%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.IX_QdsQuery_QueryHash was updated 2026-10-05 03:12 (sampled 87.1%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery._WA_Sys_00000002_54968AE5 was updated 2026-10-04 09:10 (sampled 96.6%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery._WA_Sys_00000003_54968AE5 was updated 2026-10-04 09:10 (sampled 96.6%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.IX_QdsQuery_TextHash was updated 2026-10-05 03:12 (sampled 87.1%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery._WA_Sys_00000008_54968AE5 was updated 2026-10-05 09:17 (sampled 85.8%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats.PK_QdsRuntimeStats was updated 2026-10-05 09:43 (sampled 21%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000002_5C37ACAD was updated 2026-10-05 09:43 (sampled 21%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000003_5C37ACAD was updated 2026-10-05 09:43 (sampled 21%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000006_5C37ACAD was updated 2026-10-05 09:49 (sampled 21%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000005_5C37ACAD was updated 2026-10-05 09:57 (sampled 21%); 5 updates in the window; the plans use this statistic.
Counter-evidence: HIGH_VARIANCE — The new plan's duration standard deviation is 186% of the average: the average is not reliable.
Confidence rationale: Medium: a single candidate (STATS), not mechanical (no direct link to the plan is proven). Weak counter-evidence: HIGH_VARIANCE.
Note: this is a candidate cause, not proof of the root cause.