Sorgu 0xBE690DA1C460829D

WITH ic AS (
            SELECT w.CaseID, t.SchemaName, t.TableName, i.IndexName, i.FirstSeenUtc,
                   COALESCE((SELECT MAX(l.StartUtc) FROM DBA.CollectionRunLog AS l
                             WHERE l.SourceType = 'METADATA' AND l.Status = 'OK' AND l.ServerID = w.ServerID AND l.DatabaseName = w.DatabaseName
                               AND l.EndUtc < i.FirstSeenUtc), DATEADD(day, -1, i.FirstSeenUtc)) AS PrevCollect,
                   COALESCE((SELECT MIN(h.LastUpdatedUtc) FROM perf.MetaStatisticsHistory AS h
                             WHERE h.ServerID = w.ServerID AND h.DatabaseName = w.DatabaseName AND h.object_id = i.object_id AND h.stats_id = i.index_id),
                            (SELECT st.LastUpdatedUtc FROM perf.MetaStatistics AS st
                             WHERE st.ServerID = w.ServerID AND st.DatabaseName = w.DatabaseName AND st.object_id = i.object_id AND st.stats_id = i.index_id)) AS StatsUtc,
                   w.WinStart, w.ChangeUtc, w.ChangeEndUtc
            FROM #win AS w
            JOIN #ptab AS t ON t.CaseID = w.CaseID
            JOIN perf.MetaIndex AS i ON i.ServerID = w.ServerID AND i.DatabaseName = w.DatabaseName AND i.object_id = t.object_id
            JOIN #firstc AS f ON f.ServerID = w.ServerID AND f.DatabaseName = w.DatabaseName
            WHERE i.IndexName IS NOT NULL AND i.FirstSeenUtc > f.FirstCollectUtc),
        ict AS (
            SELECT *, CASE WHEN StatsUtc > PrevCollect AND StatsUtc <= FirstSeenUtc THEN StatsUtc ELSE PrevCollect END AS E,
                      CASE WHEN StatsUtc > PrevCollect AND StatsUtc <= FirstSeenUtc THEN StatsUtc ELSE FirstSeenUtc END AS L
            FROM ic)
        INSERT rag.PlanChangeEvent (CaseID, EventType, EarliestUtc, LatestUtc, ObjectName, UsedByOldPlan, UsedByNewPlan, OrderUncertain)
        SELECT c.CaseID, 'IndexCreated', c.E, c.L, CONCAT(c.SchemaName, N'.', c.TableName, N'.', c.IndexName),
               CASE WHEN EXISTS (SELECT 1 FROM #pobj AS o WHERE o.CaseID = c.CaseID AND o.PlanRole = 'Old' AND o.TableName = c.TableName AND o.IndexName = c.IndexName) THEN 1 ELSE 0 END,
               CASE WHEN EXISTS (SELECT 1 FROM #pobj AS o WHERE o.CaseID = c.CaseID AND o.PlanRole = 'New' AND o.TableName = c.TableName AND o.IndexName = c.IndexName) THEN 1 ELSE 0 END,
               CASE WHEN c.L > c.ChangeUtc THEN 1 ELSE 0 END
        FROM ict AS c
        WHERE c.L >= c.WinStart AND c.E <= c.ChangeEndUtc