DBAtlas · Атлас эксплуатации баз данных

Поиск регрессий планов в Query Store (один запрос, разные планы)

SQL ServerQuery 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. Для планов с малым числом выполнений результаты статистически слабы. Запрос только на чтение; безопасно.

Открыть в DBAtlas →

Связанные материалы