Plan değişimi P411

(@days int,@from date,@to date)SELECT q.ServerID, q.DatabaseName, q.query_hash,
           SUM(IIF(q.StatDateUtc >= @from, ISNULL(q.Executions, 0), 0)) AS CExec,
           SUM(IIF(q.StatDateUtc >= @from, ISNULL(q.TotalCpuMs, 0), 0)) AS CCpu,
           SUM(IIF(q.StatDateUtc >= @from, ISNULL(q.TotalDurationMs, 0), 0)) AS CDur,
           SUM(IIF(q.StatDateUtc >= @from, ISNULL(CAST(q.BlockedOthersCount AS bigint), 0), 0)) AS CBlock,
           SUM(IIF(q.StatDateUtc < @from, ISNULL(q.Executions, 0), 0)) AS PExec,
           SUM(IIF(q.StatDateUtc < @from, ISNULL(q.TotalDurationMs, 0), 0)) AS PDur
    INTO #s
    FROM perf.QueryDailyStats AS q
    JOIN DBA.ManagedServers AS ms ON ms.ServerID = q.ServerID AND ISNULL(ms.IsActive, 0) = 1
    JOIN DBA.MonitoredDatabase AS md ON md.ServerID = q.ServerID AND md.DatabaseName = q.DatabaseName AND md.IsActive = 1
    WHERE q.StatDateUtc >= DATEADD(day, -@days, CAST(@from AS datetime)) AND q.StatDateUtc <= @to
    GROUP BY q.ServerID, q.DatabaseName, q.query_hash

Plan change case #P411 | DB: DB-11 | Improvement | Confidence: Medium (review pending)
Query: (@days int,@from date,@to date)SELECT q.ServerID, q.DatabaseName, q.query_hash, SUM(IIF(q.StatDateUtc >= @from, ISNULL(q.Executions, ?), ?)) AS CExec, SUM(IIF(q.StatDateUtc >= @from, ISNULL(q.TotalCpuMs, ?), ?)) AS CCpu, SUM(IIF(q.StatDateUtc >= @from, ISNULL(q.TotalDurationMs, ?), ?)) AS CDur, SUM(IIF(q.StatDateUtc >= @from, ISNULL(CAST(q.BlockedOthersCount AS bigint), ?), ?)) AS CBlock, SUM(IIF(q.StatDateUtc < @from, ISNULL(q.Executions, ?), ?)) AS PExec, SUM(IIF(q.StatDateUtc < @from, ISNULL(q.TotalDurationMs, ?), ?)) AS PDur INTO #s FROM perf.QueryDailyStats AS q JOIN DBA.ManagedServers AS ms ON ms.ServerID = q.ServerID AND ISNULL(ms.IsActive, ?) = ? JOIN DBA.MonitoredDatabase AS md ON md.ServerID = q.ServerID AND md.DatabaseName = q.DatabaseName AND md.IsActive = ? WHERE q.StatDateUtc >= DATEADD(day, -@days, CAST(@from AS datetime)) AND q.StatDateUtc <= @to GROUP BY q.ServerID, q.DatabaseName, q.query_hash
Change: 2026-10-01 21:00 UTC, plan 30730 → 30856, pattern: Alternating (7 switches)
Impact (Query Store, per execution): duration 112 → 1.4 ms (-99%); CPU 111.9 → 1.4 ms (-99%); reads 40,898 → 123 (-100%); rows 4198.83 → 0 (-100%)
Plan difference: Access only in the old plan: DBA.ManagedServers Clustered Index Scan [PK__ManagedS__C56AC8865E257CFD]; perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; Access only in the new plan: perf.QueryDailyStats Table Scan; DBA.ManagedServers Clustered Index Seek [PK__ManagedS__C56AC8865E257CFD]; Join only in the old plan: Hash Match (Inner Join); Nested Loops (Inner Join); Join only in the new plan: Nested Loops (Inner Join) ×2
Candidate cause: STATS — Statistic perf.QueryDailyStats.PK_QueryDailyStats was updated 2026-09-30 22:56 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000003_5D80D6A1 was updated 2026-09-30 22:56 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats.IX_QueryDailyStats_Query was updated 2026-09-30 22:56 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: WORKLOAD — The plans see a different workload: rows per execution 4198.83 → 0. The plans may have been compiled for different parameters or batch sizes.
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: LOW_SAMPLE — The old plan ran only 36 times: the sample is small and the average is weak.
Counter-evidence: METADATA_GAP — Metadata collection started on 2026-10-01 and the change was on 2026-10-01: index, statistics and module events could not be evaluated (no data, not no events).
Confidence rationale: Medium: more than one candidate (STATS, WORKLOAD + PARAM) — competing explanations. Weak counter-evidence: LOW_SAMPLE, METADATA_GAP.
Note: this is a candidate cause, not proof of the root cause.