Plan değişimi P426

(@from datetime,@to datetime,@srv int,@dbName nvarchar(4000),@minExec bigint,@len int,@regMin bigint,@regression bit,@regRatio float,@candidates bit,@qHash varbinary(8000),@qId bigint,@qText nvarchar(4000))WITH cur AS (
    SELECT ServerID, DatabaseName, query_hash,
           ISNULL(SUM(Executions), 0) AS Executions, ISNULL(SUM(TotalCpuMs), 0) AS TotalCpuMs, ISNULL(SUM(TotalDurationMs), 0) AS TotalDurationMs,
           MAX(P95DurationMs) AS P95DurationMs, ISNULL(SUM(TotalLogicalReads), 0) AS TotalLogicalReads,
           ISNULL(SUM(CAST(BlockedOthersCount AS bigint)), 0) AS BlockedOthersCount, ISNULL(MAX(PlanCount), 0) AS PlanCount,
           COUNT(*) AS DayCount, MAX(ObjectName) AS ObjectName
    FROM perf.QueryDailyStats
    WHERE StatDateUtc BETWEEN @from AND @to
      AND (@srv IS NULL OR ServerID = @srv) AND (@dbName IS NULL OR DatabaseName = @dbName)
    GROUP BY ServerID, DatabaseName, query_hash
    HAVING ISNULL(SUM(Executions), 0) >= @minExec
),
prev AS (   -- önceki eşit uzunluktaki dönem (kötüleşme)
    SELECT ServerID, DatabaseName, query_hash, SUM(Executions) AS Executions, SUM(TotalDurationMs) AS TotalDurationMs
    FROM perf.QueryDailyStats
    WHERE StatDateUtc BETWEEN DATEADD(day, -@len, @from) AND DATEADD(day, -1, @from)
      AND (@srv IS NULL OR ServerID = @srv) AND (@dbName IS NULL OR DatabaseName = @dbName)
    GROUP BY ServerID, DatabaseName, query_hash
),
ranked AS (
    SELECT c.*,
           CASE WHEN c.Executions >= @regMin AND p.Executions >= @regMin AND p.TotalDurationMs > 0
                THEN (c.TotalDurationMs / c.Executions) / (p.TotalDurationMs / p.Executions) END AS RegressionRatio
    FROM cur AS c
    LEFT JOIN prev AS p ON p.ServerID = c.ServerID AND p.DatabaseName = c.DatabaseName AND p.query_hash = c.query_hash
)
SELECT c.*, ISNULL(t.State, 'New') AS State, qc.Reasons AS CandidateReasons
INTO #r
FROM ranked AS c
LEFT JOIN #t AS t ON t.ServerID = c.ServerID AND t.DatabaseName = c.DatabaseName AND t.QueryHash = c.query_hash
LEFT JOIN rag.QueryCandidate AS qc ON qc.ServerID = c.ServerID AND qc.DatabaseName = c.DatabaseName AND qc.QueryHash = c.query_hash
WHERE (@regression = 0 OR c.RegressionRatio >= @regRatio)
  AND (@candidates = 0 OR qc.Reasons IS NOT NULL)
  AND (@qHash IS NULL OR c.query_hash = @qHash)
  AND (@qId IS NULL OR EXISTS (SELECT 1 FROM perf.QdsQuery AS q
                               WHERE q.ServerID = c.ServerID AND q.DatabaseName = c.DatabaseName AND q.query_hash = c.query_hash AND q.query_id = @qId))
  -- Metin: nesne adı ya da dönemdeki örnek metinlerden biri (yalnız süzülmüş dönem satırlarında; ham metin taranmaz)
  AND (@qText IS NULL OR c.ObjectName LIKE @qText
       OR EXISTS (SELECT 1 FROM perf.QueryDailyStats AS d
                  LEFT JOIN perf.QdsQueryText AS qt ON qt.TextHash = d.SampleTextHash
                  LEFT JOIN perf.SqlText AS st ON st.TextHash = d.SampleTextHash
                  WHERE d.ServerID = c.ServerID AND d.DatabaseName = c.DatabaseName AND d.query_hash = c.query_hash
                    AND d.StatDateUtc BETWEEN @from AND @to AND d.SampleTextHash IS NOT NULL
                    AND (qt.query_sql_text LIKE @qText OR st.SqlText LIKE @qText)))

