SQL Server で CPU 使用率が高いクエリを特定する手順｜DMV と Query Store

はじめに

SQL Server を運用していると、「特定の時間帯にシステムが遅くなる」「CPU 使用率が高止まりしている」といった負荷トラブルの原因調査を求められる場面があります。

かつては Windows のパフォーマンスモニター（Perfmon）と SQL Server の内部 ID を突き合わせる方法が一般的でしたが、現在は SQL Server が標準で備える 動的管理ビュー（DMV: Dynamic Management Views）と Query Store を使う方法が、Microsoft の推奨するアプローチになっています。いずれも OS へログインせず、SQL Server Management Studio（SSMS）からクエリを実行するだけで調査を完結できます。

結論を先に述べると、現在進行中の負荷は sys.dm_exec_requests、過去の累積負荷は sys.dm_exec_query_stats、再起動をまたぐ長期の履歴は Query Store で調べるのが基本方針です。プランキャッシュの統計はサーバー再起動やキャッシュクリアで失われるため、障害後の事後分析にはディスクへ永続化される Query Store が向いています。以降で、それぞれの具体的な SQL と使い分けを解説します。

調査手段の使い分け

CPU 負荷の調査には複数の手段があり、「今この瞬間を見たいのか」「過去の履歴を追いたいのか」「再起動をまたいで残したいのか」で適切な選択が変わります。まず全体像を整理します。

本記事では、SSMS だけで完結する sys.dm_exec_requests・sys.dm_exec_query_stats・Query Store の 3 つを中心に解説します。Perfmon は OS 側のリソースと相関を見たい場合に有効ですが、SQL 単体のクエリ特定という目的では上記の DMV や Query Store のほうが手数が少なくて済みます。

実行中の高負荷クエリをリアルタイムに特定する

CPU 使用率が現在進行形で高い場合、重い処理がその瞬間に走っている可能性があります。実行中のリクエスト情報を持つ sys.dm_exec_requests と、SQL 本文を復元する sys.dm_exec_sql_text、さらにセッション情報を持つ sys.dm_exec_sessions を組み合わせて確認します。

以下のクエリを SSMS で実行すると、実行中のクエリのうち CPU 消費時間が長い順にトップ 20 を表示します。

SELECT TOP 20
    r.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.cpu_time            AS cpu_time_ms,
    r.total_elapsed_time  AS elapsed_ms,
    t.text                AS sql_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1    -- ユーザーセッションのみを対象
    AND r.session_id <> @@SPID  -- このクエリ自身を除外
ORDER BY r.cpu_time DESC;

システムセッションの除外は is_user_process で行う

システムプロセスを除外する際、session_id > 50 という条件を使う例が広く出回っていますが、システムセッションの ID が常に 50 以下という保証は現行バージョンにはありません。Microsoft のトラブルシューティングガイドでは、sys.dm_exec_sessions を結合して is_user_process = 1 で絞り込む方法が採られています。 ID の範囲に依存しないこの条件のほうが確実です。

あわせて sys.dm_exec_sessions を結合することで、login_name（ログインユーザー）、host_name（接続元ホスト名）、program_name（接続元アプリケーション）が取得できます。どのホストのどのアプリケーションが負荷をかけているかまで一度に把握できるため、原因の切り分けがしやすくなります。blocking_session_id と wait_type を加えているのは、CPU 消費がブロッキングや待機と絡んでいないかを同時に確認するためです。

参考: Troubleshoot slow-running queries（Microsoft Learn）
“req.cpu_time AS cpu_time_ms”
（sys.dm_exec_requests の cpu_time はミリ秒単位）
https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries

実行結果の見方

結果は次のカラムに注目します。

• cpu_time_ms: このリクエストが消費した CPU 時間（ミリ秒）。ここが突出しているものが負荷の中心です。
• sql_text: 実行中の SQL 本文。どのアプリケーションのどの処理かを特定できます。
• session_id: セッションの ID。緊急停止（KILL コマンド）で対象を指定する際に使用します。
• status: running（実行中）や runnable（CPU の空き待ち）であれば、CPU リソースの競合が起きている可能性があります。

