Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)
The Transact-SQL SET statement options for displaying execution plan information produce output in XML and text. These options are listed and described in this topic.
Showplan SET Statement Options
Transact-SQL provides the following options to its SET statement that enable you to display a query execution plan:
Note
The SHOWPLAN_XML, SHOWPLAN_ALL, and SHOWPLAN_TEXT SET options produce one rowset for each batch. The STATISTICS XML and STATISTICS PROFILE SET options produce one rowset for each query in a batch.
SET SHOWPLAN_XML ON
This statement causes SQL Server not to execute Transact-SQL statements. Instead, Microsoft SQL Server returns execution plan information about how the statements are going to be executed in a well-formed XML document. For more information, see SET SHOWPLAN_XML (Transact-SQL).
SET SHOWPLAN_TEXT ON
After this SET statement is executed, SQL Server returns the execution plan information for each query in text. The Transact-SQL statements or batches are not executed. For more information, see SET SHOWPLAN_TEXT (Transact-SQL).
SET SHOWPLAN_ALL ON
This statement is similar to SET SHOWPLAN_TEXT, except that the output is in a format more verbose than that of SHOWPLAN_TEXT. For more information, see SET SHOWPLAN_ALL (Transact-SQL).
SET STATISTICS XML ON
Returns execution information for each statement after the statement executes in addition to the regular result set the statement returns. The output is a set of well-formed XML documents. SET STATISTICS XML ON produces an XML output document for each statement that executes. The difference between SET SHOWPLAN_XML ON and SET STATISTICS XML ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS XML ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators. For more information, see SET STATISTICS XML (Transact-SQL).
SET STATISTICS PROFILE ON
Returns the execution information for each statement after the statement executes in addition to the regular result set the statement returns. Both SET statement options provide output in text. The difference between SET SHOWPLAN_ALL ON and SET STATISTICS PROFILE ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS PROFILE ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators. For more information, see SET STATISTICS PROFILE (Transact-SQL).
SET STATISTICS IO ON
Displays information about the amount of disk activity that is generated by Transact-SQL statements after the statements execute. This SET option produces text output. For more information, see SET STATISTICS IO (Transact-SQL).
SET STATISTICS TIME ON
Displays the number of milliseconds required to parse, compile, and execute each Transact-SQL statement after statements execute. This SET option produces text output. For more information, see SET STATISTICS TIME (Transact-SQL).
Considerations for Using the Showplan SET Statement Options
When you display an execution plan using the SHOWPLAN SET options, the statements you submit to the server are not executed. Instead, SQL Server analyzes the query and displays, in a series of operators, how the statements would have been executed.
Note
Because statements are not executed when the execution plan is displayed, Transact-SQL operations are not actually carried out. So, for example, if a CREATE TABLE statement is part of an execution plan, any later operations involving the "created" table returns errors, because the table does not exist. However, there are two exceptions to this rule: temporary tables are created when using the SHOWPLAN SET options, and USE db_name statements are executed and attempt to change the database context to the db_name specified when using the SHOWPLAN SET options.
When you display an execution plan using the STATISTICS SET options, the Transact-SQL statements you submit to the server are executed.
Note
The Showplan SET options display no information about encrypted stored procedures or triggers.
SET Options Scheduled for Deprecation in Future Showplan Versions
In a future version of SQL Server, the following Showplan SET options will be deprecated. We recommend that users move to the newer modes as soon as possible. The following table lists the Showplan SET options that are scheduled for deprecation with the new SET options that users should start using.
Deprecated SET Option |
Use New SET Option |
|---|---|
SET SHOWPLAN_TEXT |
SET SHOWPLAN_XML |
SET SHOWPLAN_ALL |
SET SHOWPLAN_XML |
SET STATISTICS PROFILE |
SET STATISTICS XML |