[HOWTO] SQL Server に関するアプリケーション パフォーマンスのトラブルシューティング

文書翻訳 文書翻訳
文書番号: 224587 - 対象製品
この記事は、以前は次の ID で公開されていました: JP224587
すべて展開する | すべて折りたたむ

目次

概要

この資料では、SQL Server のパフォーマンスの問題に関するトラブルシューティング方法について説明します。パフォーマンスの問題に関するトラブルシューティングでは、アプリケーションのパフォーマンス低下の原因の割り出しと特定を行う一連の手順を実行します。考えられる原因は以下のとおりです。
  • ブロッキング
  • システム リソースの競合
  • アプリケーションの設計上の問題
  • 実行時間が長い特定のクエリのセットまたはストアド プロシージャ
この資料では、パフォーマンスの問題の原因を特定する方法について説明します。また、他のトラブルシューティングについて、特定のパフォーマンスの問題が詳述されているその他の「サポート技術情報」 (Microsoft Knowledge Base) の資料も参照しています。


SQL プロファイラ


SQL プロファイラは、SQL Server 7.0 以降のアプリケーションのパフォーマンスに関するトラブルシューティングに使用できる強力なツールです。SQL プロファイラを使用すると、サーバーで発生するすべてのイベントを標準的な負荷の下で容易にキャプチャし、そのイベントに関する情報を確認できます。SQL プロファイラを Microsoft Windows NT パフォーマンス モニタおよびいくつかの単純なクエリと共に使用してブロッキングの有無を識別すると、パフォーマンスの問題のほとんどを解決するために必要な情報が得られます。

監視対象

1. SQL プロファイラをセットアップしてトレースをキャプチャします。この操作を行うには、以下の手順を実行します。
  1. SQL プロファイラを起動します。
  2. [ツール] メニューの [オプション] をクリックします。
  3. [イベント] で [すべてのイベント クラス]、[データ列] で [すべてのデータ列] が選択されていることを確認します。
  4. [OK] をクリックします。
  5. [ファイル] メニューの [新規作成] をポイントし、[トレース] をクリックして、新しいトレースを作成します。
  6. [全般] タブで、トレース名およびデータのキャプチャ先のファイルを指定します。
  7. [イベント] タブで、以下の種類のイベントをトレースに追加します。

    元に戻す全体を表示する
    見出し
    (SQL Server 7.0 の場合)
    追加するイベント 説明
    カーソル CursorPrepare SQL ステートメントのカーソルが、ODBC、OLEDB、または DB-Library を使用して準備されたことを示します。
    エラーと警告 Missing Column Statistics オプティマイザで有効な列統計が利用できなかったことを示します。[テキスト] 列には、統計が存在しない列の一覧が表示されます。このイベントは、Misc: Auto-UpdateStats イベントと共に使用して、Auto Create Statistics オプションがトリガされたことを示します。
    その他 Attention クライアントによってアテンション シグナルが送信されたことを示します。
    その他 Auto-UpdateStats Auto Update Statistics オプションがトリガされたことを示します。
    その他 Exec Prepared SQL ODBC、OLE DB、または DB-Library が以前に準備された Transact-SQL ステートメントを実行したことを示します。
    その他 Execution Plan 実行済み Transact-SQL ステートメントのプラン ツリーを表示します。
    その他 Prepare SQL ODBC、OLE DB、または DB-Library アプリケーションが、使用する Transact-SQL ステートメントを準備したことを示します。
    その他 Unprepare SQL ODBC、OLE DB、または DB-Library アプリケーションが、使用する Transact-SQL ステートメントの準備を解除したことを示します。
    セッション Connect 新しい接続が発生したことを示します。
    セッション Disconnect クライアントが接続解除したことを示します。
    セッション Existing Connection SQL プロファイラ トレースの開始時に接続が存在していたことを示します。
    ストアド プロシージャ SP: Completed ストアド プロシージャの実行の完了時を示します。
    ストアド プロシージャ SP: Recompile 実行中にストアド プロシージャが再コンパイルされたことを示します。
    ストアド プロシージャ SP: Starting ストアド プロシージャの実行開始時を示します。
    ストアド プロシージャ SP: StmtCompleted ストアド プロシージャ内のステートメントの実行完了時を示します。
    TSQL: SQL:BatchCompleted Transact-SQL のバッチ処理の完了を示します。[テキスト] 列に実行済みステートメントが表示されます。
    TSQL: SQL:StmtCompleted Transact-SQL ステートメントの完了を示します。[テキスト] 列に実行済みステートメントが表示されます。
    TSQL: RPC:Completed リモート プロシージャ コール (RPC) の完了を示します。
  8. アプリケーションでタイムアウト エラーが発生する、応答が停止する (ハング) か、または問題のステートメントが終了しない原因となるその他のイベントが発生する場合は、同様に以下のイベントを含めます。

    元に戻す全体を表示する
    TSQL: SQL:BatchStarting Transact-SQL のバッチ処理の開始を示します。[テキスト] 列に実行中のステートメントが表示されます。
    TSQL: SQL:StmtStarting Transact-SQL ステートメントの開始を示します。[テキスト] 列に実行中のステートメントが表示されます。
    TSQL: RPC:Starting リモート プロシージャ コール (RPC) の開始を示します。
    ストアド プロシージャ SP:StmtStarting ストアド プロシージャ内のステートメントの実行開始時を示します。


    これにより、タイムアウト発生時に実行されていたステートメントを確認できます。
  9. [データ列] タブで、以下の列が選択されていることを確認します。

    SQL Server 2000 の場合

    [StartTime]

    [EndTime]

    [LoginSid]

    [SPID]

    [EventClass]

    [TextData]

    [IntegerData]

    [BinaryData]

    [Duration]

    [CPU]

    [Reads]

    [Writes]

    [ApplicationName]

    [NTUserName]

    [DBUserName]


    SQL Server 7.0 の場合

    [開始時刻]

    [終了時刻]

    [接続 ID]

    [SPID]

    [イベント クラス]

    [テキスト]

    [整数データ]

    [バイナリ データ]

    [期間]

    [CPU]

    [読み取り数]

    [書き取り数]

    [アプリケーション名]

    [NT ユーザー名]

    [SQL ユーザー名]

