Plan değişimi P428

(@srv int,@dbName nvarchar(4000),@hash varbinary(8),@from datetime,@to datetime)SELECT (SELECT ServerName FROM DBA.ManagedServers WHERE ServerID = @srv) AS ServerName,
       MAX(ObjectName) AS ObjectName,
       (SELECT TOP (1) SampleTextHash FROM perf.QueryDailyStats WHERE ServerID = @srv AND DatabaseName = @dbName AND query_hash = @hash
          AND StatDateUtc BETWEEN @from AND @to AND SampleTextHash IS NOT NULL ORDER BY StatDateUtc DESC) AS SampleTextHash,
       -- K2-5: wait kategorileri dönem toplamı (ilk 3), rollup'ın son günü değil
       (SELECT STRING_AGG(CONCAT(w.Category, N':', CAST(w.TotalMs AS nvarchar(20))), N',') WITHIN GROUP (ORDER BY w.TotalMs DESC)
        FROM (SELECT TOP (3) ws.wait_category_desc AS Category, SUM(ws.total_query_wait_time_ms) AS TotalMs
              FROM perf.QdsWaitStats AS ws
              JOIN perf.QdsPlan AS wp ON wp.ServerID = ws.ServerID AND wp.DatabaseName = ws.DatabaseName AND wp.plan_id = ws.plan_id
              JOIN perf.QdsQuery AS wq ON wq.ServerID = wp.ServerID AND wq.DatabaseName = wp.DatabaseName AND wq.query_id = wp.query_id
              WHERE ws.ServerID = @srv AND ws.DatabaseName = @dbName AND wq.query_hash = @hash
                AND ws.IntervalStartUtc >= @from AND ws.IntervalStartUtc < DATEADD(day, 1, @to)
              GROUP BY ws.wait_category_desc ORDER BY SUM(ws.total_query_wait_time_ms) DESC) AS w) AS TopWaitCategories,
       (SELECT TOP (1) SessionTopWaits FROM perf.QueryDailyStats WHERE ServerID = @srv AND DatabaseName = @dbName AND query_hash = @hash
          AND StatDateUtc BETWEEN @from AND @to AND SessionTopWaits IS NOT NULL ORDER BY StatDateUtc DESC) AS SessionTopWaits,
       ISNULL(SUM(CAST(BlockedOthersCount AS bigint)), 0) AS BlockedOthersCount, MAX(MaxGrantedMemoryKb) AS MaxGrantedMemoryKb
FROM perf.QueryDailyStats
WHERE ServerID = @srv AND DatabaseName = @dbName AND query_hash = @hash AND StatDateUtc BETWEEN @from AND @to

Plan change case #P428 | DB: DB-11 | Regression | Confidence: Medium (review pending)
Query: (@srv int,@dbName nvarchar(?),@hash varbinary(?),@from datetime,@to datetime)SELECT (SELECT ServerName FROM DBA.ManagedServers WHERE ServerID = @srv) AS ServerName, MAX(ObjectName) AS ObjectName, (SELECT TOP (?) SampleTextHash FROM perf.QueryDailyStats WHERE ServerID = @srv AND DatabaseName = @dbName AND query_hash = @hash AND StatDateUtc BETWEEN @from AND @to AND SampleTextHash IS NOT NULL ORDER BY StatDateUtc DESC) AS SampleTextHash, (SELECT STRING_AGG(CONCAT(w.Category, ?, CAST(w.TotalMs AS nvarchar(?))), ?) WITHIN GROUP (ORDER BY w.TotalMs DESC) FROM (SELECT TOP (?) ws.wait_category_desc AS Category, SUM(ws.total_query_wait_time_ms) AS TotalMs FROM perf.QdsWaitStats AS ws JOIN perf.QdsPlan AS wp ON wp.ServerID = ws.ServerID AND wp.DatabaseName = ws.DatabaseName AND wp.plan_id = ws.plan_id JOIN perf.QdsQuery AS wq ON wq.ServerID = wp.ServerID AND wq.DatabaseName = wp.DatabaseName AND wq.query_id = wp.query_id WHERE ws.ServerID = @srv AND ws.DatabaseName = @dbName AND wq.query_hash = @hash AND ws.IntervalStartUtc >= @from AND ws.IntervalStartUtc < DATEADD(day, ?, @to) GROUP BY ws.wait_category_desc ORDER BY SUM(ws.total_query_wait_time_ms) DESC) AS w) AS TopWaitCategories, (SELECT TOP (?) SessionTopWaits FROM perf.QueryDailyStats WHERE ServerID = @srv AND DatabaseName = @dbName AND query_hash = @hash AND StatDateUtc BETWEEN @from AND @to AND SessionTopWaits IS NOT NULL ORDER BY StatDateUtc DESC) AS SessionTopWaits, ISNULL(SUM(CAST(BlockedOthersCount AS bigint)), ?) AS BlockedOthersCount, MAX(MaxGrantedMemoryKb) AS MaxGrantedMemoryKb FROM perf.QueryDailyStats WHERE ServerID = @srv AND DatabaseName = @dbName AND query_hash = @hash AND StatDateUtc BETWEEN @from AND @to
Change: 2026-10-05 22:00 UTC, plan 60967 → 62543, pattern: Transition
Impact (Query Store, per execution): duration 0.6 → 51.3 ms (+8557%); CPU 0.6 → 51.2 ms (+8584%); reads 196 → 51,165 (+26042%); rows 1 → 1 (+0%)
Plan difference: Access only in the old plan: perf.QueryDailyStats Index Seek [IX_QueryDailyStats_Query]; perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats] (lookup); perf.QdsPlan Index Seek [IX_QdsPlan_Query]; Access only in the new plan: perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; perf.QdsPlan Clustered Index Seek [PK_QdsPlan]; Join only in the old plan: Nested Loops (Inner Join) ×6; 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.QdsWaitStats.PK_QdsWaitStats was updated 2026-10-05 09:43 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats._WA_Sys_00000002_5E1FF51F was updated 2026-10-05 09:43 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats._WA_Sys_00000008_5E1FF51F was updated 2026-10-05 09:43 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats._WA_Sys_00000003_5E1FF51F was updated 2026-10-05 09:43 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats._WA_Sys_00000007_5E1FF51F was updated 2026-10-05 09:57 (sampled 100%); 2 updates in the window; the plans use this statistic.
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_00000007_5D80D6A1 was updated 2026-10-05 09:49 (sampled 100%); 7 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._WA_Sys_00000018_5D80D6A1 was updated 2026-10-05 09:49 (sampled 100%); 7 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.
Counter-evidence: HIGH_VARIANCE — The old plan's duration standard deviation is 114% 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.