Muokkaa

Jaa

SET SHOWPLAN_TEXT (Transact-SQL)

  • Artikkeli

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.

Transact-SQL syntax conventions

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 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 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 Showplan 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.

Examples

This example shows how indexes are used by SQL Server 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]))

See Also

Operators (Transact-SQL)
SET Statements (Transact-SQL)
SET SHOWPLAN_ALL (Transact-SQL)