Plan değişimi P436

(@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)
      AND r.execution_type = 0   -- K1-1: iptal (3) ve hata (4) ortalamaya karışmaz
      AND NOT EXISTS (SELECT 1 FROM perf.QdsQueryText AS it CROSS APPLY perf.fn_InternalQueryText(it.query_sql_text) AS iq WHERE it.TextHash = q.TextHash)   -- K3-2b
    GROUP BY p.plan_id
    HAVING SUM(r.count_executions) >= @minExec) AS x
GROUP BY k.ServerId, k.DatabaseName, k.Hash
HAVING COUNT(*) >= 2
OPTION (RECOMPILE)

Plan change case #P436 | DB: DB-11 | Regression | Confidence: Low (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) AND r.execution_type = ? AND NOT EXISTS (SELECT ? FROM perf.QdsQueryText AS it CROSS APPLY perf.fn_InternalQueryText(it.query_sql_text) AS iq WHERE it.TextHash = q.TextHash) GROUP BY p.plan_id HAVING SUM(r.count_executions) >= @minExec) AS x GROUP BY k.ServerId, k.DatabaseName, k.Hash HAVING COUNT(*) >= ? OPTION (RECOMPILE)
Change: 2026-10-07 09:00 UTC, plan 64761 → 64797, pattern: Transition
Impact (Query Store, per execution): duration 32.6 → 229.1 ms (+603%); CPU 32.6 → 225.8 ms (+593%); reads 983 → 7,728 (+686%); rows 0.65 → 29.7 (+4469%)
Plan difference: Access only in the old plan: perf.QdsRuntimeStats Clustered Index Scan [PK_QdsRuntimeStats]; perf.QdsQuery Clustered Index Seek [PK_QdsQuery]; perf.QdsQueryText Clustered Index Seek [PK_QdsQueryText]; Access only in the new plan: perf.QdsQueryText Clustered Index Scan [PK_QdsQueryText]; perf.QdsQuery Clustered Index Scan [PK_QdsQuery]; perf.QdsRuntimeStats Clustered Index Seek [PK_QdsRuntimeStats]; Join only in the old plan: Nested Loops (Left Anti Semi Join); Join only in the new plan: Merge Join (Right Anti Semi Join)
Candidate cause: STATS — Statistic perf.QdsPlan.PK_QdsPlan was updated 2026-10-06 01:10 (sampled 79%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_Query was updated 2026-10-06 01:10 (sampled 79.3%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000002_58671BC9 was updated 2026-10-06 01:10 (sampled 79.3%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000003_58671BC9 was updated 2026-10-06 01:10 (sampled 79.3%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_PlanHash was updated 2026-10-06 22:11 (sampled 79.1%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000004_58671BC9 was updated 2026-10-06 18:58 (sampled 79.1%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.PK_QdsQuery was updated 2026-10-06 01:10 (sampled 69.2%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.IX_QdsQuery_QueryHash was updated 2026-10-06 17:10 (sampled 67%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery._WA_Sys_00000002_54968AE5 was updated 2026-10-06 01:10 (sampled 69.2%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery._WA_Sys_00000003_54968AE5 was updated 2026-10-06 01:10 (sampled 69.2%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.IX_QdsQuery_TextHash was updated 2026-10-06 17:10 (sampled 67%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQueryText.PK_QdsQueryText was updated 2026-10-06 05:48 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats.PK_QdsRuntimeStats was updated 2026-10-06 06:11 (sampled 17.9%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000002_5C37ACAD was updated 2026-10-06 06:11 (sampled 17.9%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000003_5C37ACAD was updated 2026-10-06 06:11 (sampled 17.9%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000006_5C37ACAD was updated 2026-10-06 06:11 (sampled 17.9%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000005_5C37ACAD was updated 2026-10-06 06:11 (sampled 17.9%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000008_5C37ACAD was updated 2026-10-06 16:35 (sampled 17.7%); 3 updates in the window; the plans use this statistic.
Candidate cause: WORKLOAD — The plans see a different workload: rows per execution 0.65 → 29.7. The plans may have been compiled for different parameters or batch sizes.
Counter-evidence: WORK_PROPORTIONAL — Rows per execution changed 45.69× and duration 7.03×; duration per row 50.1687 → 7.7147 ms: the duration difference comes from the amount of data processed, not the plan.
Counter-evidence: LOW_SAMPLE — The old plan ran only 20 times: the sample is small and the average is weak.
Confidence rationale: Low: strong counter-evidence (WORK_PROPORTIONAL).
Note: this is a candidate cause, not proof of the root cause.