Plan değişimi P406

(@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 #P406 | 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-09-29 22:00 UTC, plan 7197 → 8706, pattern: Transition
Impact (Query Store, per execution): duration 10.6 → 253.4 ms (+2284%); CPU 10.2 → 248.6 ms (+2345%); reads 187 → 148,119 (+79076%); rows 1.07 → 7.5 (+601%)
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: WORKLOAD — The plans see a different workload: rows per execution 1.07 → 7.5. The plans may have been compiled for different parameters or batch sizes.
Counter-evidence: ORDER_UNCERTAIN — STATS candidate ruled out: the time of the perf.QdsRuntimeStats.PK_QdsRuntimeStats event (2026-09-29 22:11) spans the change, so whether it happened before or after is unknown.
Counter-evidence: ORDER_UNCERTAIN — STATS candidate ruled out: the time of the perf.QdsRuntimeStats._WA_Sys_00000002_5C37ACAD event (2026-09-29 22:11) spans the change, so whether it happened before or after is unknown.
Counter-evidence: ORDER_UNCERTAIN — STATS candidate ruled out: the time of the perf.QdsRuntimeStats._WA_Sys_00000003_5C37ACAD event (2026-09-29 22:11) spans the change, so whether it happened before or after is unknown.
Counter-evidence: ORDER_UNCERTAIN — STATS candidate ruled out: the time of the perf.QdsRuntimeStats._WA_Sys_00000006_5C37ACAD event (2026-09-29 22:11) spans the change, so whether it happened before or after is unknown.
Counter-evidence: ORDER_UNCERTAIN — STATS candidate ruled out: the time of the perf.QdsRuntimeStats._WA_Sys_00000008_5C37ACAD event (2026-09-29 22:11) spans the change, so whether it happened before or after is unknown.
Counter-evidence: HIGH_VARIANCE — The old plan's duration standard deviation is 166% of the average: the average is not reliable.
Counter-evidence: HIGH_VARIANCE — The new plan's duration standard deviation is 158% of the average: the average is not reliable.
Counter-evidence: METADATA_GAP — Metadata collection started on 2026-10-01 and the change was on 2026-09-29: index, statistics and module events could not be evaluated (no data, not no events).
Confidence rationale: Medium: a single candidate (WORKLOAD), not mechanical (no direct link to the plan is proven). Weak counter-evidence: HIGH_VARIANCE, METADATA_GAP.
Note: this is a candidate cause, not proof of the root cause.