Plan değişimi P407

(@keys nvarchar(4000),@from datetime,@to datetime,@minExec bigint)SELECT k.ServerId, k.DatabaseName, k.Hash, COUNT(*) AS Plans, MAX(x.AvgDuration) / NULLIF(MIN(x.AvgDuration), 0) AS Ratio
FROM OPENJSON(@keys) WITH (ServerId int, DatabaseName sysname, Hash varchar(18)) AS k
CROSS APPLY (
    SELECT p.plan_id, SUM(r.avg_duration * r.count_executions) / NULLIF(SUM(r.count_executions), 0) AS AvgDuration
    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 = k.ServerId AND q.DatabaseName = k.DatabaseName AND q.query_hash = CONVERT(binary(8), k.Hash, 1)
      AND r.IntervalStartUtc >= @from AND r.IntervalStartUtc < DATEADD(day, 1, @to)
    GROUP BY p.plan_id
    HAVING SUM(r.count_executions) >= @minExec) AS x
GROUP BY k.ServerId, k.DatabaseName, k.Hash
HAVING COUNT(*) >= 2

Plan change case #P407 | DB: DB-11 | Regression | Confidence: Medium (review pending)
Query: (@keys nvarchar(?),@from datetime,@to datetime,@minExec bigint)SELECT k.ServerId, k.DatabaseName, k.Hash, COUNT(*) AS Plans, MAX(x.AvgDuration) / NULLIF(MIN(x.AvgDuration), ?) AS Ratio FROM OPENJSON(@keys) WITH (ServerId int, DatabaseName sysname, Hash varchar(?)) AS k CROSS APPLY ( SELECT p.plan_id, SUM(r.avg_duration * r.count_executions) / NULLIF(SUM(r.count_executions), ?) AS AvgDuration 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 = k.ServerId AND q.DatabaseName = k.DatabaseName AND q.query_hash = CONVERT(binary(?), k.Hash, ?) AND r.IntervalStartUtc >= @from AND r.IntervalStartUtc < DATEADD(day, ?, @to) GROUP BY p.plan_id HAVING SUM(r.count_executions) >= @minExec) AS x GROUP BY k.ServerId, k.DatabaseName, k.Hash HAVING COUNT(*) >= ?
Change: 2026-10-04 08:00 UTC, plan 33296 → 37020, pattern: Alternating (3 switches)
Impact (Query Store, per execution): duration 145.3 → 739.8 ms (+409%); CPU 119.1 → 731.4 ms (+514%); reads 2,284 → 437,175 (+19044%); rows 5.28 → 12.15 (+130%)
Plan difference: Access only in the old plan: perf.QdsPlan Index Scan [IX_QdsPlan_Query]; perf.QdsQuery Index Scan [IX_QdsQuery_QueryHash]; Access only in the new plan: perf.QdsPlan Clustered Index Seek [PK_QdsPlan]; perf.QdsQuery Clustered Index Seek [PK_QdsQuery]; Join only in the old plan: Hash Match (Inner Join) ×3; Join only in the new plan: Hash Match (Inner Join); Nested Loops (Inner Join) ×2
Candidate cause: STATS — Statistic perf.QdsPlan.PK_QdsPlan was updated 2026-10-02 18:11 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_Query was updated 2026-10-02 18:11 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000002_58671BC9 was updated 2026-10-02 18:11 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000003_58671BC9 was updated 2026-10-02 18:11 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_PlanHash was updated 2026-10-03 12:10 (sampled 100%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000004_58671BC9 was updated 2026-10-03 04:00 (sampled 100%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.IX_QdsQuery_QueryHash was updated 2026-10-03 03:10 (sampled 97.8%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.IX_QdsQuery_TextHash was updated 2026-10-03 03:10 (sampled 97.8%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats.PK_QdsRuntimeStats was updated 2026-10-02 17:10 (sampled 29.2%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000002_5C37ACAD was updated 2026-10-02 17:10 (sampled 29.2%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000003_5C37ACAD was updated 2026-10-02 17:10 (sampled 29.2%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000006_5C37ACAD was updated 2026-10-02 17:10 (sampled 29.2%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000008_5C37ACAD was updated 2026-10-02 17:25 (sampled 29.2%); 2 updates in the window; the plans use this statistic.
Candidate cause: GROWTH — From the old plan's period to the change, perf.QdsRuntimeStats rows changed +17% and size +26% (2026-10-02 → 2026-10-04).
Candidate cause: WORKLOAD — The plans see a different workload: rows per execution 5.28 → 12.15. The plans may have been compiled for different parameters or batch sizes.
Candidate cause: PARAM — The two plans alternate in the same period (3 switches): plan choice depends on the parameter value rather than an event (parameter sensitivity).
Counter-evidence: HIGH_VARIANCE — The old plan's duration standard deviation is 135% of the average: the average is not reliable.
Counter-evidence: HIGH_VARIANCE — The new plan's duration standard deviation is 100% of the average: the average is not reliable.
Confidence rationale: Medium: more than one candidate (STATS, GROWTH, WORKLOAD + PARAM) — competing explanations. Weak counter-evidence: HIGH_VARIANCE.
Note: this is a candidate cause, not proof of the root cause.