開発Blog

SQL Serverの利用時に遅いクエリを特定する方法

投稿日:

SQL Serverの利用中にレスポンス遅延が発生した場合、どのクエリがボトルネックになっているか簡単に確認する方法をご紹介します。レスポンス遅延発生時の切り分けなどにご利用ください。

概要

SQL Serverでは、Oracel DatabaseのStatsPack・AWRのようなパフォーマンス・レポート機能が標準では提供されていません。今回のブログ記事では、SQLトレースもしくは統計情報を利用して遅いクエリを特定する方法をご紹介します。記事の内容は、SQL Server 2012・SQL Server 2014にて検証しております。

1. SQL Server Profilerを利用したSQLトレース

  • 手順1. SQL Server Profilerの起動
  • 手順2. SQLトレースの実行

2. スクリプトを利用したSQLトレース

  • 手順1. SQLトレーススクリプトの作成
  • 手順2. SQLトレースの実行

3. 動的管理ビューを利用した統計情報の抽出

  • 手順1. Accel Platform上で該当する操作の実行
  • 手順2. 動的管理ビュー sys.dm_exec_query_statsから統計情報の抽出

調査方法にはそれぞれメリット・デメリットが存在するため、調査の状況に合わせて適切な方法を選択してください。

調査方法 各SQLの実行時間に関する情報 取得によるシステム負荷の低さ
SQL Server Profiler ×
SQLトレーススクリプト
動的管理ビュー

1. SQL Server Profilerを利用したSQLトレース

ここでは、SQL Server上で実行されているSQLをトレースして、一定時間以上処理を実行していたクエリをリストアップする方法をご紹介します。SQLトレースを実行するには、SQL Serverに付属しているイベントキャプチャツール SQL Server Profilerを利用します。

SQL Server Profilerの実行はSQL Serverに負荷を与えるため、運用環境では利用しないでください。検証環境のSQL Server上で実行してください。

SQL Server Profilerの詳細については、Microsoft社のドキュメントを参照してください。

手順1. SQL Server Profilerの起動

1. SQL Server Profilerを起動します。

SQL Server Management Studioのツールバー「ツール」から「SQL Server Profiler」を選択し、SQL Server Profilerを起動します。

open_profiler

2. SQL Server Profilerからデータベースへ接続します。

認証情報を入力し、接続ボタンを押下することで、SQL Server Profilerからデータベースへ接続します。

connect_profiler

手順2. SQLトレースの実行

1. SQLトレースのプロパティを入力します。
  1. タブ「全般」にて任意のトレース名を入力します。

  2. タブ「全般」にて「使用するテンプレート」を空白に設定します。

    select_template

  3. キャチャするイベントを設定します。

    タブ「イベントの選択」にて「Stored Procedures」の「SP:StmtCompleted」にチェックを入れます。
    タブ「イベントの選択」にて「TSQL」の「SQL:StmtCompleted」にチェックを入れます。

    select_event

  4. キャプチャするイベントに対するフィルタリングを設定します。

    タブ「イベントの選択」にて「列フィルター」を押下し、フィルターの編集画面を表示します。

    「Duration」を選択し、「次の値以上」に抽出したい処理時間の下限を入力します。今回のブログ記事では、1000(ミリ秒)を設定しています。

    duration_filter

    「DatabaseName」を選択し、「次のパターンに一致」にAccel Platformから接続しているデータベースのデータベース名を入力します。

    databasename_filter

2. SQLトレースを実行します。

実行ボタンを押下し、SQLトレースを実行します。

run_profiler

3. Accel Platform側で該当する操作を実行します。

Accel Platform上でレスポンス遅延が発生する操作を実行し、操作の完了を待ちます。

4. SQLトレースを停止し、トレース結果を確認します。
  1. Accel Platform側での操作完了後にトレースを停止します。SQL Server Profilerの画面上の停止ボタンを押下します。

    stop_profiler

  2. SQL Server Profilerの画面からリストアップされたSQLを確認します。

    profiler_result

2. スクリプトを利用したSQLトレース

ここでは、SQL Server Profilerを経由せず、スクリプトを直接実行することでSQL Server上で実行されているSQLをトレースする方法をご紹介します。SQL Server Profilerを経由する場合に比べて、SQL Serverに対する負荷が大幅に減少します。

スクリプトによるSQLトレースの場合でも、SQL Serverに対する多少の負荷が残るため、運用環境では利用される際はご注意ください。

手順1. SQLトレーススクリプトの作成