SQL プロファイラの使用の詳細については、SQL Server 7.0 および SQL Server 2000 の Books Online を参照してください。


2. パフォーマンス モニタを使用して、Windows NT および SQL Server のカウンタをキャプチャします。この操作を行うには、以下の手順を実行します。
  1. Windows NT パフォーマンス モニタを起動します。
  2. [表示] メニューの [ログ] をクリックします。
  3. [オプション] メニューの [ログ] をクリックします。
  4. パフォーマンス カウンタをログに出力する際のファイル名と場所を指定します。必要に応じて、更新間隔を調整できます。
  5. [編集] メニューの [ログに追加] をクリックします。
  6. Windows NT および SQL Server のオブジェクトをすべて追加します。
  7. ログを開始するには、[オプション] メニューの [ログ] をクリックし、[ログの開始] をクリックします。

関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
150934 How to Create a Performance Monitor Log for NT Troubleshooting
150934 NT のトラブルシューティングに使用するパフォーマンス モニタのログを作成する方法

3. ブロッキングを調べます。

ブロッキングの発生の有無を調べるには、sp_who システム ストアド プロシージャを実行します。
exec sp_who
実行結果の [blk] 列の値を調べます。この列の値がゼロ以外のときは、ブロッキングが発生していることを示します。このプロシージャを、パフォーマンスが低下する時間帯を通して定期的に実行します。

: sp_who システム ストアド プロシージャを実行すると、ブロッキングの有無を調べることができます。通常は、ブロッキングに関する問題の完全なトラブルシューティングを行うだけの十分な情報は得られません。 関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
251004 INF: How to Monitor SQL Server 7.0 Blocking
251004 [INF] SQL Server 7.0 のブロッキングを監視する方法

標準的な負荷の下でのアプリケーションの実行

SQL プロファイラ、パフォーマンス モニタ、およびブロッキング出力によるデータ収集は同じ時間帯に実施するのが理想的です。この時間帯には、アプリケーションのパフォーマンスが低下に向かう時間を含めます。この情報を組み合わせることにより、パフォーマンスが低下している状況が明確になります。


