SQL Serverのパフォーマンスモニタリング：プロファイラー、拡張イベント、DMVの活用法

データベースは、システム全体のパフォーマンスに直接影響を与える重要なコンポーネントです。SQL Serverを使用する際、適切なパフォーマンスモニタリングを行うことで、以下の利点を得られます。

  • ボトルネックの特定と解消
  • サーバーリソースの適切な配分
  • クエリ実行の効率化
  • ユーザーエクスペリエンスの向上

本記事では、SQL Serverの主要なパフォーマンスモニタリングツールである SQL Server Profiler（プロファイラー）、拡張イベント（Extended Events）、および 動的管理ビュー（DMV） の使い方を学びます。

SQL Server Profilerの活用

SQL Server Profilerとは？

SQL Server Profilerは、SQL Serverの動作を追跡するためのツールです。

クエリの実行時間、デッドロック、インデックス使用状況など、パフォーマンスに関する詳細な情報を収集できます。

基本的な使い方

  1. SQL Server Management Studio (SSMS)を起動し、[ツール]メニューからSQL Server Profilerを選択します。
  2. 新しいトレースを作成します。
    • サーバーインスタンスを選択し、トレース名を設定します。
    • テンプレートを「Standard」に設定すると基本的なモニタリングが可能です。
  3. 必要なイベント（例：クエリの実行）を選択し、トレースを開始します。
  4. トレース結果を分析し、長時間実行されているクエリやエラーを確認します。

プロファイラーの利点

  • リアルタイムでデータを収集可能。
  • データベース全体のパフォーマンスを素早く把握できる。

注意点

プロファイラーはリソースを消費するため、運用環境での長時間の使用は避けるべきです。

拡張イベントの活用

拡張イベントとは？

拡張イベントは、SQL Serverの軽量なモニタリングツールで、プロファイラーの代替として推奨される機能です。

システムへの負荷を最小限に抑えながら詳細なデータを収集できます。

基本的な使い方

  1. SSMSの[管理]ノードで「拡張イベント」を展開し、新しいセッションを作成します。
  2. イベントを選択します（例：「sqlserver.sql_statement_completed」）。
  3. イベントデータを収集する出力ターゲット（例：ファイル、リングバッファ）を設定します。
  4. セッションを開始し、収集データを分析します。

おすすめのイベント

  • sqlserver.lock_acquired: ロック取得状況を監視。
  • sqlserver.sql_batch_completed: バッチの実行時間を監視。

拡張イベントの利点

  • 軽量でリソース消費が少ない。
  • カスタマイズ性が高い。

動的管理ビュー (DMV) の活用

DMVとは？

DMV（Dynamic Management Views）は、SQL Serverの内部状態をクエリで取得するためのビューです。

DMVを利用することで、パフォーマンス問題の診断が簡単になります。

基本的なDMVクエリ例

(1) 実行中のクエリを確認する

SELECT
    session_id,
    status,
    start_time,
    command,
    text AS query_text
FROM
    sys.dm_exec_requests
CROSS APPLY
    sys.dm_exec_sql_text(sql_handle);

(2) インデックス使用状況を確認する

SELECT
    OBJECT_NAME(IXS.OBJECT_ID) AS TableName,
    I.name AS IndexName,
    IXS.user_seeks,
    IXS.user_scans,
    IXS.user_lookups,
    IXS.user_updates
FROM
    sys.dm_db_index_usage_stats AS IXS
    INNER JOIN sys.indexes AS I
    ON I.object_id = IXS.object_id AND I.index_id = IXS.index_id
WHERE
    OBJECTPROPERTY(IXS.OBJECT_ID, 'IsUserTable') = 1;

(3) クエリの実行プランを確認する

SELECT
    query_plan
FROM
    sys.dm_exec_query_stats AS QS
CROSS APPLY
    sys.dm_exec_query_plan(QS.plan_handle);

DMVの利点

  • クエリベースで情報を取得できるため、柔軟性が高い。
  • システム全体の詳細な情報を提供する。

演習問題

以下の内容を参考に、SQL Serverのパフォーマンスモニタリングを実践してみましょう。

問題 1: 実行中のクエリの特定

DMVを使用して、現在実行中のクエリのセッションID、状態、開始時間を確認してください。

問題 2: インデックスの効果を確認

DMVを使用して、指定したテーブルのインデックス使用状況（検索、スキャン、更新回数）を確認してください。

問題 3: 長時間実行されるクエリの特定

拡張イベントを使用して、1秒以上かかるクエリを特定するセッションを作成してください。

演習問題の解答例

解答例 1: 実行中のクエリの特定

以下のSQLクエリを使用します。

SELECT
    session_id,
    status,
    start_time,
    command,
    text AS query_text
FROM
    sys.dm_exec_requests
CROSS APPLY
    sys.dm_exec_sql_text(sql_handle);

解答例 2: インデックスの効果を確認

以下のSQLクエリを使用します。

SELECT
    OBJECT_NAME(IXS.OBJECT_ID) AS TableName,
    I.name AS IndexName,
    IXS.user_seeks,
    IXS.user_scans,
    IXS.user_lookups,
    IXS.user_updates
FROM
    sys.dm_db_index_usage_stats AS IXS
    INNER JOIN sys.indexes AS I
    ON I.object_id = IXS.object_id AND I.index_id = IXS.index_id
WHERE
    OBJECTPROPERTY(IXS.OBJECT_ID, 'IsUserTable') = 1;

解答例 3: 長時間実行されるクエリの特定

拡張イベントで以下の設定を行います。

  1. イベント「sqlserver.sql_statement_completed」を選択。
  2. フィルタで「duration >= 1000000」（1秒以上）を設定。
  3. 出力ターゲットをリングバッファに設定して開始。

まとめ

SQL Serverのパフォーマンスモニタリングを適切に行うことで、システムの効率を最大化できます。

本記事で紹介したプロファイラー、拡張イベント、DMVを活用し、運用環境でのパフォーマンスチューニングを実践してください。

演習問題を解くことで、モニタリングスキルをさらに向上させることができます。