以下のひな形を修正し、SQLトレーススクリプトを作成します。

  • 「%トレースファイルを出力するパス%」にトレースファイルを出力するWindows上のファイルパスを設定します。
    • 設定したファイルパスに既に同名のファイルが存在する場合、スクリプト実行時にエラーが発生します。
    • 設定したファイルパスに対してSQL Serverを実行しているWindowsユーザが書き込み権限を有していない場合、スクリプト実行時にエラーが発生します。
  • 「%データベース名%」にAccel Platformから接続しているデータベースのデータベース名を入力します。

こちらのひな形ではなく、独自にSQLトレーススクリプトを作成する場合は、SQL トレーススクリプトの作成、実行 (SQL Server 2005 ~ 2014)を参照してください。

手順2. SQLトレーススクリプトの実行

1. 作成したSQLトレーススクリプトを実行します。

作成したSQLトレーススクリプトを実行し、戻り値のトレースIDを取得します。

exec_sql_trace

2. Accel Platform側で該当する操作を実行します。

Accel Platform上でレスポンス遅延が発生する操作を実行し、操作の完了を待ちます。

3. SQLトレースを停止し、トレース結果を確認します。

以下のコマンドを実行し、SQLトレースを停止します。
%TRACE_ID%には、「1. 作成したSQLトレーススクリプトを実行します。」で取得した値を設定してください。

出力されたトレースファイルを開き、トレース結果を確認します。

trace_file

open_trace_file

3. 動的管理ビューを利用した統計情報の抽出

レスポンス遅延が再現する検証環境をすぐに構築できない場合、運用環境で調査を行う必要があります。SQLトレースは運用環境での利用が難しいため、ここでは動的管理ビュー上の統計情報から遅延しているクエリを抽出する方法をご紹介します。

動的管理ビュー sys.dm_exec_query_stats

動的管理ビューは、SQL Serverのパフォーマンスに関する様々な統計情報を格納したビューです。今回は、クエリのパフォーマンス統計が格納されている動的管理ビュー sys.dm_exec_query_statsから遅延しているクエリの情報を抽出します。

動的管理ビューは統計情報であるため、直前に操作した際のクエリに関する情報のみ抽出するということが実現できません。そのため、正確な調査を行うには、SQLトレースにより調査を行うことを推奨します。

検証を行う前に以下のコマンドを実行することで、動的管理ビュー sys.dm_exec_query_statsを初期化した状態で検証を行うことができますが、プロシージャ キャッシュ全体がクリアされ、クエリの実行計画がすべて消えてしまうため、運用環境では実行しないでください。

動的管理ビューの詳細については、動的管理ビューおよび関数 (Transact-SQL)を参照してください。動的管理ビュー sys.dm_exec_query_statsの詳細については、sys.dm_exec_query_stats (Transact-SQL)を参照してください。

手順1. Accel Platform上で該当する操作の実行

Accel Platform上でレスポンス遅延が発生する操作を実行し、操作の完了を待ちます。

手順2. 動的管理ビュー sys.dm_exec_query_statsから統計情報の抽出

動的管理ビュー sys.dm_exec_query_statsから統計情報を抽出し、ボトルネックになっているクエリを特定します。sys.dm_exec_query_statsにはクエリに関する様々なパフォーマンス統計が格納されており、CPU・物理IOボトルネックなど様々な観点でクエリをリストアップできますが、ここでは処理時間順にリストアップする方法をご紹介します。

1. 平均処理時間がかかっているクエリを特定します。

以下のSQLを実行し、結果を確認します。


average

2. 累積の処理時間がかかっているクエリを特定します。

以下のSQLを実行し、結果を確認します。


full

-開発Blog
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

Formaボタンアイテムのクリック時に任意の処理を追加するには

今回のブログ記事では、ボタンアイテムのクリック時に呼び出されるアイテム固有の処理(データの登録・画面遷移など)の前に、入力値の加工など独自の業務処理をスクリプトとして実行する方法を紹介します。 &nb …

no image

[IM-Workflow Version 7.2 Patch01] をリリースしました。

2010年7月30日に、[IM-Workflow Version 7.2 Patch01] をリリースしました。 通常、パッチリリースは不具合等の修正を目的としています。 そのため、パッチで …

no image

intra-martの WebServer機能について

※下記内容は、過去のintra-mart(Ver4.3以前)に関する内容です。最新のintra-martでは、異なる情報であることがありますので、ご注意ください。 ベースモジュールver2.X以前では …

no image

iCEC2011 eBuilder AppProducer

4/26に開催された「intra-mart Certified Evangelist Conference 2011(iCEC2011)」での講演資料を公開します。 eBuilder AppProdu …

no image

「戻る」ボタン問題の解法

  ※下記内容は、過去のintra-mart(Ver4.3以前)に関する内容です。最新のintra-martでは、異なる情報であることがありますので、ご注意ください。   業務シス …

まだデータがありません。

RSSRSSRSSRSS