Plan değişimi P425

(@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 #P425 | DB: DB-11 | Regression | 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-05 22:00 UTC, plan 54132 → 62548, pattern: Alternating (7 switches)
Impact (Query Store, per execution): duration 34.1 → 5459.6 ms (+15888%); CPU 32.7 → 5451.9 ms (+16587%); reads 12,865 → 629,960 (+4797%); rows 473.63 → 930.89 (+97%)
Plan difference: Access only in the old plan: perf.QueryDailyStats Index Scan [IX_QueryDailyStats_Query]; perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats] (lookup); perf.QueryDailyStats Index Seek [IX_QueryDailyStats_Query]; Access only in the new plan: perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; Join only in the old plan: Nested Loops (Inner Join) ×3; Estimated cost: 0.127 → 0.037; estimated rows: 1 → 1
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 03:12 (sampled 87.1%); 4 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 03:12 (sampled 87.1%); 4 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%); 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%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000008_5D80D6A1 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%); 6 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%); 7 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%); 6 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%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic rag.QueryCandidate.PK_QueryCandidate was updated 2026-10-05 00:28 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.QueryCandidate._WA_Sys_00000002_79DEE6A5 was updated 2026-10-05 00:28 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.QueryCandidate._WA_Sys_00000003_79DEE6A5 was updated 2026-10-05 00:28 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic rag.QueryCandidate._WA_Sys_00000004_79DEE6A5 was updated 2026-10-05 00:28 (sampled 100%); 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 old plan's duration standard deviation is 211% of the average: the average is not reliable.
Counter-evidence: HIGH_VARIANCE — The new plan's duration standard deviation is 139% 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.