SET SHOWPLAN_ALL (Transact-SQL)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Microsoft SQL Server で Transact-SQL ステートメントを実行しないようにします。 代わりに、SQL Server からステートメントの実行方法に関する詳細情報 (クエリ プラン) が返され、ステートメントのリソース要件と期待される行数の見積もり (カーディナリティ推定) が提供されます。

Transact-SQL 構文表記規則

構文

  
SET SHOWPLAN_ALL { ON | OFF }

Note

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

解説

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

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

SET SHOWPLAN_ALL は、その出力を処理するように作成されたアプリケーションで使用することを目的としています。 SET SHOWPLAN_TEXT は、osql ユーティリティなどの Microsoft Win32 コマンド プロンプト アプリケーションが読み取れる形式の出力を返す場合に使用します。

SET SHOWPLAN_TEXT および SET SHOWPLAN_ALL をストアド プロシージャ内で指定することはできません。これらはバッチ内の唯一のステートメントである必要があります。

SET SHOWPLAN_ALL では情報が行セットとして返されます。これは階層構造になっており、SQL Server クエリ プロセッサで各ステートメントが実行されるときのステップを表しています。 出力結果には、ステートメントごとに、ステートメントのテキストを示す 1 行と、実行ステップの詳細を示す複数行が含まれます。 次の表に、出力結果に含まれる列を示します。

列名 説明
StmtText PLAN_ROW 型でない行の場合、この列には Transact-SQL ステートメントのテキストが含まれます。 PLAN_ROW 型の行の場合、この列には操作の説明が含まれます。 またこの列には物理操作と、必要に応じて論理操作が含まれます。 この列の後に説明が続くこともありますが、その説明は物理操作によって決定されます。 詳細については、「プラン表示の論理操作と物理操作のリファレンス」を参照してください。
StmtId 現在のバッチに含まれるステートメント数。
NodeId 現在のクエリに含まれるノードの ID。
Parent 親ステップのノード ID。
PhysicalOp ノードの物理実装アルゴリズム。 型 PLAN_ROWS の行のみ。
LogicalOp ノードが表す関係代数操作。 型 PLAN_ROWS の行のみ。
Argument 実行されている操作に関する補足情報を指定します。 この列の内容は、物理操作に応じて異なります。
DefinedValues この演算子によって導入された値のコンマ区切りの一覧を含みます。 これらの値は、現在のクエリ (たとえば、SELECT リストや WHERE 句) に指定された計算式であるか、またはこのクエリを処理するためにクエリ プロセッサで導入された内部値です。 これらの定義された値は、このクエリ内の他の場所で参照される可能性があります。 型 PLAN_ROWS の行のみ。
EstimateRows 操作によって出力される行数の見積もり。 型 PLAN_ROWS の行のみ。
EstimateIO この演算子の推定 I/O コスト*。 型 PLAN_ROWS の行のみ。
EstimateCPU 操作の CPU コスト* の見積もり。 型 PLAN_ROWS の行のみ。
AvgRowSize この演算子に渡される行の推定平均行サイズ (バイト単位)。
TotalSubtreeCost この操作とすべての子操作の推定 (累積) コスト*。
OutputList 現在の演算によって示される列のコンマ区切りの一覧。
Warnings 現在の演算に関係する警告メッセージのコンマ区切りの一覧。 警告メッセージには、列の一覧が指定された文字列 "NO STATS:()" が含まれる場合があります。 この警告メッセージは、クエリ オプティマイザーによって、この列の統計に基づいて判断が試行され、使用できるものがなかったことを意味します。 その結果、クエリ オプティマイザーで推測する必要があり、結果として非効率的なクエリ プランが選択された可能性がありました。 クエリ オプティマイザーで効率的なクエリ プランを選択できるような列統計を作成し更新する方法の詳細については、「UPDATE STATISTICS」を参照してください。 この列には、必要に応じて文字列 "MISSING JOIN PREDICATE" を含めることができます。これは、(テーブルを含む) 結合が結合述語なしで行われていることを意味します。 結合述部を誤ってドロップすると、クエリの実行に予想よりも時間がかかり、膨大な結果セットが返される可能性があります。 この警告が返された場合は、結合述語が意図的に省略されているかどうかを確認してください。
Type ノード型です。 各クエリの親ノードの場合は、Transact-SQL ステートメントの種類 (SELECT、INSERT、EXECUTE など) を表します。 実行プランを表すサブノードの場合、種類は PLAN_ROW です。
Parallel 0 = 操作は並列実行されません。

1 = 操作は並列実行されます。
EstimateExecutions 現在のクエリの実行中に、操作が実行される推定回数。

\* コスト単位は、実時間ではなく、時間の内部測定に基づいています。 コスト単位は、プランの相対コストを他のプランと比較して決定するために使用されます。

アクセス許可

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

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

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

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

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

例

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

最初のクエリでは、インデックス列の WHERE 句で = (等しい) 比較演算子を使用しています。 この結果、LogicalOp 列に Clustered Index Seek 値が格納され、Argument 列にインデックスの名前が格納されます。

2 番目のクエリでは、WHERE 句で LIKE 演算子を使用します。 このように指定すると、SQL Server ではクラスター化インデックス スキャンが行われ、WHERE 句の条件を満たすデータが検索されます。 この結果、LogicalOp 列に Clustered Index Scan 値、Argument 列にインデックスの名前、LogicalOp 列にフィルター値、Argument 列に WHERE 句の条件がそれぞれ格納されます。

EstimateRows 列と TotalSubtreeCost 列の値は、インデックスが設定された最初のクエリの方が小さくなるので、インデックスが設定されていないクエリよりも速く処理が行われ、使用リソースが少なかったことがわかります。

USE AdventureWorks2022;  
GO  
SET SHOWPLAN_ALL ON;  
GO  
-- First query.  
SELECT BusinessEntityID   
FROM HumanResources.Employee  
WHERE NationalIDNumber = '509647174';  
GO  
-- Second query.  
SELECT BusinessEntityID, EmergencyContactID   
FROM HumanResources.Employee  
WHERE EmergencyContactID LIKE '1%';  
GO  
SET SHOWPLAN_ALL OFF;  
GO

参照

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