In SQL Server, you can use the SET STATISTICS XML statement to generate detailed information about how a T-SQL statement was executed.

This information is provided in the form of a well formed XML document.

Once SET STATISTICS XML is set to ON, each subsequent statement is reflected in the output by a single XML document.

Each XML 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.

Example

Heres’s a simple example to demonstrate.

SET STATISTICS XML ON;

SELECT 
    c.CityName, 
    s.StateProvinceName AS State, 
    c.LatestRecordedPopulation AS Population
FROM Application.Cities c
INNER JOIN Application.StateProvinces s
ON c.StateProvinceID = s.StateProvinceID
WHERE c.LatestRecordedPopulation > 2000000
ORDER BY c.LatestRecordedPopulation DESC;

Result:

When you do this using a graphical tool such as SSMS or Azure Data Studio (pictured), you’ll probably see a graphical query plan like the one in the screenshot.

In this case, you will probably be able to click various tabs to see the actual results, as well as other statistics data.

Here’s what happens when I click on the Results tab:

In this case I get to see the actual results, plus another row with the XML Showplan. Clicking on that row opens the XML document in a new window:

And here’s what happens when I click on the Top Operations tab:

How to Turn it Off

Setting STATISTICS XML to ON affects all subsequent T-SQL statements until it is turned off.

To turn it off, simply run it again using OFF instead of ON:

SET STATISTICS XML OFF;

STATISTICS XML Replaces STATISTICS PROFILE

According to the Microsoft documentation, SET STATISTICS XML should be used instead of SET STATISTICS PROFILE.

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.

STATISTICS XML vs Include Actual Execution Plan

If Include Actual Execution Plan is selected in SSMS, SET STATISTICS XML does not produce XML Showplan output. Clear the Include Actual Execution Plan button before using SET STATISTICS XML.