CPU使用率が高かったSQLクエリを抽出する方法(SQL Server)

はじめに

WebサーバからDBへの接続の際にエラーが頻発しておりその調査をしていました。
その結果、特定の操作で実行されるSQLクエリでのCPU使用率が異常に高くなっていることがわかりました。

原因

該当のテーブルに適したインデックスが設定されていなかったことが原因でした。

対処

• 該当するテーブルに適したインデックスを追加する

上記の対処を実施することでWebサーバのエラーは無くなりました。
今回の調査で調べた内容を備忘録としてまとめておきます。

CPU使用率が高かったSQLクエリの抽出

以下のクエリを実行することで、過去にCPU使用率が高かったSQLクエリを検索することができます。

SQL

SELECT 
    st.text AS QueryText,
    qs.total_worker_time AS TotalCPUTime,
    qs.execution_count AS ExecutionCount,
    qs.total_worker_time / qs.execution_count AS AvgCPUTimePerExecution,
    qs.total_elapsed_time / qs.execution_count AS AvgElapsedTimePerExecution,
    qs.last_execution_time AS LastExecutionTime
FROM 
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY 
    TotalCPUTime DESC;  -- CPU使用量の高い順に並べる

カラム説明

• st.text :
  実行されたクエリのテキスト
  どのクエリがリソースを消費しているかを特定できます。
• qs.total_worker_time :
  クエリ全体で消費されたCPU時間
  CPUを多く消費するクエリを特定できます。
• qs.execution_count :
  クエリの実行回数
  実行回数が多いクエリがシステムに負荷をかけているかどうかを判断するのに役立ちます。
• qs.last_execution_time :
  クエリが最後に実行された日時

ビュー説明

• sys.dm_exec_query_stats :
  SQL Serverの動的管理ビュー（DMV）の一つで、SQL Serverインスタンスで実行されたクエリ実行の統計情報を保持しています。
  実行頻度が高く、CPUやI/Oのリソースを多く消費するクエリを特定するときに使用されます。

インデックスの使用状況の確認

テーブルの検索のときに効率的なクエリの実行がされているかを確認します。
今回は以下のクエリを実行することで、データベース内の各テーブルのインデックスがどの程度使用されているかを取得しました。

SQL

SELECT 
    OBJECT_NAME(ius.object_id) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates
FROM 
    sys.dm_db_index_usage_stats AS ius
JOIN 
    sys.indexes AS i ON i.object_id = ius.object_id AND i.index_id = ius.index_id

カラム説明

• OBJECT_NAME(ius.object_id) :
  テーブル名を表示
• i.name :
  インデックスの名前を表示
• ius.user_seeks :
  インデックスシークの回数
  シークは、インデックスを使って特定のレコードを効率的に検索する操作です。
  この数値が高いほどインデックスが効率的に使われていることを示します。
• ius.user_scans :
  インデックススキャンの回数
  スキャンは、インデックス全体を読み取る操作で、シークよりもリソースを多く消費します。
  この数値が高い場合、インデックスが適切に設計されていない可能性があります。
• ius.user_lookups :
  インデックスルックアップの回数
  ルックアップは、インデックスから見つけたキーを使って実際のデータを取得する操作です。
  この数値が多いと、パフォーマンスに悪影響を与える可能性があります。
• ius.user_updates :
  インデックスの更新回数
  インデックスの更新が頻繁に行われる場合、インデックスの維持にリソースが割かれている可能性があります。

ビュー説明

• sys.dm_db_index_usage_stats :
  インデックスがどの程度使用されているかを追跡する動的管理ビュー（DMV）
  インデックスがどのように使用されているかに関する統計情報を提供します。
• sys.indexes :
  テーブルやビューに対するすべてのインデックスの詳細情報を保持するシステムビュー
  各インデックスの構造、特性、および状態に関する情報を提供します。

まとめ

まだまだ知らない機能や便利な機能があるということを知ることができました。
今後はもっとパフォーマンス面にも踏み込んだ設計と実装を行っていきたいです。