Plan değişimi P423

(@noStatus bit,@statuses2 nvarchar(4000),@statuses1 nvarchar(4000),@Confidence nvarchar(4000),@Direction nvarchar(4000),@Pattern nvarchar(4000),@ServerId int,@DatabaseName nvarchar(4000),@caseId bigint,@qHash varbinary(8000),@qId bigint,@qText nvarchar(4000),@asOf date,@Top int)WITH sel AS (
    SELECT c.*, ROW_NUMBER() OVER (PARTITION BY c.ServerID, c.DatabaseName, c.query_id ORDER BY c.DetectedUtc DESC, c.CaseID DESC) AS rn
    FROM rag.PlanChangeCase AS c
    WHERE (@noStatus = 1 OR c.Status IN (@statuses1,@statuses2))
      AND (@Confidence IS NULL OR c.Confidence = @Confidence)
      AND (@Direction IS NULL OR c.Direction = @Direction)
      AND (@Pattern IS NULL OR c.Pattern = @Pattern)
      AND (@ServerId IS NULL OR c.ServerID = @ServerId)
      AND (@DatabaseName IS NULL OR c.DatabaseName = @DatabaseName)
      AND (@caseId IS NULL OR c.CaseID = @caseId)
      AND (@qHash IS NULL OR c.query_hash = @qHash)
      AND (@qId IS NULL OR @qId IN (c.query_id, c.OldPlanId, c.NewPlanId))
      AND (@qText IS NULL OR c.SqlText LIKE @qText OR c.ObjectName LIKE @qText))
