Plan değişimi P435

(@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 #P435 | DB: DB-11 | Improvement | 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) 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-06 16:00 UTC, plan 64326 → 64738, pattern: Alternating (6 switches)
Impact (Query Store, per execution): duration 344.5 → 1.1 ms (-100%); CPU 343.2 → 1.1 ms (-100%); reads 5,196 → 54 (-99%); rows 0.65 → 0.65 (-0%)
Plan difference: Access only in the old plan: perf.QdsQueryText Clustered Index Scan [PK_QdsQueryText]; perf.QdsQuery Clustered Index Scan [PK_QdsQuery]; perf.QdsPlan Index Scan [IX_QdsPlan_Query]; Access only in the new plan: perf.QdsPlan Clustered Index Seek [PK_QdsPlan]; perf.QdsQuery Clustered Index Seek [PK_QdsQuery]; perf.QdsQueryText Clustered Index Seek [PK_QdsQueryText]; Join only in the old plan: Merge Join (Inner Join) ×2; Hash Match (Right Anti Semi Join); Join only in the new plan: Nested Loops (Left Anti Semi Join); Nested Loops (Inner Join) ×2
Candidate cause: STATS — Statistic perf.QdsPlan.PK_QdsPlan was updated 2026-10-06 01:10 (sampled 79%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_Query was updated 2026-10-06 01:10 (sampled 79.3%); 2 updates in the window; 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%); 2 updates in the window; 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%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_PlanHash was updated 2026-10-06 00:12 (sampled 81.1%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000004_58671BC9 was updated 2026-10-06 01:16 (sampled 79.3%); 4 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-05 23:06 (sampled 69.8%); 3 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-05 23:06 (sampled 69.8%); 3 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%); 3 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%); 4 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%); 4 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%); 4 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%); 4 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%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000008_5C37ACAD was updated 2026-10-05 22:13 (sampled 18.7%); 4 updates in the window; the plans use this statistic.
Candidate cause: PARAM — The two plans alternate in the same period (6 switches): plan choice depends on the parameter value rather than an event (parameter sensitivity).
Counter-evidence: none
Confidence rationale: Medium: more than one candidate (STATS, PARAM) — competing explanations.
Note: this is a candidate cause, not proof of the root cause.