Plan değişimi P424

(@srv int,@dbName nvarchar(4000),@to datetime)SELECT k.ServerID, k.DatabaseName, k.QueryHash, IIF(k.TrackHash = k.QueryHash, NULL, k.TrackHash) AS LineageOf, t.*
INTO #t
FROM (SELECT DISTINCT a.ServerID, a.DatabaseName, a.QueryHash, a.QueryHash AS TrackHash FROM rag.AnalysisRequest AS a
      WHERE a.TargetType = 'Query' AND a.QueryHash IS NOT NULL
        AND (@srv IS NULL OR a.ServerID = @srv) AND (@dbName IS NULL OR a.DatabaseName = @dbName)
        AND NOT EXISTS (SELECT 1 FROM rag.QueryLineage AS l
                        WHERE l.ServerID = a.ServerID AND l.DatabaseName = a.DatabaseName AND l.NewQueryHash = a.QueryHash)
      UNION
      SELECT ServerID, DatabaseName, QueryHash, QueryHash FROM rag.QuerySnooze AS s
      WHERE (@srv IS NULL OR ServerID = @srv) AND (@dbName IS NULL OR DatabaseName = @dbName)
        AND NOT EXISTS (SELECT 1 FROM rag.QueryLineage AS l
                        WHERE l.ServerID = s.ServerID AND l.DatabaseName = s.DatabaseName AND l.NewQueryHash = s.QueryHash)
      UNION
      SELECT ServerID, DatabaseName, NewQueryHash, OldQueryHash FROM rag.QueryLineage
      WHERE (@srv IS NULL OR ServerID = @srv) AND (@dbName IS NULL OR DatabaseName = @dbName)) AS k
CROSS APPLY rag.fn_QueryTrackState(k.ServerID, k.DatabaseName, k.TrackHash, @to) AS t

Plan change case #P424 | DB: DB-11 | Improvement | Confidence: Medium (review pending)
Query: (@srv int,@dbName nvarchar(?),@to datetime)SELECT k.ServerID, k.DatabaseName, k.QueryHash, IIF(k.TrackHash = k.QueryHash, NULL, k.TrackHash) AS LineageOf, t.* INTO #t FROM (SELECT DISTINCT a.ServerID, a.DatabaseName, a.QueryHash, a.QueryHash AS TrackHash FROM rag.AnalysisRequest AS a WHERE a.TargetType = ? AND a.QueryHash IS NOT NULL AND (@srv IS NULL OR a.ServerID = @srv) AND (@dbName IS NULL OR a.DatabaseName = @dbName) AND NOT EXISTS (SELECT ? FROM rag.QueryLineage AS l WHERE l.ServerID = a.ServerID AND l.DatabaseName = a.DatabaseName AND l.NewQueryHash = a.QueryHash) UNION SELECT ServerID, DatabaseName, QueryHash, QueryHash FROM rag.QuerySnooze AS s WHERE (@srv IS NULL OR ServerID = @srv) AND (@dbName IS NULL OR DatabaseName = @dbName) AND NOT EXISTS (SELECT ? FROM rag.QueryLineage AS l WHERE l.ServerID = s.ServerID AND l.DatabaseName = s.DatabaseName AND l.NewQueryHash = s.QueryHash) UNION SELECT ServerID, DatabaseName, NewQueryHash, OldQueryHash FROM rag.QueryLineage WHERE (@srv IS NULL OR ServerID = @srv) AND (@dbName IS NULL OR DatabaseName = @dbName)) AS k CROSS APPLY rag.fn_QueryTrackState(k.ServerID, k.DatabaseName, k.TrackHash, @to) AS t
Change: 2026-10-05 20:00 UTC, plan 52481 → 57515, pattern: Transition
Impact (Query Store, per execution): duration 136.4 → 3 ms (-98%); CPU 136.3 → 3 ms (-98%); reads 36,525 → 284 (-99%); rows 22.09 → 1.35 (-94%)
Plan difference: Access only in the old plan: perf.QueryDailyStats Index Seek [IX_QueryDailyStats_Query]; perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats] (lookup); rag.AnalysisRecommendation Index Seek [IX_AnalysisRecommendation_Request]; rag.AnalysisRecommendation Clustered Index Seek [PK_AnalysisRecommendation] (lookup); Access only in the new plan: rag.AnalysisRequest Index Scan [IX_AnalysisRequest_Query]; rag.AnalysisRequest Clustered Index Seek [PK_AnalysisRequest] (lookup); perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; Join only in the old plan: Nested Loops (Inner Join) ×11; Merge Join (Right Anti Semi Join); Nested Loops (Left Anti Semi Join); Join only in the new plan: Nested Loops (Inner Join) ×10; Nested Loops (Left Anti Semi Join) ×2
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.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 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_0000000E_22DFFF17 was updated 2026-10-05 09:43 (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.AnalysisRecommendation._WA_Sys_00000006_22DFFF17 was updated 2026-10-05 08:59 (sampled 100%); 4 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.AnalysisRequest.PK_AnalysisRequest was updated 2026-10-05 09:56 (sampled 100%); 4 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.IX_AnalysisRequest_Query was updated 2026-10-05 09:50 (sampled 100%); 3 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_00000002_186270A4 was updated 2026-10-05 09:10 (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.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.QuerySnooze.PK_QuerySnooze was updated 2026-10-05 11:38 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.QuerySnooze._WA_Sys_00000002_5CB23D5A was updated 2026-10-05 11:41 (sampled 100%); the plans use this statistic.
Candidate cause: INDEX_NEW — rag.QueryLineage.PK_QueryLineage was created 2026-10-05 12:46, 7 hours before the change; the new plan uses this index.
Candidate cause: WORKLOAD — The plans see a different workload: rows per execution 22.09 → 1.35. The plans may have been compiled for different parameters or batch sizes.
Counter-evidence: INDEX_UNUSED — rag.QueryLineage.IX_QueryLineage_Old was created in the window but the new plan does not use it: candidate ruled out.
Confidence rationale: Medium: more than one candidate (STATS, INDEX_NEW, WORKLOAD) — competing explanations.
Note: this is a candidate cause, not proof of the root cause.