SET SHOWPLAN_XML (Transact-SQL)

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース はいAzure SQL Managed Instance はいAzure Synapse Analytics

SQL Server で Transact-SQL ステートメントが実行されなくなります。 代わりに、SQL Server はステートメントの実行方法に関する詳細情報を、整形式の XML ドキュメントで返します。

トピック リンク アイコン Transact-SQL 構文表記規則

構文

SET SHOWPLAN_XML { ON | OFF }

注意

SQL Server 2014 以前の Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

解説

SET SHOWPLAN_XML は、解析時ではなく実行時に設定されます。

SET SHOWPLAN_XML が ON の場合、SQL Server では各ステートメントの実行プラン情報だけが返され、Transact-SQL ステートメントは実行されません。 返される情報は、このオプションが ON に設定されてから OFF に設定されるまでに発行されたすべての Transact-SQL ステートメントに関する実行プラン情報です。 たとえば、SET SHOWPLAN_XML が ON のとき、CREATE TABLE ステートメントを実行した後で、同じテーブルを参照する SELECT ステートメントを実行すると、SQL Server では指定したテーブルが存在しないというエラー メッセージが返されます。 その後、このテーブルに対して行われる参照は失敗します。 SET SHOWPLAN_XML が OFF の場合、SQL Server ではレポートを作成せずに、ステートメントを実行します。

SET SHOWPLAN_XML では、sqlcmd ユーティリティなどのアプリケーション用に、出力が nvarchar(max) 型で返されます。この XML 出力は、他のツールがクエリ プランの情報の表示や処理を行う場合に使用されます。

注意

動的管理ビュー sys.dm_exec_query_plan では、SET SHOWPLAN_XML と同じ情報が xml データ型で返されます。 この情報は、sys.dm_exec_query_planquery_plan 列から返されます。 詳しくは、「sys.dm_exec_query_plan (Transact-SQL)」をご覧ください。

SET SHOWPLAN_XML はストアド プロシージャ内では指定できません。 このステートメントは、バッチ内にのみ指定できます。

SET SHOWPLAN_XML では、情報が XML ドキュメントのセットとして返されます。 SET SHOWPLAN_XML を ON にした後で実行された各バッチの情報は、それぞれ 1 つの出力ドキュメントに反映されます。 各ドキュメントには、バッチ内のステートメントのテキストと実行ステップの詳細が含まれ、 推定コスト、行数、アクセスしたインデックス、実行された演算子の種類、結合順序、および実行プランに関するその他の情報が示されます。

注意

SQL Server Management Studio で [実際の実行プランを含める] を選ぶと、この SET オプションによって XML プラン表示出力が生成されません。 SET オプションを使う前に、[実際の実行プランを含める] ボタンの選択を解除してください。

SHOWPLAN 出力の場所

SET SHOWPLAN_XML による XML 出力用の XML スキーマを含んだドキュメントは、セットアップ時に、Microsoft SQL Server がインストールされているコンピューター上のローカル ディレクトリへコピーされます。 このドキュメントは、SQL Server インストール ファイルを含むドライブ上の次のようなパスにあります。

  • \Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

前述のパスで、ノード 130\ は SQL Server 2016 によって使用されています。 数値 130 は、SELECT @@VERSION から返される値の最初のノード (13) から派生しています。 SQL Server 2017 の場合、パスには 140\ が使用されます。これは、その @@VERSION 値の最初のノードが 14 であるためです。 SQL Server 2019 の場合、@@VERSION の最初の値は 15 です。

プラン表示スキーマは、こちらの Web サイトにもあります。

アクセス許可

SET SHOWPLAN_XML を使用するには、SET SHOWPLAN_XML の実行ステートメントを実行するための適切な権限が与えられている必要があります。また、参照されるオブジェクトを含むすべてのデータベースに対して、SHOWPLAN 権限が必要です。

SELECT、INSERT、UPDATE、DELETE、EXEC "ストアド プロシージャ"、EXEC "ユーザー定義関数" の各ステートメントの場合、プラン表示を作成するには、ユーザーに次の権限が必要です。

  • Transact-SQL ステートメントを実行するための適切な権限。

  • Transact-SQL ステートメントで参照されるテーブルやビューなどのオブジェクトを含んでいるすべてのデータベースでの SHOWPLAN 権限。

DDL、USE "データベース名"、SET、DECLARE、動的 SQL など、その他すべてのステートメントでは、Transact-SQL ステートメントを実行するための適切な権限だけが必要です。

次の 2 つのステートメントは、SET SHOWPLAN_XML の設定を使用して、SQL Server でクエリ内のインデックスの使用状況を分析し最適化する方法を示しています。

最初のクエリでは、インデックス列の WHERE 句で = (等しい) 比較演算子を使用しています。 2 番目のクエリでは、WHERE 句で LIKE 演算子を使用します。 このように指定すると、SQL Server ではクラスター化インデックス スキャンが行われ、WHERE 句の条件を満たすデータが検索されます。 EstimateRows 属性と EstimatedTotalSubtreeCost 属性の値は、インデックスが設定された最初のクエリの方が小さくなるので、インデックスが設定されていないクエリよりも速く処理が行われ、使用リソースが少なかったことがわかります。

USE AdventureWorks2012;
GO
SET SHOWPLAN_XML ON;
GO
-- First query.
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;

参照

SET ステートメント (Transact-SQL)