sys.dm_exec_requests の cpu_time はミリ秒である点を覚えておくと、次に紹介する履歴用の DMV と単位を混同せずに済みます（後述のとおり、履歴側はマイクロ秒です）

過去に CPU を多く消費したクエリを累積統計から抽出する

実行中のクエリに該当がない場合、すでに完了したクエリや、短時間でも頻繁に実行されるクエリが原因のことがあります。この場合は、SQL Server がメモリ内に保持するプランキャッシュの累積統計 sys.dm_exec_query_stats を調べます。

以下のクエリは、サーバー起動（またはキャッシュクリア）以降に実行されたクエリのうち、CPU 総消費時間が大きい順にトップ 10 を抽出します。

SELECT TOP 10
    qs.total_worker_time / 1000                          AS total_cpu_ms,
    qs.execution_count,
    (qs.total_worker_time / qs.execution_count) / 1000   AS avg_cpu_ms,
    qs.last_execution_time,
    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1)     AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_worker_time DESC;

total_worker_time はマイクロ秒である点に注意

このクエリで /1000 としているのは、total_worker_time の単位がマイクロ秒だからです。 先ほどの sys.dm_exec_requests.cpu_time（ミリ秒）とは単位が異なり、この違いが混乱の原因になりやすい箇所です。マイクロ秒を 1000 で割ることでミリ秒に換算しています。

参考: sys.dm_exec_query_stats（Microsoft Learn）
“Total amount of CPU time, reported in microseconds (but only accurate to milliseconds)”
（CPU 時間の合計。マイクロ秒単位で報告される（ただし精度はミリ秒））
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql

累積値を見る理由

パフォーマンスチューニングでは、1 回だけ実行される重いクエリよりも、1 回あたりは軽くても膨大な回数実行されるクエリのほうが、システム全体への負荷が高いことがあります。

• 単発の重いクエリは、瞬間的なスパイクの要因になります（avg_cpu_ms で判別）。
• 頻出する軽いクエリは、慢性的な高負荷の要因になります（total_cpu_ms で判別）。

上記の SQL は total_worker_time（CPU 総消費時間）の降順で並べているため、リソースを最も多く消費しているクエリを効率的に把握できます。チューニングの効果が高いのは、このランキング上位のクエリです。last_execution_time を加えているのは、その負荷が最近のものか、過去に一度きりだったのかを判断するためです。

Query Store で再起動をまたいで高負荷クエリを追跡する

前述のプランキャッシュ（sys.dm_exec_query_stats）は、サーバー再起動やキャッシュクリアで統計が失われます。障害発生後に「あの時間帯に何が重かったのか」を後から調べたい場合は、実行統計をディスクに永続化する Query Store が適しています。

Query Store は SQL Server 2016 で追加された機能で、クエリ・実行プラン・ランタイム統計の履歴をデータベース単位で保存します。SQL Server 2022 以降は新規作成されるデータベースで既定で有効になり、Azure SQL Database では常時有効です。 再起動やアップグレードをまたいでも履歴が残るため、事後分析やプラン退行（実行プランの変化による性能劣化）の調査に向いています。

参考: Monitor performance by using the Query Store（Microsoft Learn）
“It’s enabled by default in the READ_WRITE mode for new databases starting with SQL Server 2022 (16.x).”
（SQL Server 2022 以降、新規データベースでは READ_WRITE モードで既定で有効になります）
https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store

SSMS の Top Resource Consuming Queries レポート

Query Store が有効なデータベースでは、SSMS のオブジェクトエクスプローラーでデータベースを展開し、「Query Store」フォルダー配下の 「Top Resource Consuming Queries（リソースを最も消費するクエリ）」 をダブルクリックすると、リソース消費の大きいクエリが既定で上位 25 件、ダッシュボード形式で表示されます。右上のメトリック選択で「CPU Time」を選ぶと、CPU 消費順に並び替えできます。GUI 上で対象クエリを選ぶと、実行プランや実行回数の推移もそのまま確認できます。

