SET SHOWPLAN_XML (Transact-SQL)

**APPLIES TO:** ![yes](media/yes.png)SQL Server (starting with 2008) ![yes](media/yes.png)Azure SQL Database ![yes](media/yes.png)Azure SQL Data Warehouse ![no](media/no.png)Parallel Data Warehouse

Causes SQL Server not to execute SQL Server Transact\-SQL statements. Instead, SQL Server Transact\-SQL SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document

Topic link icon Transact-SQL Syntax Conventions

Syntax

  
SET SHOWPLAN_XML { ON | OFF }  

Remarks

The setting of SET SHOWPLAN_XML is set at execute or run time and not at parse time.

When SET SHOWPLAN_XML is ON, SQL Server returns execution plan information for each statement without executing it, and SQL Server Transact\-SQL statements are not executed. After this option is set ON, execution plan information about all subsequent SQL Server Transact\-SQL Transact\-SQL statements is returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_XML is ON, SQL Server Transact\-SQL Transact\-SQL 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_XML is OFF, SQL Server Transact\-SQL Transact\-SQL SQL Server SQL Server executes the statements without generating a report.

SET SHOWPLAN_XML is intended to return output as nvarchar(max) for applications such as the sqlcmd utility, where the XML output is subsequently used by other tools to display and process the query plan information.

[!NOTE]
The dynamic management view, sys.dm_exec_query_plan, returns the same information as SET SHOWPLAN XML in the xml data type. This information is returned from the query_plan column of sys.dm_exec_query_plan. For more information, see sys.dm_exec_query_plan (Transact-SQL).

SET SHOWPLAN_XML cannot be specified inside a stored procedure. It must be the only statement in a batch.

SET SHOWPLAN_XML returns information as a set of XML documents. Each batch after the SET SHOWPLAN_XML ON statement is reflected in the output by a single document. Each document contains the text of the statements in the batch, followed by the details of the execution steps. The document shows the estimated costs, numbers of rows, accessed indexes, and types of operators performed, join order, and more information about the execution plans.

The document containing the XML schema for the XML output by SET SHOWPLAN_XML is copied during setup to a local directory on the computer on which Microsoft SQL Server is installed. It can be found on the drive containing the SQL Server SQL Server installation files, at:

SQL Server\130\2004\07.xsd

The Showplan Schema can also be found at this Web site.

[!NOTE]
If Include Actual Execution Plan is selected in SQL Server Management Studio this SET option does not produce XML Showplan output. Clear the Include Actual Execution Plan button before using this SET option.

Permissions

In order to use SET SHOWPLAN_XML, you must have sufficient permissions to execute the statements on which SET SHOWPLAN_XML 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:

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

The two statements that follow use the SET SHOWPLAN_XML settings to show the way SQL Server analyzes and optimizes the use of indexes in queries.

The first query uses the Equals comparison operator (=) in the WHERE clause on an indexed column. The second query uses the LIKE operator in the WHERE clause. This forces SQL Server to use a clustered index scan and find the data meeting the WHERE clause condition. The values in the EstimateRows and the EstimatedTotalSubtreeCost attributes are smaller for the first indexed query, indicating that it is processed much faster and uses fewer resources than the nonindexed query.

USE AdventureWorks2012;  
GO  
SET SHOWPLAN_XML ON;  
GO  
-- First query.  
SELECT BusinessEntityID   
FROM HumanResources.Employee  
WHERE NationalIDNumber = '509647174';  
GO  
-- Second query.  
SELECT BusinessEntityID, JobTitle  
FROM HumanResources.Employee  
WHERE JobTitle LIKE 'Production%';  
GO  
SET SHOWPLAN_XML OFF;  

See Also

SET Statements (Transact-SQL)