Plan değişimi P416

(@from datetime,@NowUtc datetime,@Top int)WITH items AS (
        SELECT 'Analysis' AS Kind, a.RequestID AS RefId, a.TargetType, a.ServerID, a.DatabaseName, a.QueryHash, a.ObjectName,
               CAST(NULL AS varchar(10)) AS Direction, CAST(NULL AS varchar(6)) AS Confidence,
               p.Pending, p.Invalid, a.FinishedUtc AS SinceUtc
        FROM rag.AnalysisRequest AS a
        CROSS APPLY (SELECT COUNT(*) AS Pending, SUM(IIF(r.ValidationStatus = 'Invalid', 1, 0)) AS Invalid
                     FROM rag.AnalysisRecommendation AS r WHERE r.RequestID = a.RequestID AND r.Decision = 'Pending') AS p
        WHERE a.Status = 'Done' AND p.Pending > 0
        UNION ALL
        SELECT 'PlanChange', c.CaseID, 'Query', c.ServerID, c.DatabaseName, c.query_hash, NULL,
               c.Direction, c.Confidence, 1, 0, c.DetectedUtc
        FROM rag.PlanChangeCase AS c
        WHERE c.Status IN ('Pending', 'Unsure')
    )
    SELECT TOP (@Top) i.Kind, i.RefId, i.TargetType, i.ServerID AS ServerId, i.DatabaseName, i.QueryHash, i.ObjectName,
           i.Direction, i.Confidence, i.Pending, ISNULL(i.Invalid, 0) AS Invalid, i.SinceUtc,
           DATEDIFF(hour, i.SinceUtc, @NowUtc) AS AgeHours, cost.SecondsPerDay
    FROM items AS i
    OUTER APPLY (SELECT SUM(q.TotalDurationMs) / 1000.0 / 7 AS SecondsPerDay FROM perf.QueryDailyStats AS q
                 WHERE q.ServerID = i.ServerID AND q.DatabaseName = i.DatabaseName AND q.StatDateUtc >= @from
                   AND ((i.QueryHash IS NOT NULL AND q.query_hash = i.QueryHash)
                        OR (i.QueryHash IS NULL AND i.ObjectName IS NOT NULL AND q.ObjectName = i.ObjectName))) AS cost
    ORDER BY ISNULL(cost.SecondsPerDay, -1) DESC, i.SinceUtc