結果分析

  1. ブロッキングを調べる

    sp_who の実行結果に含まれる [blk] 列がゼロ以外の場合は、システムでブロッキングが発生していることを示します。プロセスが互いに妨げ合っている場合、それらのプロセスの実行時間が長くなることがあります。 関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
    224453 INF: SQL Server 7.0 のブロッキング問題と解決策
  2. SQL プロファイラの出力を調べる

    SQL プロファイラのデータを効率的に表示することは、パフォーマンスの問題を解決するうえで非常に重要です。特に大切なことは、キャプチャしたデータをすべて表示するのではなく、選択するということです。SQL プロファイラには、キャプチャしたデータを効率的に表示する機能があります。[トレースのプロパティ] ダイアログ ボックスを開いて ([ファイル] メニューの [プロパティ] をクリックして)、キャプチャ対象のデータ列やイベントを削除したり、データ列のグループ化 (並べ替え) を行ったりできます。また、フィルタを適用することで表示するデータを制限できます。検索対象には、トレース全体を指定することも、特定の値を持つ列のみを指定することもできます ([編集] メニューの [検索] をクリックします)。SQL プロファイラ データを SQL Server テーブルに保存 ([ファイル] メニューの [名前を付けて保存] をポイントし、[トレース テーブル] をクリック) し、そのテーブルに対して SQL クエリを実行することもできます。

    フィルタ処理は、保存済みのトレース ファイルに対してのみ実行するように注意します。アクティブなトレースに対してこれらの手順を実行すると、トレースの開始以降にキャプチャされたデータが失われる危険性があります。まずはアクティブなトレースをファイルまたはテーブルに保存 ([ファイル] メニューの [名前を付けて保存] をクリック) し、再度それを開いて ([ファイル] メニューの [開く] をクリック) からフィルタ処理を続行します。保存済みのトレース ファイルで作業を行う場合、フィルタ処理によって除外されるデータは削除されるのではなく、表示されないだけです。必要に応じて、イベントとデータを追加または削除して、より詳細な検索を行うことができます。

    SQL プロファイラのトレース ファイルを調べてパフォーマンスに関する問題を特定するには、まずサーバーで異なる種類のイベントが発生している箇所を特定します。

    イベント クラス別にトレースをグループ化する

    a. [ファイル] メニューの [プロパティ] をクリックします。

    b. [データ列] タブで、[上へ] を使用して [グループ] の下に [イベント クラス] を移動し、[下へ] を使用して [グループ] の下からそれ以外のすべての列を削除します。

    c. [OK] をクリックします。

    [イベント クラス] 列でグループ化すると、SQL Server で発生するイベントの種類と頻度を表示できます。この列から以下のイベントを検索します。

    SP:RECOMPILE

    このイベントは、実行中にストアド プロシージャが再コンパイルされたことを示します。再コンパイル イベントが複数存在する場合は、SQL Server がクエリの実行よりもコンパイルにリソースを消費していることを示します。

    ストアド プロシージャの再コンパイルのトラブルシューティングの関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    243586 INF: Troubleshooting Stored Procedure Recompilation
    243586 INF: ストアド プロシージャのリコンパイルのトラブルシューティング


    Attention

    アテンション シグナルは、クエリがクライアントによってキャンセルされたことを示します。これは一般に、以下の 2 つのいずれかが原因で発生します。

    ユーザーが明示的にクエリをキャンセルしたかアプリケーションを終了した場合

    または

    クエリのタイムアウト時間を超えた場合

    アテンション シグナルが表示された場合、特定のクエリの実行速度が低下している可能性があります。

    関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
    243589 INF: SQL Server 7.0 で実行に時間がかかるクエリのトラブルシューティング
    アテンション シグナルを受け取ったクエリを特定するには、トレースのデータ列ごとのグループ化を解除し、アテンション シグナルを受け取ったシステム プロセス ID (SPID) にフィルタを実行 ([フィルタ] タブで SPID = x を設定) します。アテンション シグナル直前の SQL:StmtStarting、SQL:BatchStarting、または SP:StmtStarting イベントが、タイムアウトまたはキャンセルを受け取ったクエリです。[イベント クラス] 列で Attention イベントを検索すると、容易に見つけることができます ([編集] メニューの [検索] をクリック)。

    PREPARE SQL と EXEC PREPARED SQL

    Prepare SQL イベントは、ODBC、OLE DB、または DB-Library アプリケーションが、使用する Transact-SQL ステートメント、またはステートメントを準備したことを示します。Exec Prepared SQL イベントは、アプリケーションが既存の準備済みステートメントを使用してコマンドを実行したことを示します。

    これら 2 つのイベントの発生回数を比べます。アプリケーションが SQL ステートメントを 1 回準備し、そのステートメントを何度も使用するのが理想的です。これにより、ステートメントの実行のたびにオプティマイザで新しいプランをコンパイルする手間が省けます。したがって、Exec Prepared SQL イベントの数は Prepare SQL イベントの数よりもずっと大きい必要があります。Prepare SQL イベントの数と Exec Prepared SQL イベントの数がほとんど同じ場合は、アプリケーションが prepare/execute モデルを有効に利用できていない可能性があります。実行回数が 1 回のみのステートメントは準備しないようにします。SQL ステートメントの準備の詳細については、SQL Server 7.0 Books Online の「SQL ステートメントの準備」を参照してください。

    Exec Prepared SQL イベントの数が Prepare SQL イベントの数の 3 〜 5 倍に満たない場合は、アプリケーションが prepare/execute モデルを有効に利用できていない可能性があります。 関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
    243588 INF: アドホック クエリのパフォーマンスのトラブルシューティング

    SQL Server 2000 では、prepare/execute あたりの余分なラウンドトリップが除去されるため、3 〜 5 倍という値に厳密に従う必要はありません。それでも、準備済みプランの再利用を複数回試みる場合は、この値が適しています。

    Missing Column Statistics

    このイベントは、オプティマイザがより適切なクエリ プランを生成するために使用する統計情報が入手できなかったことを示します。これは、少なくとも 1 つのテーブルで有効なインデックスがクエリに含まれていないことを示します。有効なインデックスが含まれていないだけでなく、SQL Server には、情報に基づいてクエリ プランを決定するための、列に関する統計データもありません。この結果、最適なクエリ プランが生成されないことがあります。これらのイベントが発生した場合はクエリおよび生成された実行プランを調べ、次の「サポート技術情報」 (Microsoft Knowledge Base) の資料を参照して、このクエリのパフォーマンスの改善手順を確認します。
    243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
    243589 INF: SQL Server 7.0 で実行に時間がかかるクエリのトラブルシューティング

    Missing Column Statistics イベントを表示する場合、まず実行時間の長いクエリに関連して発生するイベントに注目します。一部のクエリは、自動統計を持つ SQL Server によって自動的に生成および解決され、ユーザー操作が不要な場合もあります。したがって、最善策としては、この資料の以下の文章で説明するように、最初に実行時間の長いクエリに焦点を絞り、関連する Missing Column Statistics イベントがあるかどうかを確認します。

    これらのイベント クラスのインスタンスが表示されない場合は、次の手順として、時間を要している箇所を特定します。

    期間別にトレースをグループ化する

    a. [ファイル] メニューの [プロパティ] をクリックします。

    b. [データ列] タブで、[上へ] を使用して [グループ] の下に [期間] を移動し、[下へ] を使用して [グループ] の下からそれ以外のすべての列を削除します。

    c. [イベント] タブで、[TSQL] と [ストアド プロシージャ] 以外のすべてのグループを削除します。

    d. [OK] をクリックします。

    期間でグループ化すると、最も遅い SQL ステートメント、バッチ、またはプロシージャを容易に確認できます。問題の発生時を調べるだけでなく、比較に使用するパフォーマンスが良いときのベースラインを割り出すことも非常に重要です。開始時刻に対してフィルタを実行して、パフォーマンスが良いときのセクションとパフォーマンスが低いときのセクションにトレースを分けることができます。パフォーマンスが良好なときに実行時間が最も長いクエリを調べます。これが問題の原因である可能性が非常に高いためです。システム パフォーマンス全体が低下している場合は、良好なクエリであってもシステム リソース上で待機しているため、実行時間が長くなることがあります。

    実行時間が長いクエリの数が少ない場合は、次の「サポート技術情報」 (Microsoft Knowledge Base) の資料を参照してください。
    243589 HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
    243589 INF: SQL Server 7.0 で実行に時間がかかるクエリのトラブルシューティング
    個別のクエリの実行時間は短くてもクエリ数が多く、パフォーマンス モニタの出力の SQL Compilations/sec カウンタ (後述) の値が大きい場合は、次の「サポート技術情報」 (Microsoft Knowledge Base) の資料を参照してください。
    243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
    243588 INF: アドホック クエリのパフォーマンスのトラブルシューティング
    残りのデータ列を調べる

    パフォーマンスに関する問題をさらに詳しく調べるには、トレース データ内のその他のデータ列を表示します。次に示すいくつかの点を検討します。

    CPU 使用量が高い場合は、CPU 別にグループ化して、CPU 時間を最も多く使用しているクエリを表示します。[テキスト] 列で "hash" または "merge" を検索して、これらの種類の結合を使用しているクエリ実行プランを探します。これらの結合は、一般的に I/O バウンドの (入出力操作を集中的に行う) ネストされたループ結合より、CPU およびメモリを大量に消費します。

    ディスク IO がボトルネックの場合は、読み出し/書き込み別にグループ化します。[アプリケーション名]、[NT ユーザー名]、および [SQL ユーザー名] フィールドを表示すると、実行時間の長いクエリの原因を割り出しやすくなります。

    例外イベントの [整数データ] 列には、クライアントに返されたすべてのエラーが表示されます。エラー メッセージのテキストを確認するには、SQL Server 7.0 Books Online で番号を検索します。

    [接続 ID] フィールドは、特定のクライアントの同一セッションを調べるのに役立ちます。SPID が同じでも、そのセッションが特定のクライアントの同一セッションであるとは限りません。ユーザーが接続解除し、新しいユーザーが接続して同じ SPID を受け取ることがあるためです。

    これらのフィールドの利点は状況に応じて異なりますが、前述のフィールドに何も値が表示されない場合は、これらのフィールドを調べる必要があります。
  3. パフォーマンス モニタの出力を調べる

    パフォーマンス モニタは、システム全体のボトルネックを表示します。SQL Server およびアプリケーションは期待どおりに動作していても、メモリまたはその他のリソースが不足して、コンピュータのパフォーマンスが低下することがあります。また、特定のカウンタには、実行中のアプリケーションおよび SQL Server の動作に関する問題が表示されることもあります。最低限、以下のカウンタを調べます。

  • オブジェクト : Process

    カウンタ : % Processor Time

    インスタンス : sqlservr

  • オブジェクト : Processor

    カウンタ : % Processor Time

    インスタンス : 各 Processor インスタンスを調べる

  • オブジェクト : PhysicalDisk

    カウンタ : Avg. Disk Queue Length

    インスタンス : 各 Physical Disk インスタンスを調べる

  • オブジェクト : SQL Server:SQL Statistics

    カウンタ : SQL Compilations/sec
