Plan değişimi P408

(@from datetime,@asOf datetime,@ServerID int,@DatabaseName nvarchar(128),@minExec int)SELECT rs.ServerID, rs.DatabaseName, p.query_id, rs.plan_id,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.count_executions ELSE 0 END) AS Execs,
               SUM(CASE WHEN rs.execution_type = 3 THEN rs.count_executions ELSE 0 END) AS Aborted,
               SUM(CASE WHEN rs.execution_type = 4 THEN rs.count_executions ELSE 0 END) AS Failed,
               MIN(CASE WHEN rs.execution_type = 0 THEN rs.IntervalStartUtc END) AS FirstUtc,
               MAX(CASE WHEN rs.execution_type = 0 THEN rs.IntervalEndUtc END) AS LastUtc,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_duration * rs.count_executions END) AS SumDur,
               MAX(CASE WHEN rs.execution_type = 0 THEN rs.max_duration END) AS MaxDur,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.count_executions * (SQUARE(ISNULL(rs.stdev_duration, 0)) + SQUARE(rs.avg_duration)) END) AS SumSq,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_cpu_time * rs.count_executions END) AS SumCpu,
               MAX(CASE WHEN rs.execution_type = 0 THEN rs.max_cpu_time END) AS MaxCpu,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_logical_io_reads * rs.count_executions END) AS SumReads,
               MAX(CASE WHEN rs.execution_type = 0 THEN rs.max_logical_io_reads END) AS MaxReads,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_physical_io_reads * rs.count_executions END) AS SumPhys,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_logical_io_writes * rs.count_executions END) AS SumWrites,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_query_max_used_memory * rs.count_executions END) AS SumMem,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_tempdb_space_used * rs.count_executions END) AS SumTempdb,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_dop * rs.count_executions END) AS SumDop,
               SUM(CASE WHEN rs.execution_type = 0 THEN rs.avg_rowcount * rs.count_executions END) AS SumRows,
               MAX(CASE WHEN rs.execution_type = 0 THEN rs.max_rowcount END) AS MaxRows
        INTO #ps
        FROM perf.QdsRuntimeStats AS rs
        JOIN perf.QdsPlan AS p ON p.ServerID = rs.ServerID AND p.DatabaseName = rs.DatabaseName AND p.plan_id = rs.plan_id
        JOIN DBA.MonitoredDatabase AS md ON md.ServerID = rs.ServerID AND md.DatabaseName = rs.DatabaseName
        WHERE md.IsActive = 1 AND md.CollectQds = 1
          AND rs.IntervalStartUtc >= @from AND rs.IntervalStartUtc < @asOf
          AND (@ServerID IS NULL OR rs.ServerID = @ServerID)
          AND (@DatabaseName IS NULL OR rs.DatabaseName = @DatabaseName)
        GROUP BY rs.ServerID, rs.DatabaseName, p.query_id, rs.plan_id
        HAVING SUM(CASE WHEN rs.execution_type = 0 THEN rs.count_executions ELSE 0 END) >= @minExec

