SET SHOWPLAN_XML (Transact-SQL)

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

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 information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements are 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.

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

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:

• 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

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;

See Also

Reference

SET (Transact-SQL)

Other Resources

How to: Save an Execution Plan in XML Format
Analyzing a Query
Transact-SQL Statements That Produce Showplans
Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
5 December 2005	Changed content: Changed installation location for the Showplan XML schema.