SET SHOWPLAN_XML (Transact-SQL)

対象者:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics(専用SQLプールのみ)Microsoft FabricのSQL分析エンドポイントMicrosoft FabricのWarehouseMicrosoft FabricにおけるSQLデータベース

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

Transact-SQL 構文表記規則

構文

SET SHOWPLAN_XML { ON | OFF }

解説

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

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

SET SHOWPLAN_XML SQLcmdユーティリティのようなアプリケーションでは、XML出力が他のツールでクエリプラン情報の表示や処理に利用されるため、nvarchar(max)として出力を返すことを意図しています。

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

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

SSMSやSET SHOWPLAN_XMLを通じた推定実行計画は、専用SQLプール(旧SQL DW)およびAzure Synapse Analyticsの専用SQLプールで利用可能です。 専用 SQL プール (旧称 SQL DW) と Azure Synapse Analytics の専用 SQL プールの実際の実行プランを取得するには、さまざまなコマンドがあります。 詳しくは、「DMV を使用して Azure Synapse Analytics の専用 SQL プールのワークロードを監視する」をご覧ください。

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 です。 SQL Server 2022 の場合、@@VERSION の最初の値は 16 です。

プラン表示スキーマは、「Microsoft SQL Server XML スキーマ」でも見つかります。

アクセス許可

SET SET SHOWPLAN_XMLを使用するには、SETSET SHOWPLAN_XMLを実行する文を実行するための十分な権限を持ち、参照対象オブジェクトを含むすべてのデータベースに対してSHOWPLAN権限が必要です。

SELECT、INSERT、UPDATE、DELETE、EXEC *stored_procedure*、EXEC *user_defined_function* ステートメントの場合、プラン表示を生成するには、ユーザーに次のものが必要です。

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

• SHOWPLAN 権限。これは、Transact-SQL ステートメントで参照されるオブジェクト (テーブルやビューなど) を含むすべてのデータベースに対して必要です。

DDL、USE *database_name*、SET、DECLARE、動的 SQL など、他すべてのステートメントでは、Transact-SQL ステートメントを実行するための適切なアクセス許可だけが必要です。

例

以下の2つの文は、SET SHOWPLAN_XML設定を用いて、クエリにおけるインデックスのSQL Serverどのように分析・最適化しているかを示しています。

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

USE AdventureWorks2022;
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)
• 推定実行プランの表示