☰

SQL Server でクエリ実行プランを取得するにはどうすればいいですか? 質問する

tag-icon • tag-icon sql sql-server performance t-sql sql-execution-plan
SQL Server でクエリ実行プランを取得するにはどうすればいいですか? 質問する

Microsoft SQL Server で、クエリ/ストアド プロシージャのクエリ実行プランを取得するにはどうすればよいですか?

ベストアンサー1

実行プランを取得する方法はいくつかありますが、どの方法を使用するかは状況によって異なります。通常は SQL Server Management Studio を使用してプランを取得できますが、何らかの理由で SQL Server Management Studio でクエリを実行できない場合は、SQL Server Profiler 経由またはプラン キャッシュの検査によってプランを取得できると便利な場合があります。

方法 1 - SQL Server Management Studio を使用する

SQL Server には、実行プランを簡単に取得できる便利な機能がいくつか付属しています。[クエリ] メニューの [実際の実行プランを含める] メニュー項目がオンになっていることを確認し、通常どおりクエリを実行します。

アクション実行計画メニュー項目を含める

ストアド プロシージャ内のステートメントの実行プランを取得しようとしている場合は、次のようにストアド プロシージャを実行する必要があります。

exec p_Example 42

クエリが完了すると、結果ペインに「実行プラン」という追加のタブが表示されます。多数のステートメントを実行した場合、このタブに多数のプランが表示されることがあります。

実行計画のスクリーンショット

ここから、SQL Server Management Studio で実行プランを検査したり、プランを右クリックして「実行プランを名前を付けて保存...」を選択して、プランを XML 形式のファイルに保存したりできます。

方法 2 - SHOWPLAN オプションを使用する

この方法は方法 1 と非常に似ています (実際、これは SQL Server Management Studio が内部的に行うことです)。ただし、完全性のため、または SQL Server Management Studio が使用できない場合に備えて、この方法を含めました。

クエリを実行する前に、次のいずれかのステートメントを実行します。このステートメントはバッチ内の唯一のステートメントである必要があります。つまり、同時に別のステートメントを実行することはできません。

SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON -- The is the recommended option to use

これらは接続オプションなので、接続ごとに 1 回だけ実行する必要があります。この時点から、実行されるすべてのステートメントには、必要な形式で実行プランを含む追加の結果セットが付随します。プランを表示するには、通常どおりクエリを実行するだけです。

完了したら、次のステートメントを使用してこのオプションをオフにすることができます。

SET <<option>> OFF

実行計画フォーマットの比較

強いこだわりがない限り、このSTATISTICS XMLオプションを使用することをお勧めします。このオプションは、SQL Server Management Studio の「実際の実行プランを含める」オプションと同等であり、最も便利な形式で最も多くの情報を提供します。

  • SHOWPLAN_TEXT- クエリを実行せずに、基本的なテキストベースの推定実行プランを表示します。
  • SHOWPLAN_ALL- クエリを実行せずに、コスト見積もりを含むテキストベースの推定実行プランを表示します。
  • SHOWPLAN_XML- クエリを実行せずに、コスト見積もりを含む XML ベースの推定実行プランを表示します。これは、SQL Server Management Studio の [推定実行プランを表示...] オプションと同じです。
  • STATISTICS PROFILE- クエリを実行し、テキストベースの実際の実行プランを表示します。
  • STATISTICS XML- クエリを実行し、XML ベースの実際の実行プランを表示します。これは、SQL Server Management Studio の [実際の実行プランを含める] オプションに相当します。

方法 3 - SQL Server Profiler を使用する

クエリを直接実行できない場合 (または、クエリを直接実行しても実行速度が遅くならない - クエリのパフォーマンスが悪いプランが必要であることに注意してください)、SQL Server Profiler トレースを使用してプランをキャプチャできます。その目的は、"Showplan" イベントの 1 つをキャプチャするトレースの実行中にクエリを実行することです。

負荷によっては、この方法を実稼働環境で使用できますが、当然注意が必要です。SQL Server のプロファイリング メカニズムは、データベースへの影響を最小限に抑えるように設計されていますが、パフォーマンスに影響がないというわけではありません。データベースの使用率が高い場合は、トレースで正しいプランをフィルタリングして特定する際に問題が発生することもあります。もちろん、DBA に確認して、貴重なデータベースでこの操作を行っても問題ないかどうかを確認してください。

  1. SQL Server Profiler を開き、トレースを記録する目的のデータベースに接続して新しいトレースを作成します。
  2. 「イベントの選択」タブで「すべてのイベントを表示」をチェックし、「パフォーマンス」->「Showplan XML」行をチェックしてトレースを実行します。
  3. トレース実行中に、実行速度が遅いクエリを実行するために必要な操作をすべて実行します。
  4. クエリが完了するまで待機し、トレースを停止します。
  5. トレースを保存するには、SQL Server Profiler でプラン xml を右クリックし、「イベント データの抽出...」を選択して、プランを XML 形式でファイルに保存します。

取得するプランは、SQL Server Management Studio の「実際の実行プランを含める」オプションと同等です。

方法4 - クエリキャッシュの検査

クエリを直接実行できず、プロファイラー トレースをキャプチャできない場合でも、SQL クエリ プラン キャッシュを調べることで推定プランを取得できます。

SQL Serverにクエリを実行してプランキャッシュを検査します。DMV以下は、キャッシュされたすべてのクエリ プラン (xml 形式) とその SQL テキストを一覧表示する基本的なクエリです。ほとんどのデータベースでは、結果を必要なプランだけに絞り込むために、追加のフィルター句も追加する必要があります。

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

このクエリを実行し、プラン XML をクリックして新しいウィンドウでプランを開きます。右クリックして「実行プランを名前を付けて保存...」を選択し、プランを XML 形式でファイルに保存します。

ノート：

関係する要素が非常に多いため (テーブルとインデックスのスキーマから、保存されているデータやテーブル統計まで)、常に対象のデータベース (通常はパフォーマンスの問題が発生しているデータベース) から実行プランを取得するようにしてください。

暗号化されたストアド プロシージャの実行プランをキャプチャすることはできません。

「実際の」実行計画と「推定」実行計画

実際の実行プランは、SQL Server が実際にクエリを実行するプランです。一方、推定実行プランは、SQL Server がクエリを実行せずに実行する処理を計算します。論理的には同等ですが、実際の実行プランの方がはるかに便利です。これは、クエリの実行時に実際に発生した内容に関する追加の詳細と統計が含まれているためです。これは、SQL Server の推定が間違っている (統計が古くなっているなど) 問題を診断する際に不可欠です。

クエリ実行プランをどのように解釈すればよいですか?

これは（無料）に値するトピックです本それ自体の権利として。

参照:

おすすめ記事