T-SQL で Query Store から CPU 上位を抽出する

GUI を使わず SQL で取得する場合は、Query Store のカタログビューを結合します。以下は、記録済みの区間全体で CPU 総消費時間が大きい順にトップ 10 を抽出する例です。

SELECT TOP 10
    qt.query_sql_text,
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions)                              AS total_executions,
    SUM(rs.count_executions * rs.avg_cpu_time) / 1000     AS total_cpu_ms,
    AVG(rs.avg_cpu_time) / 1000                           AS avg_cpu_ms,
    MAX(rs.last_execution_time)                           AS last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
GROUP BY qt.query_sql_text, q.query_id, p.plan_id
ORDER BY total_cpu_ms DESC;

Query Store の avg_cpu_time もマイクロ秒単位のため、/1000 でミリ秒に換算しています。1 回あたりの平均 CPU 時間に実行回数を掛けることで、区間全体の CPU 総消費時間を求めています。特定期間だけに絞りたい場合は、sys.query_store_runtime_stats_interval の start_time / end_time を条件に加えると、障害が起きた時間帯のみを対象にできます。

参考: sys.query_store_runtime_stats（Microsoft Learn）
“Contains information about the runtime execution statistics information for the query.”
（クエリのランタイム実行統計に関する情報が格納されます）
https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-transact-sql

Query Store が有効か確認する・有効化する

Query Store の状態は次の SQL で確認できます。actual_state_desc が READ_WRITE であれば収集中です。

SELECT actual_state_desc,
       desired_state_desc,
       readonly_reason,
       current_storage_size_mb,
       max_storage_size_mb
FROM sys.database_query_store_options;

無効な場合（SQL Server 2019 以前や、既存のデータベースなど）は、次の ALTER DATABASE で有効化できます。

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

有効化した直後は履歴が空のため、ある程度のワークロードが流れてからデータが蓄積されます。過去にさかのぼった調査には使えない点に注意が必要です。

過去の CPU 使用率の推移を SQL だけで確認する

「特定のクエリ」ではなく「サーバー全体の CPU 使用率がいつ上がったか」を知りたい場合は、sys.dm_os_ring_buffers のスケジューラーモニター情報から、直近の CPU 使用率の推移を SQL だけで取得できます。OS のパフォーマンスモニターを開かなくても、SQL Server プロセスの CPU 使用率を約 4 時間分、1 分間隔（最大 256 件）で振り返れるのが利点です。

DECLARE @ts_now BIGINT =
    (SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info);

SELECT TOP 256
    DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS event_time,
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
        AS sql_cpu_percent,
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
        AS system_idle_percent,
    100
      - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
      - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
        AS other_process_percent
FROM (
    SELECT [timestamp], CONVERT(XML, record) AS record
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
      AND record LIKE N'%<SystemHealth>%'
) AS x
ORDER BY [timestamp] DESC;

sql_cpu_percent が SQL Server プロセスの CPU 使用率、other_process_percent が SQL Server 以外のプロセスによる使用率です。両者を分けて見ることで、CPU の高騰が SQL Server 由来か、同居する別プロセス（アンチウイルスや他サービスなど）由来かを切り分けられます。

なお、sys.dm_os_ring_buffers は Microsoft が「情報提供目的で、サポート対象外・将来の互換性は保証されない」と位置づける未公開ビューです。恒常的な監視には Perfmon や Query Store を併用し、この方法は障害直後の一次切り分け用途にとどめることをおすすめします。

