SET STATISTICS XML (Transact-SQL)

Causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.

Transact-SQL Syntax Conventions

Syntax

SET STATISTICS XML { ON | OFF }

Remarks

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

When SET STATISTICS XML is ON, SQL Server returns execution information for each statement after executing it. After this option is set ON, information about all subsequent Transact-SQL statements is returned until the option is set to OFF. Note that SET STATISTICS XML need not be the only statement in a batch.

SET STATISTICS XML returns 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.

SET STATISTICS XML returns information as a set of XML documents. Each statement after the SET STATISTICS XML ON statement is reflected in the output by a single document. Each document contains the text of the statement, followed by the details of the execution steps. The output shows run-time information such as the costs, accessed indexes, and types of operations performed, join order, the number of times a physical operation is performed, the number of rows each physical operator produced, and more.

The document containing the XML schema for the XML output by SET STATISTICS 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\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

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

SET STATISTICS PROFILE and SET STATISTICS XML are counterparts of each other. The former produces textual output; the latter produces XML output. In future versions of SQL Server, new query execution plan information will only be displayed through the SET STATISTICS XML statement, not the SET STATISTICS PROFILE statement.

Permissions

To use SET STATISTICS XML and view the output, users must have the following permissions:

• Appropriate permissions to execute the Transact-SQL statements.

• SHOWPLAN permission on all databases containing objects that are referenced by the Transact-SQL statements.

For Transact-SQL statements that do not produce STATISTICS XML result sets, only the appropriate permissions to execute the Transact-SQL statements are required. For Transact-SQL statements that do produce STATISTICS XML result sets, checks for both the Transact-SQL statement execution permission and the SHOWPLAN permission must succeed, or the Transact-SQL statement execution is aborted and no Showplan information is generated.

Examples

The two statements that follow use the SET STATISTICS 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 to find the data that satisfies the WHERE clause condition. The values in the EstimateRows and the EstimatedTotalSubtreeCost attributes are smaller for the first indexed query indicating that it was processed much faster and used fewer resources than the nonindexed query.

USE AdventureWorks2012;
GO
SET STATISTICS 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 STATISTICS XML OFF;
GO

See Also

Reference

SET SHOWPLAN_XML (Transact-SQL)

sqlcmd Utility