Plan değişimi P430

(@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)
                AND NOT EXISTS (SELECT 1 FROM perf.QdsQueryText AS it CROSS APPLY perf.fn_InternalQueryText(it.query_sql_text) AS iq WHERE it.TextHash = wq.TextHash)
              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 #P430 | DB: DB-11 | Improvement | 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) AND NOT EXISTS (SELECT ? FROM perf.QdsQueryText AS it CROSS APPLY perf.fn_InternalQueryText(it.query_sql_text) AS iq WHERE it.TextHash = wq.TextHash) 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-06 00:00 UTC, plan 64329 → 64390, pattern: Alternating (7 switches)
Impact (Query Store, per execution): duration 45.3 → 1.1 ms (-98%); CPU 45.2 → 1.1 ms (-98%); reads 44,648 → 289 (-99%); rows 1 → 1 (+0%)
Plan difference: Access only in the old plan: perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; perf.QdsPlan Clustered Index Seek [PK_QdsPlan]; perf.QdsQuery Clustered Index Seek [PK_QdsQuery]; Access only in the new plan: perf.QueryDailyStats Index Seek [IX_QueryDailyStats_Query]; perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats] (lookup); perf.QdsQuery Index Seek [IX_QdsQuery_QueryHash]; perf.QdsQuery Clustered Index Seek [PK_QdsQuery] (lookup); perf.QdsPlan Index Seek [IX_QdsPlan_Query]; Join only in the old plan: Nested Loops (Inner Join) ×3; Join only in the new plan: Nested Loops (Inner Join) ×7
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%); 6 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 23:06 (sampled 69.8%); 5 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 23:06 (sampled 69.8%); 5 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQueryText.PK_QdsQueryText was updated 2026-10-05 08:59 (sampled 100%); 3 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%); 7 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%); 7 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%); 8 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%); 7 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%); 8 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%); 7 updates in the window; the plans use this statistic.
Candidate cause: PARAM — The two plans alternate in the same period (7 switches): plan choice depends on the parameter value rather than an event (parameter sensitivity).
Counter-evidence: HIGH_VARIANCE — The new plan's duration standard deviation is 192% of the average: the average is not reliable.
Confidence rationale: Medium: more than one candidate (STATS, PARAM) — competing explanations. Weak counter-evidence: HIGH_VARIANCE.
Note: this is a candidate cause, not proof of the root cause.