適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース
Microsoft SQL Server で Transact-SQL ステートメントを実行しないようにします。 代わりに、SQL Server からステートメントの実行方法に関する詳細情報 (クエリ プラン) が返され、ステートメントのリソース要件と期待される行数の見積もり (カーディナリティ推定) が提供されます。
構文
SET SHOWPLAN_ALL { ON | OFF }
解説
SET SHOWPLAN_ALLの設定は、解析時ではなく実行時に設定されます。
SET SHOWPLAN_ALL が ON の場合、SQL Server は、各ステートメントを実行せずに実行情報だけを返します。Transact-SQL ステートメントは実行されません。 返される情報は、このオプションが ON に設定されてから OFF に設定されるまでに実行されたすべての Transact-SQL ステートメントに関する情報です。 例えば、CREATE TABLE文がSET SHOWPLAN_ALLがONのまま実行されると、SQL Server同じテーブルに関する後続のSELECT文からエラーメッセージを返し、指定されたテーブルが存在しないことをユーザーに知らせます。 その後、このテーブルに対して行われる参照は失敗します。
SET SHOWPLAN_ALLがオフのとき、SQL Serverレポートを生成せずに文を実行します。
SET SHOWPLAN_ALL は、その出力を処理するように作成されたアプリケーションで使用することを目的としています。
SET SHOWPLAN_TEXTを使って、OsqlユーティリティなどのWin32コマンドプロンプトアプリケーションの読み取れる出力Microsoft返してください。
SET SET SHOWPLAN_TEXT そして SETSET 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 SET SHOWPLAN_ALLを使用するには、SETSET SHOWPLAN_ALLを実行する文を実行するための十分な権限を持ち、参照対象オブジェクトを含むすべてのデータベースに対して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_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)