Query Store ile plan regresyonu bul (aynı sorgu, farklı planlar)

Komut

USE [Musteri];
-- Son 24 saatte birden fazla plani olan sorgular, plan bazinda performans:
WITH plan_stats AS (
    SELECT p.query_id, p.plan_id, p.is_forced_plan,
           SUM(rs.count_executions) AS execs,
           SUM(rs.avg_duration * rs.count_executions) / SUM(rs.count_executions) / 1000.0     AS avg_ms,
           SUM(rs.avg_cpu_time * rs.count_executions) / SUM(rs.count_executions) / 1000.0     AS avg_cpu_ms,
           SUM(rs.avg_logical_io_reads * rs.count_executions) / SUM(rs.count_executions)      AS avg_reads,
           MAX(rs.last_execution_time) AS last_exec
    FROM   sys.query_store_plan p
      JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
      JOIN sys.query_store_runtime_stats_interval i
        ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
    WHERE  i.start_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
    GROUP BY p.query_id, p.plan_id, p.is_forced_plan
)
SELECT ps.query_id, ps.plan_id, ps.is_forced_plan, ps.execs,
       CAST(ps.avg_ms AS DECIMAL(18,2))     AS avg_ms,
       CAST(ps.avg_cpu_ms AS DECIMAL(18,2)) AS avg_cpu_ms,
       CAST(ps.avg_reads AS BIGINT)         AS avg_reads,
       CAST(ps.avg_ms / NULLIF(MIN(ps.avg_ms) OVER (PARTITION BY ps.query_id), 0) AS DECIMAL(18,1)) AS x_slower_than_best,
       ps.last_exec,
       OBJECT_NAME(q.object_id)     AS module,
       LEFT(qt.query_sql_text, 120) AS query_text
FROM   plan_stats ps
  JOIN sys.query_store_query q       ON q.query_id = ps.query_id
  JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
WHERE  ps.query_id IN (SELECT query_id FROM plan_stats GROUP BY query_id HAVING COUNT(*) > 1)
ORDER BY ps.query_id, ps.avg_ms;

Ne yapar?

Son 24 saatte birden fazla planla çalışan sorguları bulur; her planın çalışma sayısını, ağırlıklı ortalama süre/CPU/okuma değerlerini ve en iyi plana göre kaç kat yavaş olduğunu yan yana koyar.

Ne zaman kullanılır?

Bir sorgu "dün hızlıydı bugün yavaş" olduğunda, istatistik güncellemesi, index değişikliği, compat level yükseltmesi veya patch sonrası.

Dikkat

Ortalama süre veri dağılımına da bağlıdır; farklı parametre değerleriyle çalışan planları karşılaştırırken avg_reads'e de bak (parameter sniffing). Zaman aralığı UTC'dir. Az çalışmış (execs düşük) planlar için sonuç istatistiksel olarak zayıftır. Sadece sorgu; risksiz.