[解決済み】SQL ServerでQuery Execution Planを取得する方法は？

2022-04-18 03:36:49

質問

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

どのように解決するのですか？

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

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

SQL Serverには、実行計画を簡単に取得するための機能がいくつかあります。「"実際の実行計画を含める」メニュー項目（「"クエリー」メニューにあります）がチェックされていることを確認し、通常通りクエリーを実行するだけです。

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

exec p_Example 42

問い合わせが完了すると、結果ペインにquot;実行計画"というタイトルの追加タブが表示されるはずです。多くのステートメントを実行した場合、このタブに多くのプランが表示されるかもしれません。

ここから SQL Server Management Studio で実行計画を確認するか、計画を右クリックして "Save Execution Plan As ..." を選択して計画を XML 形式でファイルに保存することができます。

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

この方法は、方法1と非常によく似ていますが（実際、SQL Server Management Studioは内部的にこれを行います）、念のため、またはSQL Server Management Studioが利用できない場合のために、この方法を含めました。

クエリを実行する前に 1 のうち、次のステートメントを使用します。このステートメントは、バッチ内の唯一のステートメントでなければなりません。つまり、他のステートメントを同時に実行することはできません。

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つの接続につき1回だけ実行する必要があります。これ以降、実行されるすべてのステートメントには 追加結果セット 通常通りクエリを実行するだけで、実行計画が表示されます。

このオプションをオフにするには、次のように記述します。

SET <<option>> OFF

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

強いこだわりがない限り、私は STATISTICS XML オプションを使用します。このオプションは、SQL Server Management Studio の "Include Actual Execution Plan" オプションと同等であり、最も便利なフォーマットで最も多くの情報を提供します。

SHOWPLAN_TEXT - クエリを実行せずに、基本的なテキストベースの推定実行計画を表示します。
SHOWPLAN_ALL - クエリを実行せずに、テキストベースの推定実行計画（コスト推定付き）を表示します。
SHOWPLAN_XML - クエリを実行せずに、XMLベースの推定実行計画（コスト推定値付き）を表示します。これは、SQL Server Management Studioの"Display Estimated Execution Plan..."オプションと同じものです。
STATISTICS PROFILE - クエリを実行し、テキストベースの実際の実行計画を表示します。
STATISTICS XML - クエリを実行し、XMLベースの実際の実行計画を表示します。これは、SQL Server Management Studio の "Include Actual Execution Plan" オプションと同じです。

方法3 - SQL Server プロファイラを使用する

クエリを直接実行できない場合 (または、直接実行してもクエリの実行速度が遅い場合 - クエリのパフォーマンスが悪い場合のプランが欲しいことを思い出してください) は、SQL Server Profiler トレースを使用してプランをキャプチャすることができます。このアイデアは、"Showplan" イベントをキャプチャしているトレースが実行されている間に、クエリを実行することです。

ただし、負荷によっては可能この方法は本番環境でも使用できますが、当然ながら注意が必要です。 SQL Server のプロファイリング機構は、データベースへの影響を最小限に抑えるように設計されています。 どんな パフォーマンスへの影響また、データベースが大量に使用されている場合、トレースのフィルタリングと正しいプランの特定に問題が発生する可能性があります。もちろん、DBAに確認して、彼らの貴重なデータベースでこれを行うことに満足しているかどうかを確認する必要があります!

SQL Server Profiler を開き、トレースを記録したいデータベースに接続する新しいトレースを作成します。
イベント選択] タブで [すべてのイベントを表示] をチェックし、[パフォーマンス] - [プラン XML を表示] の行をチェックして、トレースを実行します。
トレース実行中に、動作の遅いクエリを実行させるために必要なことを何でもしてください。
クエリが完了するのを待って、トレースを停止します。
トレースを保存するには、SQL Server Profiler の plan xml を右クリックして、"Extract event data..." を選択して、プランを XML フォーマットでファイルに保存してください。

取得した計画は、SQL Server Management Studio の "Include Actual Execution Plan" オプションと同じものです。

方法4 - クエリキャッシュを検査する

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

プランキャッシュを調べるには、SQL Server にクエリを実行します。 DMVs . 以下は、キャッシュされた全てのクエリプランを（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)

右クリックして、"Save execution plan as..."を選択して、計画をXML形式でファイルに保存してください。

注意事項

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

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

実際の実行プランと推定実行プランの比較

について 実際の 実行計画とは、SQL Serverが実際にクエリを実行する計画であり、それに対して推定実行計画 SQL Serverは だろう は、クエリを実行せずに行う。論理的には同じですが、実際の実行計画の方が、クエリ実行時に実際に何が起こったかについての詳細や統計情報を含んでおり、はるかに有用です。これは、SQLサーバーの推定が外れている問題（統計が古くなっている場合など）を診断する際に不可欠なものです。

推定実行計画と実績実行計画の再確認

クエリの実行計画をどのように解釈すればよいですか？

これは、（無料で）公開するのに十分なテーマです。書籍を、それ自体で表現しています。

こちらもご覧ください。

実行計画の基本
SHOWPLAN権限とTransact-SQLバッチ
SQL Server 2008 - クエリハッシュとクエリプランハッシュを使用する
SQL Server プランキャッシュの分析