SET SHOWPLAN_TEXT (Transact-SQL)
Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.
.gif "Topic link icon")
Syntax
SET SHOWPLAN_TEXT { ON | OFF }
Remarks
The setting of SET SHOWPLAN_TEXT is set at execute or run time and not at parse time.
When SET SHOWPLAN_TEXT is ON, SQL Server returns execution information for each Transact-SQL statement without executing it. After this option is set ON, execution plan information about all subsequent SQL Server statements is returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_TEXT is ON, SQL Server 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, SQL Server 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 osql 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 SQL Server 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 Transact-SQL 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 Logical and Physical Operators Reference
Permissions
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 Transact-SQL 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 Transact-SQL statements are needed.
For more information, see Showplan Security and SHOWPLAN Permission and Transact-SQL Batches.
Examples
This example shows how indexes are used by SQL Server as it processes the statements.
This is the query using an index:
USE AdventureWorks2008R2;
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:([AdventureWorks2008R2].[Production].[Product].[PK_Product_ProductID]), SEEK:([AdventureWorks2008R2].[Production].[Product].[ProductID]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
Here is the query not using an index:
USE AdventureWorks2008R2;
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:([AdventureWorks2008R2].[Production].[ProductCostHistory].[PK_ProductCostHistory_ProductCostID]), WHERE:([AdventureWorks2008R2].[Production].[ProductCostHistory].[StandardCost]<[@1]))