Query Store로 실행 계획 회귀 찾기 (같은 쿼리, 다른 계획)

명령어

USE [Musteri];
-- Queries with more than one plan in the last 24 hours, per-plan performance:
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;

하는 일

최근 24시간 동안 둘 이상의 실행 계획으로 실행된 쿼리를 찾는다. 각 계획의 실행 횟수, 가중 평균 소요 시간/CPU/읽기, 최적 계획 대비 몇 배 느린지를 나란히 보여 준다.

사용 시점

쿼리가 "어제는 빨랐는데 오늘은 느릴" 때, 통계 갱신, 인덱스 변경, 호환성 수준 업그레이드 또는 패치 후.

주의

평균 소요 시간은 데이터 분포에도 좌우되므로 다른 파라미터 값으로 실행된 계획을 비교할 때는 avg_reads도 본다(parameter sniffing). 시간 구간은 UTC다. 실행 횟수가 적은 계획은 통계적으로 신뢰도가 낮다. 읽기 전용 쿼리이며 안전함.