SET SHOWPLAN_TEXT

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

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, information about all subsequent Transact-SQL 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; 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.

SET SHOWPLAN_TEXT is intended to return readable output for Microsoft MS-DOS® 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.

For more information about the physical and logical operators that can be seen in showplan output, see Logical and Physical Operators.

Permissions

SET SHOWPLAN_TEXT permissions default to all users.

Examples

This example shows how indexes are used by SQL Server as it processes the statements.

This is the query using an index:

SET SHOWPLAN_TEXT ON
GO
USE pubs
SELECT *
FROM roysched
WHERE title_id = 'PS1372'
GO
SET SHOWPLAN_TEXT OFF
GO

Here is the result set:

StmtText                                               
------------------------------------------------------ 
USE pubs

SELECT *
FROM roysched
WHERE title_id = 'PS1372'

(2 row(s) affected)

StmtText                                                                
------------------------------------------------------------------------
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[roysched]))
       |--Index Seek(OBJECT:([pubs].[dbo].[roysched].[titleidind]), SEEK:([roysched].[title_id]='PS1372') ORDERED)

(2 row(s) affected)

Here is the query not using an index:

SET SHOWPLAN_TEXT ON
GO
USE pubs
SELECT *
FROM roysched
WHERE lorange < 5000
GO
SET SHOWPLAN_TEXT OFF
GO

Here is the result set:

StmtText                                          
------------------------------------------------- 
USE pubs

SELECT *
FROM roysched
WHERE lorange < 5000

(2 row(s) affected)

StmtText                                                                
------------------------------------------------------------------------
  |--Table Scan(OBJECT:([pubs].[dbo].[roysched]), WHERE:([roysched].[lorange]<5000))

(1 row(s) affected)

See Also

Operators

SET

SET SHOWPLAN_ALL