Sorgu 0x4137E8E1949A7CB9

(@keys nvarchar(4000),@from datetime,@to datetime,@minExec bigint)SELECT k.ServerId, k.DatabaseName, k.Hash, COUNT(*) AS Plans, MAX(x.AvgDuration) / NULLIF(MIN(x.AvgDuration), 0) AS Ratio
FROM OPENJSON(@keys) WITH (ServerId int, DatabaseName sysname, Hash varchar(18)) AS k
CROSS APPLY (
    SELECT p.plan_id, SUM(r.avg_duration * r.count_executions) / NULLIF(SUM(r.count_executions), 0) AS AvgDuration
    FROM perf.QdsQuery AS q
    JOIN perf.QdsPlan AS p ON p.ServerID = q.ServerID AND p.DatabaseName = q.DatabaseName AND p.query_id = q.query_id
    JOIN perf.QdsRuntimeStats AS r ON r.ServerID = p.ServerID AND r.DatabaseName = p.DatabaseName AND r.plan_id = p.plan_id
    WHERE q.ServerID = k.ServerId AND q.DatabaseName = k.DatabaseName AND q.query_hash = CONVERT(binary(8), k.Hash, 1)
      AND r.IntervalStartUtc >= @from AND r.IntervalStartUtc < DATEADD(day, 1, @to)
      AND r.execution_type = 0   -- K1-1: iptal (3) ve hata (4) ortalamaya karışmaz
      AND NOT EXISTS (SELECT 1 FROM perf.QdsQueryText AS it CROSS APPLY perf.fn_InternalQueryText(it.query_sql_text) AS iq WHERE it.TextHash = q.TextHash)   -- K3-2b
    GROUP BY p.plan_id
    HAVING SUM(r.count_executions) >= @minExec) AS x
GROUP BY k.ServerId, k.DatabaseName, k.Hash
HAVING COUNT(*) >= 2
OPTION (RECOMPILE)