SET SHOWPLAN_XML (Transact-SQL)

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics (専用の 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 ステートメントに関する実行プラン情報です。 たとえば、SET SHOWPLAN_XML が ON のとき、CREATE TABLE ステートメントを実行した後で、同じテーブルを参照する SELECT ステートメントを実行すると、SQL Server では指定したテーブルが存在しないというエラー メッセージが返されます。 その後、このテーブルに対して行われる参照は失敗します。 SET SHOWPLAN_XML が OFF の場合、SQL Server ではレポートを作成せずに、ステートメントを実行します。

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

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

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

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 SHOWPLAN_XML を使用するには、SET 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)
• 推定実行プランの表示