プランキャッシュ内で最もコストの高いクエリを特定する (CPU、読み取り、実行時間)

コマンド

SELECT TOP (20)
       qs.query_hash,
       SUM(qs.execution_count)                                       AS execs,
       SUM(qs.total_worker_time) / 1000                              AS total_cpu_ms,
       SUM(qs.total_worker_time) / SUM(qs.execution_count) / 1000    AS avg_cpu_ms,
       SUM(qs.total_elapsed_time) / SUM(qs.execution_count) / 1000   AS avg_ms,
       SUM(qs.total_logical_reads) / SUM(qs.execution_count)         AS avg_reads,
       SUM(qs.total_grant_kb) / SUM(qs.execution_count)              AS avg_grant_kb,
       COUNT(DISTINCT qs.query_plan_hash)                            AS plan_count,
       MAX(qs.last_execution_time)                                   AS last_exec,
       MIN(SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
           ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1)) AS statement_text,
       MIN(DB_NAME(pa.dbid))                                         AS db
FROM   sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  OUTER APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(qs.plan_handle)
               WHERE attribute = N'dbid') pa
GROUP BY qs.query_hash
ORDER BY total_cpu_ms DESC;

-- To rank by reads: ORDER BY SUM(qs.total_logical_reads) DESC
-- Plan: SELECT query_plan FROM sys.dm_exec_query_plan(<plan_handle>);

概要

プランキャッシュ内のステートメントを query_hash でグループ化し (リテラルのみが異なるアドホッククエリは 1 行にまとめられる)、CPU、実行時間、論理読み取り、メモリ許可の合計と平均、およびプラン数を返す。

使用する場面

CPU が張り付いている場合、「サーバーが遅い」という苦情、Query Store のないデータベース、またはすべてのデータベースにわたるサーバー全体の視点が必要な場合に。

注意

キャッシュに残っているプランのみが対象となる: 再起動、DBCC FREEPROCCACHE、メモリ負荷、RECOMPILE によりプランが削除されたクエリは含まれない。永続的で時間範囲を指定した分析には Query Store を使用する。plan_count > 1 はパラメータ感度または異なる SET オプションを示唆する。プランキャッシュが非常に大きい場合、クエリに数秒かかることがある。安全。