| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SET SHOWPLAN_TEXT (Transact-SQL) |
SET SHOWPLAN_TEXT (Transact-SQL) |
WilliamDAssafMSFT |
wiassaf |
06/10/2016 |
sql |
t-sql |
reference |
|
|
|
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
Causes Microsoft [!INCLUDEssNoVersion] not to execute [!INCLUDEtsql] statements. Instead, [!INCLUDEssNoVersion] returns detailed information about how the statements are executed.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
SET SHOWPLAN_TEXT { ON | OFF }
The setting of SET SHOWPLAN_TEXT is set at execute or run time and not at parse time.
When SET SHOWPLAN_TEXT is ON, [!INCLUDEssNoVersion] returns execution information for each [!INCLUDEtsql] statement without executing it. After this option is set ON, execution plan information about all subsequent [!INCLUDEssNoVersion] statements is returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_TEXT is ON, [!INCLUDEssNoVersion] returns an error message from a subsequent SELECT statement involving that same table informing the user that the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_TEXT is OFF, [!INCLUDEssNoVersion] executes statements without generating a report with execution plan information.
SET SHOWPLAN_TEXT is intended to return readable output for Microsoft Win32 command prompt applications such as the sqlcmd utility. SET SHOWPLAN_ALL returns more detailed output intended to be used with programs designed to handle its output.
SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified in a stored procedure. They must be the only statements in a batch.
SET SHOWPLAN_TEXT returns information as a set of rows that form a hierarchical tree representing the steps taken by the [!INCLUDEssNoVersion] query processor as it executes each statement. Each statement reflected in the output contains a single row with the text of the statement, followed by several rows with the details of the execution steps. The table shows the column that the output contains.
| Column name | Description |
|---|---|
| StmtText | For rows which are not of type PLAN_ROW, this column contains the text of the [!INCLUDEtsql] statement. For rows of type PLAN_ROW, this column contains a description of the operation. This column contains the physical operator and may optionally also contain the logical operator. This column may also be followed by a description which is determined by the physical operator. For more information about physical operators, see the Argument column in SET SHOWPLAN_ALL (Transact-SQL). |
For more information about the physical and logical operators that can be seen in Showplan output, see Showplan Logical and Physical Operators Reference
In order to use SET SHOWPLAN_TEXT, you must have sufficient permissions to execute the statements on which SET SHOWPLAN_TEXT is executed, and you must have SHOWPLAN permission for all databases containing referenced objects.
For SELECT, INSERT, UPDATE, DELETE, EXEC stored_procedure, and EXEC user_defined_function statements, to produce a Showplan the user must:
-
Have the appropriate permissions to execute the [!INCLUDEtsql] statements.
-
Have SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements, such as tables, views, and so on.
For all other statements, such as DDL, USE database_name, SET, DECLARE, dynamic SQL, and so on, only the appropriate permissions to execute the [!INCLUDEtsql] statements are needed.
This example shows how indexes are used by [!INCLUDEssNoVersion] as it processes the statements.
This is the query using an index:
USE AdventureWorks2022;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT *
FROM Production.Product
WHERE ProductID = 905;
GO
SET SHOWPLAN_TEXT OFF;
GO Here is the result set:
StmtText
---------------------------------------------------
SELECT *
FROM Production.Product
WHERE ProductID = 905;
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Seek(OBJECT:([AdventureWorks2022].[Production].[Product].[PK_Product_ProductID]), SEEK:([AdventureWorks2022].[Production].[Product].[ProductID]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
Here is the query not using an index:
USE AdventureWorks2022;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT *
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET SHOWPLAN_TEXT OFF;
GO Here is the result set:
StmtText
------------------------------------------------------------------------
SELECT *
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([AdventureWorks2022].[Production].[ProductCostHistory].[PK_ProductCostHistory_ProductCostID]), WHERE:([AdventureWorks2022].[Production].[ProductCostHistory].[StandardCost]<[@1]))
Operators (Transact-SQL)
SET Statements (Transact-SQL)
SET SHOWPLAN_ALL (Transact-SQL)