Plan değişimi P433

(@srv int,@dbName nvarchar(4000),@hash varbinary(8),@from datetime,@to datetime)SELECT p.plan_id AS PlanId, CAST(MAX(CAST(p.is_forced_plan AS int)) AS bit) AS IsForced, MAX(p.LastExecutionUtc) AS LastExecutionUtc,
       SUM(r.count_executions) AS Executions,
       SUM(r.avg_duration * r.count_executions) / NULLIF(SUM(r.count_executions), 0) / 1000.0 AS AvgDurationMs,
       SUM(r.avg_cpu_time * r.count_executions) / NULLIF(SUM(r.count_executions), 0) / 1000.0 AS AvgCpuMs,
       SUM(r.avg_logical_io_reads * r.count_executions) / NULLIF(SUM(r.count_executions), 0) AS AvgLogicalReads,
       MAX(p.query_id) AS QueryId,
       SUM(r.avg_rowcount * r.count_executions) / NULLIF(SUM(CASE WHEN r.avg_rowcount IS NOT NULL THEN r.count_executions END), 0) AS AvgRows,
       MAX(r.max_rowcount) AS MaxRows
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   -- 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: iç sorgu planı aday değil
GROUP BY p.plan_id
ORDER BY AvgDurationMs

Plan change case #P433 | DB: DB-11 | Improvement | Confidence: Medium (review pending)
Query: (@srv int,@dbName nvarchar(?),@hash varbinary(?),@from datetime,@to datetime)SELECT p.plan_id AS PlanId, CAST(MAX(CAST(p.is_forced_plan AS int)) AS bit) AS IsForced, MAX(p.LastExecutionUtc) AS LastExecutionUtc, SUM(r.count_executions) AS Executions, SUM(r.avg_duration * r.count_executions) / NULLIF(SUM(r.count_executions), ?) / ? AS AvgDurationMs, SUM(r.avg_cpu_time * r.count_executions) / NULLIF(SUM(r.count_executions), ?) / ? AS AvgCpuMs, SUM(r.avg_logical_io_reads * r.count_executions) / NULLIF(SUM(r.count_executions), ?) AS AvgLogicalReads, MAX(p.query_id) AS QueryId, SUM(r.avg_rowcount * r.count_executions) / NULLIF(SUM(CASE WHEN r.avg_rowcount IS NOT NULL THEN r.count_executions END), ?) AS AvgRows, MAX(r.max_rowcount) AS MaxRows 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 = ? 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 ORDER BY AvgDurationMs
Change: 2026-10-06 00:00 UTC, plan 64330 → 64332, pattern: Alternating (9 switches)
Impact (Query Store, per execution): duration 88 → 1.4 ms (-98%); CPU 87.8 → 1.4 ms (-98%); reads 100,972 → 347 (-100%); rows 15.39 → 15.11 (-2%); aborted 1 → 0
Plan difference: Access only in the old plan: perf.QdsPlan Clustered Index Seek [PK_QdsPlan]; 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); perf.QdsPlan Index Seek [IX_QdsPlan_Query]; perf.QdsPlan Clustered Index Seek [PK_QdsPlan] (lookup); Join only in the old plan: Nested Loops (Inner Join) ×2; Join only in the new plan: Nested Loops (Inner Join) ×4
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%); 6 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 23:06 (sampled 69.8%); 5 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 23:06 (sampled 69.8%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQueryText.PK_QdsQueryText was updated 2026-10-05 08:59 (sampled 100%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats.PK_QdsRuntimeStats was updated 2026-10-05 22:12 (sampled 18.8%); 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 22:12 (sampled 18.8%); 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 22:12 (sampled 18.8%); 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 22:12 (sampled 18.8%); 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 22:12 (sampled 18.8%); 5 updates in the window; the plans use this statistic.
Candidate cause: PARAM — The two plans alternate in the same period (9 switches): plan choice depends on the parameter value rather than an event (parameter sensitivity).
Counter-evidence: HIGH_VARIANCE — The new plan's duration standard deviation is 224% of the average: the average is not reliable.
Confidence rationale: Medium: more than one candidate (STATS, PARAM) — competing explanations. Weak counter-evidence: HIGH_VARIANCE.
Note: this is a candidate cause, not proof of the root cause.