SQL ServerのSET SHOWPLAN_XML ONとは？クエリを実行せずに実行プランをXML取得する方法と注意点

SQL Serverでクエリの性能調査を行う際、実際に処理を実行する前に実行プランを確認したい場面があります。そのような場合に利用できる機能のひとつがSET SHOWPLAN_XML ONです。

SHOWPLAN_XMLを利用すると、クエリの実行結果を取得する代わりに、SQL Serverが作成した推定実行プランをXML形式で確認できます。この記事では、SET SHOWPLAN_XML ONの仕組みや使い方、実際の調査で注意すべきポイントについて解説します。

SET SHOWPLAN_XML ONで取得できるもの

SET SHOWPLAN_XML ONは、SQL Serverに対して「クエリを実際には実行せず、実行予定のプランだけをXML形式で返す」という指示を行う設定です。

通常、SQL文を実行するとSQL Serverはクエリオプティマイザーによって最適な実行方法を決定し、その後データ取得などの処理を行います。しかしSHOWPLAN_XMLを有効にすると、実際のデータ処理は行われず、作成された実行計画だけが返されます。

例えば、大量データを扱うUPDATEやDELETE文について、本当に処理を実行する前にどのようなインデックスが利用されるのか、テーブルスキャンになるのかなどを確認できます。

SET SHOWPLAN_XML ONを有効にした状態では、対象のクエリは実行されません。そのため、INSERT、UPDATE、DELETEなどのデータ変更処理を書いたSQLでも、実際のデータ変更は発生しません。

例えば、以下のようなSQLを実行した場合でも、テーブルの内容は変更されません。

SET SHOWPLAN_XML ON; UPDATE Customer SET Name = 'Test' WHERE CustomerID = 1; SET SHOWPLAN_XML OFF;

この場合、SQL ServerはUPDATE処理を行う代わりに、どのような実行計画を使用する予定だったかをXML形式で返します。

SHOWPLAN_XMLで取得できるのは「推定実行プラン」です。これは、SQL Serverが統計情報などを基に予測した実行計画です。

一方、実際の実行プランは、クエリを本当に実行した後に取得される情報です。実際の行数や処理時間など、実行時に発生した情報が含まれる点が異なります。

種類	特徴
推定実行プラン	クエリを実行せずに取得可能。予測値を使用する。
実際の実行プラン	クエリ実行後に取得。実際の処理状況を確認できる。

例えば、統計情報が古い場合、推定実行プランでは「少ないデータ量」と判断されていても、実際には大量データを処理しているというケースがあります。

SHOWPLAN_XMLを利用する場合は、SQL Server Management Studio（SSMS）などで以下のように設定します。

SET SHOWPLAN_XML ON; SELECT * FROM Orders WHERE CustomerID = 100; SET SHOWPLAN_XML OFF;

SHOWPLAN_XMLをONにした後に実行したSQLは、結果セットではなくXML形式の実行計画を返します。確認後は必ずOFFに戻すことが推奨されます。

SSMSでは返されたXMLをクリックすると、グラフィカルな実行プランとして表示できます。これにより、インデックス利用状況やコストの高い処理を確認できます。

SHOWPLAN_XMLを有効にしている間は、すべてのクエリが実行されなくなります。そのため、設定を解除し忘れると、通常のSELECT文でも結果が返らない状態になります。

例えば、調査終了後にSET SHOWPLAN_XML OFFを実行し忘れると、「SQLを実行したのにデータが表示されない」という状態になることがあります。

また、実行プランを作成するためにはSQL Serverが対象オブジェクトのメタデータや権限情報を確認する必要があります。そのため、対象テーブルやビューへの参照権限が不足している場合はエラーになることがあります。

SQL Serverには、実行プランを取得するための複数のSHOWPLAN系設定があります。