SELECT TOP (@Top) c.CaseID AS CaseId, c.ServerID AS ServerId, c.DatabaseName, c.query_id AS QueryId,
       LEFT(LTRIM(REPLACE(REPLACE(REPLACE(c.SqlText, CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ')), 80) AS SqlSnippet,
       c.Direction, c.DurationRatio, c.TotalLoadDeltaMs, c.Confidence, c.Pattern,
       (SELECT STRING_AGG(x.Code, ', ') FROM (SELECT DISTINCT f.Code FROM rag.PlanChangeFinding AS f
         WHERE f.CaseID = c.CaseID AND f.Kind = 'Candidate' AND f.Supports = 1) AS x) AS Candidates,
       (SELECT COUNT(*) FROM rag.PlanChangeFinding AS f WHERE f.CaseID = c.CaseID AND f.Kind = 'Counter') AS CounterCount,
       c.Status, c.DetectedUtc,
       o.OtherCases, ISNULL(o.OtherAccepted, 0) AS OtherAccepted, ISNULL(o.OtherRejected, 0) AS OtherRejected, c.query_hash AS QueryHash, t.State AS TrackState, c.Source
FROM sel AS c
OUTER APPLY (SELECT COUNT(*) AS OtherCases,
                    SUM(IIF(p.Status IN ('Accepted', 'AutoAccepted'), 1, 0)) AS OtherAccepted,
                    SUM(IIF(p.Status = 'Rejected', 1, 0)) AS OtherRejected
             FROM rag.PlanChangeCase AS p
             WHERE p.ServerID = c.ServerID AND p.DatabaseName = c.DatabaseName AND p.query_id = c.query_id AND p.CaseID <> c.CaseID) AS o
OUTER APPLY (SELECT s.State FROM rag.fn_QueryTrackState(c.ServerID, c.DatabaseName, c.query_hash, ISNULL(@asOf, CAST(DBA.fn_Now() AS date))) AS s
             WHERE c.query_hash IS NOT NULL) AS t
WHERE c.rn = 1
ORDER BY c.TotalLoadDeltaMs DESC, c.CaseID

Plan change case #P423 | DB: DB-11 | Regression | Confidence: Medium (review pending)
Query: (@noStatus bit,@statuses2 nvarchar(?),@statuses1 nvarchar(?),@Confidence nvarchar(?),@Direction nvarchar(?),@Pattern nvarchar(?),@ServerId int,@DatabaseName nvarchar(?),@caseId bigint,@qHash varbinary(?),@qId bigint,@qText nvarchar(?),@asOf date,@Top int)WITH sel AS ( SELECT c.*, ROW_NUMBER() OVER (PARTITION BY c.ServerID, c.DatabaseName, c.query_id ORDER BY c.DetectedUtc DESC, c.CaseID DESC) AS rn FROM rag.PlanChangeCase AS c WHERE (@noStatus = ? OR c.Status IN (@statuses1,@statuses2)) AND (@Confidence IS NULL OR c.Confidence = @Confidence) AND (@Direction IS NULL OR c.Direction = @Direction) AND (@Pattern IS NULL OR c.Pattern = @Pattern) AND (@ServerId IS NULL OR c.ServerID = @ServerId) AND (@DatabaseName IS NULL OR c.DatabaseName = @DatabaseName) AND (@caseId IS NULL OR c.CaseID = @caseId) AND (@qHash IS NULL OR c.query_hash = @qHash) AND (@qId IS NULL OR @qId IN (c.query_id, c.OldPlanId, c.NewPlanId)) AND (@qText IS NULL OR c.SqlText LIKE @qText OR c.ObjectName LIKE @qText)) SELECT TOP (@Top) c.CaseID AS CaseId, c.ServerID AS ServerId, c.DatabaseName, c.query_id AS QueryId, LEFT(LTRIM(REPLACE(REPLACE(REPLACE(c.SqlText, CHAR(?), ?), CHAR(?), ?), CHAR(?), ?)), ?) AS SqlSnippet, c.Direction, c.DurationRatio, c.TotalLoadDeltaMs, c.Confidence, c.Pattern, (SELECT STRING_AGG(x.Code, ?) FROM (SELECT DISTINCT f.Code FROM rag.PlanChangeFinding AS f WHERE f.CaseID = c.CaseID AND f.Kind = ? AND f.Supports = ?) AS x) AS Candidates, (SELECT COUNT(*) FROM rag.PlanChangeFinding AS f WHERE f.CaseID = c.CaseID AND f.Kind = ?) AS CounterCount, c.Status, c.DetectedUtc, o.OtherCases, ISNULL(o.OtherAccepted, ?) AS OtherAccepted, ISNULL(o.OtherRejected, ?) AS OtherRejected, c.query_hash AS QueryHash, t.State AS TrackState, c.Source FROM sel AS c OUTER APPLY (SELECT COUNT(*) AS OtherCases, SUM(IIF(p.Status IN (?), ?)) AS OtherAccepted, SUM(IIF(p.Status = ?)) AS OtherRejected FROM rag.PlanChangeCase AS p WHERE p.ServerID = c.ServerID AND p.DatabaseName = c.DatabaseName AND p.query_id = c.query_id AND p.CaseID <> c.CaseID) AS o OUTER APPLY (SELECT s.State FROM rag.fn_QueryTrackState(c.ServerID, c.DatabaseName, c.query_hash, ISNULL(@asOf, CAST(DBA.fn_Now() AS date))) AS s WHERE c.query_hash IS NOT NULL) AS t WHERE c.rn = ? ORDER BY c.TotalLoadDeltaMs DESC, c.CaseID
Change: 2026-10-07 14:00 UTC, plan 54153 → 65749, pattern: Transition
Impact (Query Store, per execution): duration 521.7 → 992.8 ms (+90%); CPU 521.6 → 988 ms (+89%); reads 37,994 → 43,252 (+14%); rows 110.63 → 164.69 (+49%)
Plan difference: Access only in the new plan: rag.AnalysisRecommendation Clustered Index Scan [PK_AnalysisRecommendation]; rag.AnalysisRequest Clustered Index Seek [PK_AnalysisRequest]; Join only in the old plan: Merge Join (Right Outer Join); Nested Loops (Inner Join) ×10; Nested Loops (Left Outer Join) ×5; Join only in the new plan: Nested Loops (Left Outer Join) ×6; Nested Loops (Inner Join) ×8; Estimated cost: 31.896 → 50.483; estimated rows: 166.2 → 200
Candidate cause: STATS — Statistic perf.QueryDailyStats.PK_QueryDailyStats was updated 2026-10-06 19:19 (sampled 72.6%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000003_5D80D6A1 was updated 2026-10-06 19:19 (sampled 72.6%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000004_5D80D6A1 was updated 2026-10-06 19:19 (sampled 72.6%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats.IX_QueryDailyStats_Query was updated 2026-10-06 19:19 (sampled 76.6%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRecommendation.IX_AnalysisRecommendation_Request was updated 2026-10-06 01:17 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRecommendation._WA_Sys_0000000E_22DFFF17 was updated 2026-10-06 19:19 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRecommendation._WA_Sys_0000000A_22DFFF17 was updated 2026-10-06 19:19 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRecommendation._WA_Sys_00000006_22DFFF17 was updated 2026-10-06 01:17 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest.PK_AnalysisRequest was updated 2026-10-06 19:19 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest.IX_AnalysisRequest_Status was updated 2026-10-06 05:11 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest._WA_Sys_00000004_186270A4 was updated 2026-10-06 05:11 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest._WA_Sys_00000005_186270A4 was updated 2026-10-06 05:11 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest._WA_Sys_00000002_186270A4 was updated 2026-10-06 05:11 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest._WA_Sys_00000003_186270A4 was updated 2026-10-06 05:11 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest.IX_AnalysisRequest_PlanChangeCase was updated 2026-10-06 05:11 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase.PK_PlanChangeCase was updated 2026-10-06 19:19 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase.IX_PlanChangeCase_Status was updated 2026-10-07 10:24 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase.UQ_PlanChangeCase_Pair was updated 2026-10-06 19:19 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase._WA_Sys_00000006_25014A66 was updated 2026-10-06 19:19 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase._WA_Sys_00000003_25014A66 was updated 2026-10-06 19:19 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase._WA_Sys_00000004_25014A66 was updated 2026-10-06 19:19 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase._WA_Sys_00000007_25014A66 was updated 2026-10-06 19:19 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeCase._WA_Sys_00000005_25014A66 was updated 2026-10-06 05:11 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeFinding.IX_PlanChangeFinding_Case was updated 2026-10-07 10:24 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeFinding._WA_Sys_00000005_3EC11C69 was updated 2026-10-07 10:24 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeFinding._WA_Sys_00000003_3EC11C69 was updated 2026-10-07 10:24 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.PlanChangeFinding._WA_Sys_00000004_3EC11C69 was updated 2026-10-07 10:24 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: GROWTH — From the old plan's period to the change, rag.PlanChangeCase rows changed +39% and size +71% (2026-10-05 → 2026-10-07).
Candidate cause: GROWTH — From the old plan's period to the change, rag.PlanChangeFinding rows changed +95% and size +59% (2026-10-05 → 2026-10-07).
Candidate cause: GROWTH — From the old plan's period to the change, perf.QueryDailyStats rows changed +4% and size +59% (2026-10-05 → 2026-10-07).
Counter-evidence: none
Confidence rationale: Medium: more than one candidate (STATS, GROWTH) — competing explanations.
Note: this is a candidate cause, not proof of the root cause.