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 Transact-SQL statements are not executed. After this option is set ON, execution plan 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_XML 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_XML is OFF, 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 installation files, at:

\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.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.

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:

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

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 AdventureWorks;
GO
SET SHOWPLAN_XML ON;
GO
-- First query.
SELECT EmployeeID 
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT EmployeeID, ContactID 
FROM HumanResources.Employee
WHERE Title LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;

Reference

Other Resources

Help and Information