In SQL Server, you can use the SET SHOWPLAN_XML statement to return detailed information about how a T-SQL statement will be executed, in the form of a well-defined XML document.

It’s similar to SHOWPLAN_ALL, except that SHOWPLAN_ALL returns its data set of rows that form a hierarchical tree.

You can set SHOWPLAN_XML to either ON or OFF.

When SHOWPLAN_XML is ON, all subsequent T-SQL statements are not executed. Instead SQL Server returns execution information for the statement (without executing it).

Example

Here’s an example to demonstrate.

SET SHOWPLAN_XML ON;
GO

SELECT * FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;
GO

Note that SET SHOWPLAN_XML cannot be specified inside a stored procedure, and it must be the only statement in a batch.

The result you get may depend on which tool you’re using to access SQL Server.

When I run this in Azure Data Studio, I can click on the various tabs to get a different view of the result.

The Results tab displays the raw XML string:

Clicking on the row opens the XML document in a new tab:

The Query Plan tab displays a graphical representation of the result:

The Top Operations tab presents the data in tabular format that enables you to sort the data by various metrics:

Not Working?

If it doesn’t work for you, make sure Include Actual Execution Plan is not selected in SSMS. When this is selected, SET SHOWPLAN_XML ON does not produce XML Showplan output.

How to Turn it Off

You can turn it off by using SET SHOWPLAN_XML OFF.

Once you’ve done this, subsequent statements will execute as normal.

SET SHOWPLAN_XML OFF;
GO

SELECT * FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;
GO

Result:

Commands completed successfully.
+---------+-----------+---------+-----------+-----------+
| CatId   | CatName   | DogId   | DogName   | GoodDog   |
|---------+-----------+---------+-----------+-----------|
| 2       | Fluffy    | 2       | Fluffy    | 0         |
+---------+-----------+---------+-----------+-----------+
(1 row affected)
Commands completed successfully.

Execution Plan in GUIs

If you’re using a graphical tool such as SSMS or Azure Data Studio, you might have a shortcut option for viewing the estimated graphical query execution plan. This allows you to view the query plan without needing to run SET SHOWPLAN_XML ON.

To run an estimated query plan:

• In SSMS you can use Ctrl + L to do this. Or you can click the Display Estimated Execution Plan icon, or right-click in the query window and select Display Estimated Execution Plan. This will save you from having to turn SHOWPLAN_XML on and off in your code.
• In Azure Data Studio, you can click the Explain button above the query window.

You can also run an actual query plan:

• In SSMS, on the Query menu, click Include Actual Execution Plan or click the Include Actual Execution Plan toolbar button.
• In Azure Data Studio, go to View > Command Palette and type Run Current Query with Actual Plan.

Note that if Include Actual Execution Plan is selected in SSMS, the SET SHOWPLAN_XML ON option does not produce XML Showplan output. Try clearing the Include Actual Execution Plan button before using this SET option.

However, I find Azure Data Studio does the opposite SHOWPLAN_XML ON seems to override the Run Current Query with Actual Plan option, except if I run Explain first (estimated query plan), after which Run Current Query with Actual Plan suddenly works (the Actual Rows and Actual Executions columns of the Top Operations tab return the appropriate data).

It’s probably best to turn the XML Showplan option off before using other methods for retrieving the query plan.