Plan değişimi P413

(@name nvarchar(128),@schema nvarchar(128),@like nvarchar(520),@FromUtc datetime,@ToUtc datetime,@Top int)WITH o AS (
        SELECT mo.ServerID, ms.ServerName, mo.DatabaseName, mo.SchemaName, mo.ObjectName, mo.ObjectType, mo.ModifyDateUtc
        FROM perf.MetaObject AS mo
        JOIN DBA.ManagedServers AS ms ON ms.ServerID = mo.ServerID AND ISNULL(ms.IsActive, 0) = 1
        JOIN DBA.MonitoredDatabase AS md ON md.ServerID = mo.ServerID AND md.DatabaseName = mo.DatabaseName AND md.IsActive = 1
        WHERE mo.IsDeleted = 0 AND mo.ObjectType IN ('P ', 'FN', 'IF', 'TF', 'V ', 'TR')
          AND ((@name IS NOT NULL AND mo.SchemaName = @schema AND mo.ObjectName = @name)
               OR (@name IS NULL AND mo.ObjectName LIKE @like ESCAPE N'\'))
    )
    SELECT TOP (@Top) o.ServerID AS ServerId, o.ServerName, o.DatabaseName, o.SchemaName, o.ObjectName, RTRIM(o.ObjectType) AS ObjectType,
           o.ModifyDateUtc, ISNULL(a.Statements, 0) AS Statements, ISNULL(a.Executions, 0) AS Executions, ISNULL(a.TotalDurationMs, 0) AS TotalDurationMs
    FROM o
    OUTER APPLY (SELECT COUNT(DISTINCT q.query_hash) AS Statements, SUM(q.Executions) AS Executions, SUM(q.TotalDurationMs) AS TotalDurationMs
                 FROM perf.QueryDailyStats AS q
                 WHERE q.StatDateUtc >= @FromUtc AND q.StatDateUtc <= @ToUtc
                   AND q.ServerID = o.ServerID AND q.DatabaseName = o.DatabaseName
                   AND q.ObjectName = CONCAT(o.SchemaName, N'.', o.ObjectName)) AS a
    ORDER BY ISNULL(a.TotalDurationMs, 0) DESC, o.ObjectName, o.DatabaseName

Plan change case #P413 | DB: DB-11 | Improvement | Confidence: Medium (review pending)
Query: (@name nvarchar(?),@schema nvarchar(?),@like nvarchar(?),@FromUtc datetime,@ToUtc datetime,@Top int)WITH o AS ( SELECT mo.ServerID, ms.ServerName, mo.DatabaseName, mo.SchemaName, mo.ObjectName, mo.ObjectType, mo.ModifyDateUtc FROM perf.MetaObject AS mo JOIN DBA.ManagedServers AS ms ON ms.ServerID = mo.ServerID AND ISNULL(ms.IsActive, ?) = ? JOIN DBA.MonitoredDatabase AS md ON md.ServerID = mo.ServerID AND md.DatabaseName = mo.DatabaseName AND md.IsActive = ? WHERE mo.IsDeleted = ? AND mo.ObjectType IN (?) AND ((@name IS NOT NULL AND mo.SchemaName = @schema AND mo.ObjectName = @name) OR (@name IS NULL AND mo.ObjectName LIKE @like ESCAPE ?)) ) SELECT TOP (@Top) o.ServerID AS ServerId, o.ServerName, o.DatabaseName, o.SchemaName, o.ObjectName, RTRIM(o.ObjectType) AS ObjectType, o.ModifyDateUtc, ISNULL(a.Statements, ?) AS Statements, ISNULL(a.Executions, ?) AS Executions, ISNULL(a.TotalDurationMs, ?) AS TotalDurationMs FROM o OUTER APPLY (SELECT COUNT(DISTINCT q.query_hash) AS Statements, SUM(q.Executions) AS Executions, SUM(q.TotalDurationMs) AS TotalDurationMs FROM perf.QueryDailyStats AS q WHERE q.StatDateUtc >= @FromUtc AND q.StatDateUtc <= @ToUtc AND q.ServerID = o.ServerID AND q.DatabaseName = o.DatabaseName AND q.ObjectName = CONCAT(o.SchemaName, ?, o.ObjectName)) AS a ORDER BY ISNULL(a.TotalDurationMs, ?) DESC, o.ObjectName, o.DatabaseName
Change: 2026-10-07 10:00 UTC, plan 46804 → 64800, pattern: Transition
Impact (Query Store, per execution): duration 896.3 → 157.5 ms (-82%); CPU 894.8 → 156.7 ms (-82%); reads 145,505 → 83,381 (-43%); rows 67.53 → 67.5 (-0%)
Plan difference: Access only in the old plan: perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; Access only in the new plan: perf.QueryDailyStats Clustered Index Scan [PK_QueryDailyStats]; Warning only in the old plan: ColumnsWithNoStatistics; Estimated cost: 1.968 → 4.99; estimated rows: 100 → 100
Candidate cause: STATS — Statistic DBA.MonitoredDatabase.PK_MonitoredDatabase was updated 2026-10-06 19:19 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic DBA.MonitoredDatabase._WA_Sys_00000003_10216507 was updated 2026-10-05 17:58 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic DBA.MonitoredDatabase._WA_Sys_00000002_10216507 was updated 2026-10-06 19:19 (sampled 100%); the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_0000000B_031C6FA4 was updated 2026-10-07 05:56 (sampled 100%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_00000005_031C6FA4 was updated 2026-10-06 22:15 (sampled 100%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_00000006_031C6FA4 was updated 2026-10-07 05:56 (sampled 100%); 3 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_00000004_031C6FA4 was updated 2026-10-06 22:15 (sampled 100%); 3 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_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_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 perf.QueryDailyStats._WA_Sys_00000006_5D80D6A1 was updated 2026-10-06 03:00 (sampled 76.5%); 2 updates in the window; the plans use this statistic.
Candidate cause: GROWTH — From the old plan's period to the change, DBA.MonitoredDatabase rows changed +21% and size 0% (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).
Counter-evidence: TREND_BEFORE — The old plan was already drifting before the change (avg 937.6 → 19.7 ms): part of the duration difference is independent of the plan change.
Confidence rationale: Medium: more than one candidate (STATS, GROWTH) — competing explanations. Weak counter-evidence: TREND_BEFORE.
Note: this is a candidate cause, not proof of the root cause.