Summary: How to trouble shooting the slow SQl block.
SQLServerで実行速度の遅いクエリを特定する方法
概要
実行速度の遅いクエリは、大量のデータを処理する最も一般的な問題の1つです。そして、私が取り組んでいるほとんどすべてのクライアントで最も難しい問題は、実行速度が遅いクエリを見つけて、パフォーマンスの問題の背後にある実際の原因を特定する方法です。ありがたいことに、ほとんどの場合、解決策は単純です。
私は常に、問題の背後にある実際の原因を解明することに大部の時間を費やすことをお勧めします。存在する可能性のある潜在的な解決策について考えることではありません。
幸いなことに、開発者またはDBAが（少なくとも）実行速度の遅いクエリについて公正なアイデアを得るために常に使用する必要のあるツールと手法がいくつかあります。
詳細に入る前に、ここで説明するツールと手法は、データベース管理の専門知識を持たないSQL開発者と、キャリアを開始したばかりのデータベース管理者を対象としています。
注：この記事のテストケースでは、SQL Server2016を使用します。以前のバージョンを使用している場合、クエリストアは使用できませんが、他のすべてのツールは正常に使用できますので、ご安心ください。
ツールと技術
世界のあらゆる種類の作業と同様に、必要な特別なツールがあります。 実行速度の遅いクエリを識別するプロセスも同じです。 この記事では、システムにすでに存在するツール、または無料でダウンロードできるツールについてのみ説明します。 商用ツールが役に立たないとは言えませんが、実際、大規模な組織では、実行速度の遅いクエリを強調するために、各サーバーを深く掘り下げる時間を節約することを強くお勧めします。 しかし、この記事の目的のために、私たちは頑張ってで、すでに自由に使える既存のツールを使用して基本を学びたいと思います。
ここで説明する最初のツールは、SQL Server ManagementStudioの組み込みツールである「利用状況モニター」です。 これを表示するには、SQL Server Management Studioでインスタンス名を右クリックし、[利用状況モニター]を選択します。
利用状況モニターは、SQLServerインスタンスの現在および最近の利用状況（アクティビティ）を示します。
上のスクリーンショットは、利用状況モニターの概要ウィンドウを示しています。この画面には、プロセッサー時間、待機タスク、およびバッチ要求のグラフが表示されます。通常、カウント数が少ないほど、パフォーマンスが向上します。負荷が大きい大規模な組織では、プロセッサ時間が長い大量のバッチリクエストである可能性がありますが、それは必ずしもパフォーマンスの問題を示しているわけではありません。
概要の後で、インスタンスで実行されているすべてのプロセスを表示するためのアクセスを提供し、待機中、ブロック中、またはブロックされているプロセスの数を詳しく調べることができるプロセスに焦点を当てる必要があります。特定の待機のため、または時間がかかるクエリが他のプロセスによってブロックされているためにクエリの実行が遅くなっています。このビューでは、任意のプロセスを右クリックし、[詳細]をクリックして、そのセッションで実行されている実際のTSQLを表示できます。
ブロックされているクエリは、プロセスが依存するリソースで動作している他のプロセスのために実際に中断されているクエリです。従って他のプロセスによってブロックされているクエリを見つけた場合は、すべてを引き起こしているルートブロッカーを確認してください。 [Blocked By]列を見て、ブロックします。ブロックされているすべてのプロセスではなく、そのクエリだけを検討してください。
また、特定のリソースを待機しているクエリは、待機リソースに関する情報を提供するため、待機タイプを確認して、その問題の解決策を見つけ出すことができます。最も一般的な待機統計の一部は、包括的なSQLに一覧表示されます。 ShackのSQLServer待機タイプのセクション。詳細なアクションについては、それを確認してください。
ActiveExpensiveおよびRecentExpensiveクエリは、CPU、論理読み取り、または経過時間が長いクエリに関する情報を提供します。
現在または最近の高額なクエリについては、各セクションに移動できます。 経過時間、論理読み取り、CPU時間の順に並べ替え、実行プランを確認してください。 実行プランでは、これらの高価なクエリが非常に長い時間を要した理由を突き止め、適切なアクションを実行してそれらを解決できるようになります。 この記事の後半でSQLServerクエリ実行プランを実行する方法をお知らせしますので、しばらくお待ちください。
次のツールは「クエリストア」です。これは、SQL Serverが1時間前に遅い理由を確認するために深夜に呼び出された場合に役立ち、命を救うことができます。
一般に、SQL Server 2016より前は、サードパーティのアプリケーションやカスタムソリューションがないと、クエリの実行履歴を確認できません。そのため、クエリストアはこの点で多くの付加価値機能を提供します。EdPollackここにクエリストアについて書いたので、クエリストアを深く掘り下げるための優れたリソースであるため、この記事を確認してください。
SQL Server 2016以降を使用している場合は、最初にデータベースのプロパティでSQL Serverを有効にする必要があります。クエリストアを有効にすると、次のスクリーンショットに示すようにデータベースのプロパティが表示されます。
クエリストアを有効にした後、以下のスクリーンショットに示すように、データベースオブジェクトを展開し、「トップリソース消費クエリ」に移動できます。
注：実際の負荷で簡単に作業できるように、クエリストアに本番負荷をキャプチャするための1日か2日を与えます。
トップリソース消費クエリを右クリックし、[トップリソース消費クエリの表示]を選択すると、これらのリソース消費の高いクエリを表示するウィンドウが表示されます。期間、CPU時間、論理などの適切な「メトリック」を選択して、ビューをカスタマイズできます。読み取りまたはメモリ消費。次に変更する必要があるのは「統計」です。最小、最大、または平均に変更できます。クエリを取得するには、上記のすべてのメトリックで平均統計を使用することをお勧めします。
次のステップは、高いリソースを消費しているクエリを強調表示することです。左側のウィンドウでグラフ値を強調表示した後（下のスクリーンショットで強調表示されているように）、下部のウィンドウにクエリ実行プランが表示されます。
[クエリストア]ウィンドウで以下に強調表示されているボタンをクリックすると、実際のクエリテキストを取得してさらに分析できます。
したがって、現時点では、高リソース使用量のクエリを取得する方法は複数あります。次に、クエリの実行速度が遅い理由と、クエリのどの部分を修正する必要があるか（必要な場合）を確認する方法を説明します。
そこで、ここでは、MicrosoftサンプルデータベースWideWorldImportersで使用されるクエリの例を取り上げます。TSQLは、ストアドプロシージャ[Integration].[GetOrderUpdates]を実行します。
このストアドプロシージャの呼び出しには約1秒かかりますが、最適化はしません。これは、この1秒がどのように費やされたかを知る方法の例を示すためのものです。また、クエリのどの部分が最も多くを費やしているかを知りたいと思います。時間と、どのテーブルに焦点を合わせなければならないか。
以下は、ストアドプロシージャの呼び出しと結果です。
これで電話があり、これについてさらに深く掘り下げます。
まず、このセッションのクエリ統計を有効にする必要があります。TSQLSET STATISTICS TIME, IO ONを発行して、このクエリセッションのCPUおよびIO統計を有効にします。
統計を有効にするために上記のTSQLを実行した後、以下のスクリーンショットに示すように、[メッセージ]タブのストアドプロシージャ内で実行されているクエリの各テーブルのIOと合計CPUコストを取得します。
上のスクリーンショットでは、ほとんどのIOがOrderLinesテーブルによって取得され、ストアドプロシージャ内で実行されているクエリは1つだけで、CPU時間は672ミリ秒（経過時間は1650ミリ秒）であることがわかります。
注：ストアドプロシージャ内で複数のクエリが実行されている可能性があるため、統計では各クエリの時間と、最後にすべてのクエリの合計が表示されることに注意してください。したがって、ストアドプロシージャの場合は、合計CPU時間のみです。最後のCPU時間を考慮し、クエリごとにそのCPU時間のみを考慮し、最後のCPU時間を除外します。これは、すべての合計にすぎないためです。
現在のところ、OrderLinesテーブルがほとんどの論理読み取りを行っていることがわかっています。
次に、SQL Server Management Studioのアイコン
（Ctrl + M)をクリックして、クエリの実際の実行プランを有効にし、このテーブルがこのIOを取得した理由と、実行プランのどのコンポーネントが取得したかという質問に答えようとします。ほとんどの時間。
実際の実行プランを含めた後、クエリを再実行して実行プランを表示します。
ただし、SQL Server Management Studio内でクエリ実行プランに関する詳細情報を取得できますが、ApexSQL Planという、はるかに直感的な方法でクエリ実行プランを探索するために使用できる別の優れたツールがWeb上にあります。
このツールをインストールした後、SQL Server Management Studioを再起動してインストールし、クエリを再実行して実行プランを取得する必要がある場合があります。このツールのスクリーンショットツアーをここに示します。クエリを実行した後、右クリックして実行プランと「ApexSQLプランで表示」のオプションがあります。
ApexSQLプランで実行プランを表示した後、ApexSQLプランで開かれる以下のスクリーンショットで強調表示された項目を確認できます。
ここで見るべきポイントとヒントのカップル
- テーブルがキールックアップを使用している場合は、テーブルで使用されているインデックスにこれらの列を追加して、そのルックアップを削除してみてください。
- テーブル演算子によって返される行数と比較して、クエリによって返される行数が大幅に少ない場合（クエリプランの下部で強調表示されているように）、より多くの列を使用してデータをフィルタリングするクエリを書き直してみてください。行数を減らします。
- 推定行と実際の行に大きな違いがある場合は、下にあるテーブルの統計を更新してみてください。
- クエリで示されたインデックスが欠落している場合は、インデックスを評価してみてください。そのインデックスがクエリに役立つ場合は、対応するテーブルに追加してください。
最後に、クエリが1回の実行で正常に実行され、本番環境での実行中にのみ問題が発生する場合は、こちらのリンクからダウンロードできるAdam MachanicのSQLストレスツールを使用して、単一のクエリの本番環境の負荷を簡単に偽造できます。上記の手法から実行速度の遅いクエリをキャプチャし、それに応じて調整することができます。
纏め
上記のツール、テクニック、およびヒントは、実行速度の遅いクエリをトラブルシューティングするための最も一般的なソリューションです。これらは一般的で一般的であり、ほとんどの場合、クエリはそれらの恩恵を受けます。
パフォーマンスの最適化と実行速度の遅いクエリのトラブルシューティングにはペットのルールがないことを覚えておいてください。このトピックは大きすぎて一部の記事ではカバーできないため、最も重要で一般的なツールとテクニックのいくつかを1つの記事にまとめようとしました。基本的なトラブルシューティングを簡単にするためです。
拡張
ApexSQLプランをチェックして、プランの比較、ストアドプロシージャのパフォーマンスプロファイリング、欠落しているインデックスの詳細、遅延プロファイリング、待機時間、プランの実行履歴などを含むSQL実行プランを確認してください。
※上記内容が訳文です、原文リンクをクリックすると自動遷移できますので、ご参照ください。
–end–