Plan change case #P416 | DB: DB-11 | Regression | Confidence: Medium (review pending)
Query: (@from datetime,@NowUtc datetime,@Top int)WITH items AS ( SELECT ? AS Kind, a.RequestID AS RefId, a.TargetType, a.ServerID, a.DatabaseName, a.QueryHash, a.ObjectName, CAST(NULL AS varchar(?)) AS Direction, CAST(NULL AS varchar(?)) AS Confidence, p.Pending, p.Invalid, a.FinishedUtc AS SinceUtc FROM rag.AnalysisRequest AS a CROSS APPLY (SELECT COUNT(*) AS Pending, SUM(IIF(r.ValidationStatus = ?)) AS Invalid FROM rag.AnalysisRecommendation AS r WHERE r.RequestID = a.RequestID AND r.Decision = ?) AS p WHERE a.Status = ? AND p.Pending > ? UNION ALL SELECT ?, c.CaseID, ?, c.ServerID, c.DatabaseName, c.query_hash, NULL, c.Direction, c.Confidence, ?, c.DetectedUtc FROM rag.PlanChangeCase AS c WHERE c.Status IN (?) ) SELECT TOP (@Top) i.Kind, i.RefId, i.TargetType, i.ServerID AS ServerId, i.DatabaseName, i.QueryHash, i.ObjectName, i.Direction, i.Confidence, i.Pending, ISNULL(i.Invalid, ?) AS Invalid, i.SinceUtc, DATEDIFF(hour, i.SinceUtc, @NowUtc) AS AgeHours, cost.SecondsPerDay FROM items AS i OUTER APPLY (SELECT SUM(q.TotalDurationMs) / ? / ? AS SecondsPerDay FROM perf.QueryDailyStats AS q WHERE q.ServerID = i.ServerID AND q.DatabaseName = i.DatabaseName AND q.StatDateUtc >= @from AND ((i.QueryHash IS NOT NULL AND q.query_hash = i.QueryHash) OR (i.QueryHash IS NULL AND i.ObjectName IS NOT NULL AND q.ObjectName = i.ObjectName))) AS cost ORDER BY ISNULL(cost.SecondsPerDay, -?) DESC, i.SinceUtc
Change: 2026-10-05 10:00 UTC, plan 29192 → 48490, pattern: Transition
Impact (Query Store, per execution): duration 170.6 → 609.3 ms (+257%); CPU 170.5 → 607.3 ms (+256%); reads 21,682 → 81,621 (+276%); rows 97.82 → 200 (+104%)
Plan difference: Access only in the old plan: perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; Access only in the new plan: perf.QueryDailyStats Clustered Index Scan [PK_QueryDailyStats]; Estimated cost: 0.972 → 4.967; estimated rows: 82.9 → 200
Candidate cause: STATS — Statistic perf.QueryDailyStats.PK_QueryDailyStats was updated 2026-10-05 09:49 (sampled 100%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000003_5D80D6A1 was updated 2026-10-05 09:43 (sampled 100%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000004_5D80D6A1 was updated 2026-10-05 09:43 (sampled 100%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats.IX_QueryDailyStats_Query was updated 2026-10-05 09:49 (sampled 100%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000006_5D80D6A1 was updated 2026-10-05 09:50 (sampled 100%); 7 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRecommendation.IX_AnalysisRecommendation_Request was updated 2026-10-05 09:09 (sampled 100%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRecommendation._WA_Sys_0000000A_22DFFF17 was updated 2026-10-05 09:56 (sampled 100%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest.IX_AnalysisRequest_Status was updated 2026-10-05 09:56 (sampled 100%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest._WA_Sys_00000004_186270A4 was updated 2026-10-05 09:09 (sampled 100%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest._WA_Sys_00000005_186270A4 was updated 2026-10-05 09:09 (sampled 100%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest._WA_Sys_00000003_186270A4 was updated 2026-10-05 09:09 (sampled 100%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest._WA_Sys_00000006_186270A4 was updated 2026-10-05 08:59 (sampled 100%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest.IX_AnalysisRequest_PlanChangeCase was updated 2026-10-05 09:09 (sampled 100%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase.IX_PlanChangeCase_Status was updated 2026-10-05 08:59 (sampled 100%); 8 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase.UQ_PlanChangeCase_Pair was updated 2026-10-04 08:18 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase._WA_Sys_00000005_25014A66 was updated 2026-10-05 07:50 (sampled 100%); 5 updates in the window; the plans use this statistic.
Candidate cause: GROWTH — From the old plan's period to the change, rag.AnalysisRequest rows changed +24% and size +7% (2026-10-01 → 2026-10-05).
Candidate cause: GROWTH — From the old plan's period to the change, rag.AnalysisRecommendation rows changed +14% and size +29% (2026-10-01 → 2026-10-05).
Candidate cause: GROWTH — From the old plan's period to the change, rag.PlanChangeCase rows changed +149% and size +251% (2026-10-01 → 2026-10-05).
Candidate cause: GROWTH — From the old plan's period to the change, perf.QueryDailyStats rows changed +162% and size +186% (2026-10-01 → 2026-10-05).
Candidate cause: WORKLOAD — The plans see a different workload: rows per execution 97.82 → 200. The plans may have been compiled for different parameters or batch sizes.
Counter-evidence: ORDER_UNCERTAIN — MODULE candidate ruled out: the time of the rag.usp_ReviewQueue event (between 2026-10-05 09:00 and 2026-10-05 10:00) spans the change, so whether it happened before or after is unknown.
Counter-evidence: TREND_BEFORE — The old plan was already drifting before the change (avg 136.5 → 243.9 ms): part of the duration difference is independent of the plan change.
Confidence rationale: Medium: more than one candidate (STATS, GROWTH, WORKLOAD) — competing explanations. Weak counter-evidence: TREND_BEFORE.
Note: this is a candidate cause, not proof of the root cause.