Plan değişimi P412

(@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 #P412 | DB: DB-11 | Regression | 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-05 09:00 UTC, plan 37016 → 46804, pattern: Transition
Impact (Query Store, per execution): duration 81.3 → 896.3 ms (+1002%); CPU 81.1 → 894.8 ms (+1003%); reads 44,100 → 145,505 (+230%); rows 77.81 → 67.53 (-13%)
Plan difference: Access only in the old plan: DBA.MonitoredDatabase Clustered Index Seek [PK_MonitoredDatabase]; perf.QueryDailyStats Clustered Index Scan [PK_QueryDailyStats]; Access only in the new plan: DBA.MonitoredDatabase Clustered Index Scan [PK_MonitoredDatabase]; perf.QueryDailyStats Clustered Index Seek [PK_QueryDailyStats]; 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.MetaObject.PK_MetaObject was updated 2026-10-05 08:58 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject.IX_MetaObject_Name was updated 2026-10-05 08:58 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_00000002_031C6FA4 was updated 2026-10-05 08:58 (sampled 100%); 2 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_0000000B_031C6FA4 was updated 2026-10-05 02:02 (sampled 100%); 13 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_00000005_031C6FA4 was updated 2026-10-05 08:59 (sampled 100%); 19 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_00000006_031C6FA4 was updated 2026-10-05 02:05 (sampled 100%); 13 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.MetaObject._WA_Sys_00000004_031C6FA4 was updated 2026-10-05 08:59 (sampled 100%); 19 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats.PK_QueryDailyStats was updated 2026-10-05 03:00 (sampled 100%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000003_5D80D6A1 was updated 2026-10-05 03:00 (sampled 100%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000004_5D80D6A1 was updated 2026-10-05 03:00 (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 03:00 (sampled 100%); 6 updates in the window; the plans use this statistic.
Candidate cause: STATS — Statistic perf.QueryDailyStats._WA_Sys_00000006_5D80D6A1 was updated 2026-10-05 07:50 (sampled 100%); 7 updates in the window; the plans use this statistic.
Candidate cause: GROWTH — From the old plan's period to the change, perf.QueryDailyStats rows changed +71% and size +87% (2026-10-04 → 2026-10-05).
Counter-evidence: none
Confidence rationale: Medium: more than one candidate (STATS, GROWTH) — competing explanations.
Note: this is a candidate cause, not proof of the root cause.