Sorgu 0x0A97A5660B30C50E

(@minDiffMs int,@pct int)WITH o AS (
            SELECT *, SumDur / Execs AS AvgDur,
                   ROW_NUMBER() OVER (PARTITION BY ServerID, DatabaseName, query_id ORDER BY FirstUtc, plan_id) AS rn
            FROM #ps)
        SELECT a.ServerID, a.DatabaseName, a.query_id, a.plan_id AS OldPlanId, b.plan_id AS NewPlanId,
               b.FirstUtc AS ChangeUtc, a.AvgDur AS OldAvgDur, b.AvgDur AS NewAvgDur, b.Execs AS NewExecs,
               CASE WHEN b.AvgDur > a.AvgDur THEN 'Regressed' ELSE 'Improved' END AS Direction,
               CASE WHEN b.AvgDur > a.AvgDur THEN b.AvgDur / NULLIF(a.AvgDur, 0) ELSE a.AvgDur / NULLIF(b.AvgDur, 0) END AS Ratio,
               b.Execs * ABS(b.AvgDur - a.AvgDur) / 1000.0 AS LoadMs,
               CAST(NULL AS datetime) AS ChangeEndUtc, 0 AS Switches, CAST(NULL AS bigint) AS CaseID, CAST(0 AS bit) AS IsNew
        INTO #pair
        FROM o AS a
        JOIN o AS b ON b.ServerID = a.ServerID AND b.DatabaseName = a.DatabaseName AND b.query_id = a.query_id AND b.rn = a.rn + 1
        WHERE ABS(b.AvgDur - a.AvgDur) / 1000.0 >= @minDiffMs
          AND (CASE WHEN b.AvgDur > a.AvgDur THEN b.AvgDur / NULLIF(a.AvgDur, 0) ELSE a.AvgDur / NULLIF(b.AvgDur, 0) END) >= 1 + @pct / 100.0