パフォーマンスが低下に向かう時間帯で、何が最初に増加するか、コンピュータは CPU バウンドであるのかまたはディスク I/O バウンドであるのかなどの傾向を探ります。上記のプロファイラの出力に加えて、この情報は問題の領域を絞り込むのに役立ちます。CPU 利用率が高い場合は、ストアド プロシージャの再コンパイル数、またはアドホック クエリのコンパイル数が多すぎることや、ハッシュ結合およびマージ結合が集中的に使用されている可能性があります。上で参照した資料に従って、正しい対策方法を決定する必要があります。Disk Queue Length の値が大きい場合は、システム メモリの増設やディスク サブシステムの改良を検討する必要があります。

関連情報

この資料は米国 Microsoft Corporation から提供されている Knowledge Base の Article ID 224587 (最終更新日 2003-06-23) を基に作成したものです。

この資料に含まれているサンプル コード/プログラムは英語版を前提に書かれたものをありのままに記述しており、日本語環境での動作は確認されておりません。

プロパティ

文書番号: 224587 - 最終更新日: 2007年10月26日 - リビジョン: 4.1
この資料は以下の製品について記述したものです。
  • Microsoft SQL Server 7.0 Standard Edition
キーワード:?
kbhowto kbhowtomaster kbinfo kbproductlink KB224587
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com