Plan değişimi P427

(@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
GROUP BY p.plan_id
ORDER BY AvgDurationMs

Plan change case #P427 | 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 = ? GROUP BY p.plan_id ORDER BY AvgDurationMs
Change: 2026-10-05 22:00 UTC, plan 60965 → 60974, pattern: Transition
Impact (Query Store, per execution): duration 77.6 → 0.6 ms (-99%); CPU 77.6 → 0.6 ms (-99%); reads 94,768 → 212 (-100%); rows 14.13 → 12.64 (-11%)
Plan difference: Access only in the old plan: perf.QdsPlan Clustered Index Seek [PK_QdsPlan]; Access only in the new plan: 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) ×3
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%); 5 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 03:12 (sampled 87.1%); 4 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 03:12 (sampled 87.1%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats.PK_QdsRuntimeStats was updated 2026-10-05 09:43 (sampled 21%); 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 09:43 (sampled 21%); 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 09:43 (sampled 21%); 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 09:49 (sampled 21%); 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 09:57 (sampled 21%); 5 updates in the window; the plans use this statistic.
Counter-evidence: HIGH_VARIANCE — The new plan's duration standard deviation is 169% of the average: the average is not reliable.
Confidence rationale: Medium: a single candidate (STATS), not mechanical (no direct link to the plan is proven). Weak counter-evidence: HIGH_VARIANCE.
Note: this is a candidate cause, not proof of the root cause.