Plan değişimi P431

(@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 #P431 | 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) 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-07 16:00 UTC, plan 64452 → 65794, pattern: Transition
Impact (Query Store, per execution): duration 1.3 → 39.4 ms (+2929%); CPU 1.3 → 39.4 ms (+2952%); reads 318 → 42,768 (+13342%); rows 1 → 1 (+0%)
Plan difference: Access only in the old plan: perf.QdsQuery Index Seek [IX_QdsQuery_QueryHash]; perf.QdsQuery Clustered Index Seek [PK_QdsQuery] (lookup); perf.QdsPlan Index Seek [IX_QdsPlan_Query]; Access only in the new plan: perf.QdsPlan Clustered Index Seek [PK_QdsPlan]; perf.QdsQuery Clustered Index Seek [PK_QdsQuery]; Join only in the old plan: Nested Loops (Inner Join) ×7; Join only in the new plan: Nested Loops (Inner Join) ×6
Candidate cause: STATS — Statistic perf.QdsPlan.PK_QdsPlan was updated 2026-10-06 01:10 (sampled 79%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_Query was updated 2026-10-06 01:10 (sampled 79.3%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000002_58671BC9 was updated 2026-10-06 01:10 (sampled 79.3%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000003_58671BC9 was updated 2026-10-06 01:10 (sampled 79.3%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_PlanHash was updated 2026-10-06 22:11 (sampled 79.1%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000004_58671BC9 was updated 2026-10-06 18:58 (sampled 79.1%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.PK_QdsQuery was updated 2026-10-06 01:10 (sampled 69.2%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.IX_QdsQuery_QueryHash was updated 2026-10-07 14:10 (sampled 67%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery._WA_Sys_00000002_54968AE5 was updated 2026-10-06 01:10 (sampled 69.2%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery._WA_Sys_00000003_54968AE5 was updated 2026-10-06 01:10 (sampled 69.2%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQuery.IX_QdsQuery_TextHash was updated 2026-10-07 14:10 (sampled 67%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsQueryText.PK_QdsQueryText was updated 2026-10-06 05:48 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats.PK_QdsWaitStats was updated 2026-10-06 06:10 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats._WA_Sys_00000002_5E1FF51F was updated 2026-10-06 06:10 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats._WA_Sys_00000008_5E1FF51F was updated 2026-10-06 06:10 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats._WA_Sys_00000003_5E1FF51F was updated 2026-10-06 06:10 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsWaitStats._WA_Sys_00000007_5E1FF51F was updated 2026-10-06 06:10 (sampled 100%); the plans use this statistic.
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_00000007_5D80D6A1 was updated 2026-10-06 19:19 (sampled 76.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._WA_Sys_00000018_5D80D6A1 was updated 2026-10-06 19:19 (sampled 76.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.
Counter-evidence: HIGH_VARIANCE — The old plan's duration standard deviation is 199% 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.