SET SHOWPLAN_XML (Transact-SQL)

Specifies that SQL Server should return the showplan information for each statement in XML format.

Syntax

                    SET SHOWPLAN_XML { ON | OFF }

Description

When SET SHOWPLAN_XML ON is specified, SQL Server returns the execution plan for each statement that follows. The information is returned as an XML document. When SET SHOWPLAN_XML OFF is specified, it stops the return of showplan information.

SHOWPLAN_XML provides more detailed information about the execution plan compared to the older SHOWPLAN_ALL or SHOWPLAN_TEXT.

Note: You must have the appropriate permissions to view execution plans. Typically, this is the SHOWPLAN permission.

Permissions

Users must be granted the SHOWPLAN permission to use SET SHOWPLAN_XML.

Examples

A. Displaying the XML showplan for a SELECT statement

This example shows how to enable SHOWPLAN_XML and then execute a simple SELECT statement. The output will include the XML execution plan.

                    SET SHOWPLAN_XML ON;
                    GO
                    SELECT Column1, Column2
                    FROM YourTable
                    WHERE Column3 = 10;
                    GO
                    SET SHOWPLAN_XML OFF;
                    GO

B. Using SHOWPLAN_XML with a stored procedure

You can also capture the plan for stored procedures. Ensure you recompile the procedure if needed to get the latest plan.

                    SET SHOWPLAN_XML ON;
                    GO
                    -- Assuming you have a stored procedure named usp_GetCustomerData
                    EXEC usp_GetCustomerData 123;
                    GO
                    SET SHOWPLAN_XML OFF;
                    GO

Remarks

SET SHOWPLAN_XML ON affects only the statements that are executed after it is set.
The XML output can be viewed in a browser or saved to a file for analysis using tools like SQL Server Management Studio (SSMS) or XQuery.
SHOWPLAN_XML is generally preferred over SHOWPLAN_TEXT and SHOWPLAN_ALL for detailed analysis due to its structured nature.
Consider using SET SHOWPLAN_XML OFF to disable the feature and prevent unnecessary overhead.