Plan change case #P408 | DB: DB-11 | Regression | Confidence: Low (review pending)
Query: (@from datetime,@asOf datetime,@ServerID int,@DatabaseName nvarchar(?),@minExec int)SELECT rs.ServerID, rs.DatabaseName, p.query_id, rs.plan_id, SUM(CASE WHEN rs.execution_type = ? THEN rs.count_executions ELSE ? END) AS Execs, SUM(CASE WHEN rs.execution_type = ? THEN rs.count_executions ELSE ? END) AS Aborted, SUM(CASE WHEN rs.execution_type = ? THEN rs.count_executions ELSE ? END) AS Failed, MIN(CASE WHEN rs.execution_type = ? THEN rs.IntervalStartUtc END) AS FirstUtc, MAX(CASE WHEN rs.execution_type = ? THEN rs.IntervalEndUtc END) AS LastUtc, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_duration * rs.count_executions END) AS SumDur, MAX(CASE WHEN rs.execution_type = ? THEN rs.max_duration END) AS MaxDur, SUM(CASE WHEN rs.execution_type = ? THEN rs.count_executions * (SQUARE(ISNULL(rs.stdev_duration, ?)) + SQUARE(rs.avg_duration)) END) AS SumSq, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_cpu_time * rs.count_executions END) AS SumCpu, MAX(CASE WHEN rs.execution_type = ? THEN rs.max_cpu_time END) AS MaxCpu, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_logical_io_reads * rs.count_executions END) AS SumReads, MAX(CASE WHEN rs.execution_type = ? THEN rs.max_logical_io_reads END) AS MaxReads, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_physical_io_reads * rs.count_executions END) AS SumPhys, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_logical_io_writes * rs.count_executions END) AS SumWrites, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_query_max_used_memory * rs.count_executions END) AS SumMem, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_tempdb_space_used * rs.count_executions END) AS SumTempdb, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_dop * rs.count_executions END) AS SumDop, SUM(CASE WHEN rs.execution_type = ? THEN rs.avg_rowcount * rs.count_executions END) AS SumRows, MAX(CASE WHEN rs.execution_type = ? THEN rs.max_rowcount END) AS MaxRows INTO #ps FROM perf.QdsRuntimeStats AS rs JOIN perf.QdsPlan AS p ON p.ServerID = rs.ServerID AND p.DatabaseName = rs.DatabaseName AND p.plan_id = rs.plan_id JOIN DBA.MonitoredDatabase AS md ON md.ServerID = rs.ServerID AND md.DatabaseName = rs.DatabaseName WHERE md.IsActive = ? AND md.CollectQds = ? AND rs.IntervalStartUtc >= @from AND rs.IntervalStartUtc < @asOf AND (@ServerID IS NULL OR rs.ServerID = @ServerID) AND (@DatabaseName IS NULL OR rs.DatabaseName = @DatabaseName) GROUP BY rs.ServerID, rs.DatabaseName, p.query_id, rs.plan_id HAVING SUM(CASE WHEN rs.execution_type = ? THEN rs.count_executions ELSE ? END) >= @minExec
Change: 2026-10-01 21:00 UTC, plan 7645 → 30664, pattern: Alternating (17 switches)
Impact (Query Store, per execution): duration 2.4 → 1373.1 ms (+57110%); CPU 2.4 → 1275.6 ms (+53132%); reads 242 → 8,702 (+3493%); rows 3.75 → 5000.19 (+133131%)
Plan difference: Access only in the old plan: perf.QdsPlan Table Scan; DBA.MonitoredDatabase Table Scan; perf.QdsRuntimeStats Table Scan; Access only in the new plan: perf.QdsPlan Clustered Index Scan [PK_QdsPlan]; DBA.MonitoredDatabase Clustered Index Scan [PK_MonitoredDatabase]; perf.QdsRuntimeStats Clustered Index Seek [PK_QdsRuntimeStats]; Join only in the old plan: Nested Loops (Inner Join) ×2; Join only in the new plan: Merge Join (Inner Join); Nested Loops (Inner Join)
Candidate cause: STATS — Statistic perf.QdsPlan.PK_QdsPlan was updated 2026-09-30 22:17 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_Query was updated 2026-09-30 22:17 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000002_58671BC9 was updated 2026-09-30 22:17 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan._WA_Sys_00000003_58671BC9 was updated 2026-09-30 22:17 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsPlan.IX_QdsPlan_PlanHash was updated 2026-09-30 21:15 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats.PK_QdsRuntimeStats was updated 2026-09-29 22:11 (sampled 87.2%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000002_5C37ACAD was updated 2026-09-29 22:11 (sampled 87.2%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QdsRuntimeStats._WA_Sys_00000003_5C37ACAD was updated 2026-09-29 22:11 (sampled 87.2%); 2 updates in the window; the plans use this statistic.
Candidate cause: WORKLOAD — The plans see a different workload: rows per execution 3.75 → 5000.19. The plans may have been compiled for different parameters or batch sizes.
Candidate cause: PARAM — The two plans alternate in the same period (17 switches): plan choice depends on the parameter value rather than an event (parameter sensitivity).
Counter-evidence: WORK_PROPORTIONAL — Rows per execution changed 1332.31× and duration 572.1×; duration per row 0.6395 → 0.2746 ms: the duration difference comes from the amount of data processed, not the plan.
Counter-evidence: LOW_SAMPLE — The new plan ran only 26 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: Low: strong counter-evidence (WORK_PROPORTIONAL).
Note: this is a candidate cause, not proof of the root cause.