| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SET STATISTICS XML (Transact-SQL) |
SET STATISTICS XML (Transact-SQL) |
WilliamDAssafMSFT |
wiassaf |
06/10/2016 |
sql |
t-sql |
reference |
|
|
|
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
Causes Microsoft [!INCLUDEssNoVersion] to execute [!INCLUDEtsql] statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
SET STATISTICS XML { ON | OFF }
The setting of SET STATISTICS XML is set at execute or run time and not at parse time.
When SET STATISTICS XML is ON, [!INCLUDEssNoVersion] returns execution information for each statement after executing it. After this option is set ON, information about all subsequent [!INCLUDEtsql] 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 [!INCLUDEssNoVersion] is installed. It can be found on the drive containing the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion], new query execution plan information will only be displayed through the SET STATISTICS XML statement, not the SET STATISTICS PROFILE statement.
Note
If Include Actual Execution Plan is selected in [!INCLUDEssManStudioFull], this SET option does not produce XML Showplan output. Clear the Include Actual Execution Plan button before using this SET option.
To use SET STATISTICS XML and view the output, users must have the following permissions:
-
Appropriate permissions to execute the [!INCLUDEtsql] statements.
-
SHOWPLAN permission on all databases containing objects that are referenced by the [!INCLUDEtsql] statements.
For [!INCLUDEtsql] statements that do not produce STATISTICS XML result sets, only the appropriate permissions to execute the [!INCLUDEtsql] statements are required. For [!INCLUDEtsql] statements that do produce STATISTICS XML result sets, checks for both the [!INCLUDEtsql] statement execution permission and the SHOWPLAN permission must succeed, or the [!INCLUDEtsql] statement execution is aborted and no Showplan information is generated.
The two statements that follow use the SET STATISTICS XML settings to show the way [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 AdventureWorks2022;
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