SQL:Show Execution Plan of SQL Statement--SHOWPLAN_ALL

SQL Server: Displaying the Execution Plan Using SHOWPLAN_ALL

SET SHOWPLAN_ALL can be used to display the execution plan of a Transact-SQL statement without actually executing the statement.

Syntax:

SET SHOWPLAN_ALL { ON | OFF }

Example:

SET SHOWPLAN_ALL ON;

CREATE TABLE TEST_TABLE
(
    T_ID INT,
    T_NAME VARCHAR(50)
);

SET SHOWPLAN_ALL OFF;

When SET SHOWPLAN_ALL is ON, SQL Server does not execute the Transact-SQL statements. Instead, it returns detailed information about how the statements would be executed, along with estimated resource requirements.

For example, if you execute a CREATE TABLE statement while SHOWPLAN_ALL is ON, the table is not actually created. SQL Server only returns the execution-plan information for that statement.

After checking the execution plan, turn SHOWPLAN_ALL OFF:

SET SHOWPLAN_ALL OFF;

The subsequent statements will then execute normally.

Example with SELECT:

SHOWPLAN_ALL ON:

SET SHOWPLAN_ALL ON;

SELECT * FROM TEST_TABLE;

SHOWPLAN_ALL OFF:

SET SHOWPLAN_ALL OFF;

SELECT * FROM TEST_TABLE;

When SHOWPLAN_ALL is ON, SQL Server returns information for each subsequent Transact-SQL statement without executing it. The output is returned as a set of rows that represents the execution steps used by the SQL Server query processor.

The output contains information about the operators involved in executing the statement and their estimated resource requirements.

Important:

SET SHOWPLAN_ALL must be the only statement in a batch. It cannot be specified inside a stored procedure.

If you need more readable execution-plan output, SQL Server also provides:

SHOWPLAN_TEXT

SHOWPLAN_XML

SHOWPLAN_ALL

SHOWPLAN_TEXT provides a text-based execution plan, while SHOWPLAN_XML returns the execution plan in XML format.

Parallel

The execution-plan output can also contain information about whether an operator is executed in parallel.

A value of 0 means the operator is not running in parallel.

A value of 1 means the operator is running in parallel.

The Parallel value does not indicate whether the query was successfully executed. When SHOWPLAN_ALL is ON, the statement is not actually executed.

Note:

Use SHOWPLAN_ALL carefully, especially when testing statements that create, modify, or delete database objects, because those statements will not actually be executed while SHOWPLAN_ALL is ON.