Plan change case #P426 | DB: DB-11 | Improvement | Confidence: Medium (review pending)
Query: (@from datetime,@to datetime,@srv int,@dbName nvarchar(?),@minExec bigint,@len int,@regMin bigint,@regression bit,@regRatio float,@candidates bit,@qHash varbinary(?),@qId bigint,@qText nvarchar(?))WITH cur AS ( SELECT ServerID, DatabaseName, query_hash, ISNULL(SUM(Executions), ?) AS Executions, ISNULL(SUM(TotalCpuMs), ?) AS TotalCpuMs, ISNULL(SUM(TotalDurationMs), ?) AS TotalDurationMs, MAX(P95DurationMs) AS P95DurationMs, ISNULL(SUM(TotalLogicalReads), ?) AS TotalLogicalReads, ISNULL(SUM(CAST(BlockedOthersCount AS bigint)), ?) AS BlockedOthersCount, ISNULL(MAX(PlanCount), ?) AS PlanCount, COUNT(*) AS DayCount, MAX(ObjectName) AS ObjectName FROM perf.QueryDailyStats WHERE StatDateUtc BETWEEN @from AND @to AND (@srv IS NULL OR ServerID = @srv) AND (@dbName IS NULL OR DatabaseName = @dbName) GROUP BY ServerID, DatabaseName, query_hash HAVING ISNULL(SUM(Executions), ?) >= @minExec ), prev AS ( SELECT ServerID, DatabaseName, query_hash, SUM(Executions) AS Executions, SUM(TotalDurationMs) AS TotalDurationMs FROM perf.QueryDailyStats WHERE StatDateUtc BETWEEN DATEADD(day, -@len, @from) AND DATEADD(day, -?, @from) AND (@srv IS NULL OR ServerID = @srv) AND (@dbName IS NULL OR DatabaseName = @dbName) GROUP BY ServerID, DatabaseName, query_hash ), ranked AS ( SELECT c.*, CASE WHEN c.Executions >= @regMin AND p.Executions >= @regMin AND p.TotalDurationMs > ? THEN (c.TotalDurationMs / c.Executions) / (p.TotalDurationMs / p.Executions) END AS RegressionRatio FROM cur AS c LEFT JOIN prev AS p ON p.ServerID = c.ServerID AND p.DatabaseName = c.DatabaseName AND p.query_hash = c.query_hash ) SELECT c.*, ISNULL(t.State, ?) AS State, qc.Reasons AS CandidateReasons INTO #r FROM ranked AS c LEFT JOIN #t AS t ON t.ServerID = c.ServerID AND t.DatabaseName = c.DatabaseName AND t.QueryHash = c.query_hash LEFT JOIN rag.QueryCandidate AS qc ON qc.ServerID = c.ServerID AND qc.DatabaseName = c.DatabaseName AND qc.QueryHash = c.query_hash WHERE (@regression = ? OR c.RegressionRatio >= @regRatio) AND (@candidates = ? OR qc.Reasons IS NOT NULL) AND (@qHash IS NULL OR c.query_hash = @qHash) AND (@qId IS NULL OR EXISTS (SELECT ? FROM perf.QdsQuery AS q WHERE q.ServerID = c.ServerID AND q.DatabaseName = c.DatabaseName AND q.query_hash = c.query_hash AND q.query_id = @qId)) AND (@qText IS NULL OR c.ObjectName LIKE @qText OR EXISTS (SELECT ? FROM perf.QueryDailyStats AS d LEFT JOIN perf.QdsQueryText AS qt ON qt.TextHash = d.SampleTextHash LEFT JOIN perf.SqlText AS st ON st.TextHash = d.SampleTextHash WHERE d.ServerID = c.ServerID AND d.DatabaseName = c.DatabaseName AND d.query_hash = c.query_hash AND d.StatDateUtc BETWEEN @from AND @to AND d.SampleTextHash IS NOT NULL AND (qt.query_sql_text LIKE @qText OR st.SqlText LIKE @qText)))
Change: 2026-10-07 10:00 UTC, plan 62548 → 64806, pattern: Transition
Impact (Query Store, per execution): duration 5459.6 → 2.5 ms (-100%); CPU 5451.9 → 2.5 ms (-100%); reads 629,960 → 270 (-100%); rows 930.89 → 3 (-100%)
Plan difference: Access only in the old plan: perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; perf.SqlText Clustered Index Seek [PK_perf_SqlText]; Access only in the new plan: perf.QueryDailyStats Index Scan [IX_QueryDailyStats_Query]; perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats] (lookup); perf.QueryDailyStats Index Seek [IX_QueryDailyStats_Query]; perf.SqlText Clustered Index Scan [PK_perf_SqlText]; Join only in the new plan: Nested Loops (Inner Join) ×3; Estimated cost: 0.037 → 0.487; estimated rows: 1 → 26.3
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-06 17:10 (sampled 67%); 2 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-06 17:10 (sampled 67%); 2 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.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_00000008_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_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.IX_QueryDailyStats_Query was updated 2026-10-06 19:19 (sampled 76.6%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.QueryCandidate.PK_QueryCandidate was updated 2026-10-06 00:45 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.QueryCandidate._WA_Sys_00000002_79DEE6A5 was updated 2026-10-06 00:45 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.QueryCandidate._WA_Sys_00000003_79DEE6A5 was updated 2026-10-06 00:45 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.QueryCandidate._WA_Sys_00000004_79DEE6A5 was updated 2026-10-06 16:35 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: GROWTH — From the old plan's period to the change, perf.QdsQuery rows changed +43% and size +42% (2026-10-05 → 2026-10-07).
Candidate cause: GROWTH — From the old plan's period to the change, perf.QueryDailyStats rows changed +4% and size +59% (2026-10-05 → 2026-10-07).
Candidate cause: GROWTH — From the old plan's period to the change, perf.SqlText rows changed +32% and size +53% (2026-10-05 → 2026-10-07).
Candidate cause: WORKLOAD — The plans see a different workload: rows per execution 930.89 → 3. The plans may have been compiled for different parameters or batch sizes.
Counter-evidence: HIGH_VARIANCE — The old plan's duration standard deviation is 139% of the average: the average is not reliable.
Counter-evidence: LOW_SAMPLE — The new plan ran only 20 times: the sample is small and the average is weak.
Confidence rationale: Medium: more than one candidate (STATS, GROWTH, WORKLOAD) — competing explanations. Weak counter-evidence: HIGH_VARIANCE, LOW_SAMPLE.
Note: this is a candidate cause, not proof of the root cause.