参考: Monitor CPU usage on SQL Server and Azure SQL（Microsoft Community Hub）
“you can use sys.dm_os_ring_buffers view to find CPU usage”
（sys.dm_os_ring_buffers ビューを使って CPU 使用率を確認できます）
https://techcommunity.microsoft.com/blog/azuresqlblog/monitor-cpu-usage-on-sql-server-and-azure-sql/680777

DMV を使う際の注意点

DMV は手軽に使える一方、値の解釈を誤ると原因を見誤ります。実務で押さえておきたい注意点を整理します。

 total_worker_time は経過時間より大きくなることがある。並列クエリでは複数のワーカースレッドの CPU 時間が合算されるため、total_worker_time（CPU 時間）が total_elapsed_time（経過時間）を上回る場合があります。CPU 時間が経過時間より大きい行は並列実行の可能性が高いです。

 プランキャッシュの統計は揮発します。sys.dm_exec_query_stats はプランがキャッシュされている間の累積値のため、再起動・DBCC FREEPROCCACHE・メモリ圧迫によるプラン破棄で消えます。前夜に重かったクエリが今は見えないこともあります。長期の傾向分析は Query Store が確実です。

 アドホッククエリは分散して埋もれます。パラメーター化されていない SQL はテキストごとに別プランとして扱われ、集計時に上位へ現れにくくなります。execution_count > 1 で絞ると、繰り返し実行されるクエリに焦点を当てられます。

 必要な権限がバージョンで異なります。sys.dm_exec_query_stats の参照には、SQL Server 2022 以降は VIEW SERVER PERFORMANCE STATE、2019 以前は VIEW SERVER STATE が必要です。

参考: sys.dm_exec_query_stats（Microsoft Learn）
“SQL Server 2022 (16.x) and later versions require VIEW SERVER PERFORMANCE STATE permission on the server.”
（SQL Server 2022 以降では、サーバーに対する VIEW SERVER PERFORMANCE STATE 権限が必要です）
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql

検証: テスト負荷で特定できるか確認する

紹介した DMV が実際に高負荷クエリを捉えられるかを、テスト環境で確認します。

CPU 使用率を意図的に上昇させる操作を含むため、本番環境ではなくテスト環境で実施することをおすすめします。

無限ループによる負荷生成クエリ

SSMS で新しいクエリウィンドウを開き、以下を実行します。WHILE (1=1) で無限ループし、変数の加算を続けることで CPU を消費します。

DECLARE @i BIGINT = 0;

-- CPU 負荷をかけるための無限ループ
WHILE (1 = 1)
BEGIN
    SET @i = @i + 1;
END

実行すると処理が終わらない状態になります。確認が済んだら、赤い「停止」ボタン（または Alt + Break）で停止します。

リアルタイム調査 SQL で捕捉できるか確認

負荷クエリを実行したまま、別のクエリウィンドウで前述の「実行中の高負荷クエリをリアルタイムに特定する」SQL を実行すると、結果の上位にこの無限ループが表示されます。

• sql_text: WHILE (1 = 1) BEGIN SET @i = @i + 1; END が表示される。
• cpu_time_ms: 実行中は数値が増え続ける。

この結果から、パフォーマンスモニターやプロファイラーを使わずに、SSMS 上の DMV で CPU 消費の大きいクエリを特定できることを確認できます。

まとめ

SQL Server の CPU 負荷調査は、OS へログインせず SSMS だけで完結できる時代になりました。調査の対象時点とデータの永続性を意識して、DMV と Query Store を使い分けることが要点です。

• 現在進行中の負荷は sys.dm_exec_requests でリアルタイムに特定
• 過去の累積負荷は sys.dm_exec_query_stats のプランキャッシュで抽出
• 再起動をまたぐ履歴は Query Store で永続的に追跡
• cpu_time はミリ秒、total_worker_time はマイクロ秒で単位が異なる
• システムセッションの除外は is_user_process = 1 で行う
• サーバー全体の CPU 推移は ring buffer から SQL だけで確認
• 調査手段は対象時点と永続性で使い分ける

以上、最後までお読みいただきありがとうございました。