Save an Execution Plan in XML Format

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Use SQL Server Management Studio to save execution plans as an XML file, and to open them for viewing.

To use the execution plan feature in Management Studio, or to use the XML Showplan SET options, users must have the appropriate permissions to execute the Transact-SQL query for which an execution plan is being generated, and they must be granted the SHOWPLAN permission for all databases referenced by the query.

To save a query plan by using the XML Showplan SET options

1. In SQL Server Management Studio open a query editor and connect to Database Engine.

2. Turn SHOWPLAN_XML on with the following statement:

   SQL

   SET SHOWPLAN_XML ON;  
   GO  
   

   To turn STATISTICS XML on, use the following statement:

   SQL

   SET STATISTICS XML ON;  
   GO  
   

   Note

   SHOWPLAN_XML generates compile-time query execution plan information for a query, but does not execute the query. This is also known as the estimated execution plan. STATISTICS XML generates runtime query execution plan information for a query, and executes the query. This is also known as the actual execution plan.

3. Execute a query. Example:

   SQL

   USE AdventureWorks2022;  
   GO  
   SET SHOWPLAN_XML ON;  
   GO  
   -- Execute a query.  
   SELECT BusinessEntityID   
   FROM HumanResources.Employee  
   WHERE NationalIDNumber = '509647174';  
   GO  
   SET SHOWPLAN_XML OFF;  
   

4. In the Results pane, right-click the Microsoft SQL Server XML Showplan that contains the query plan, and then click Save Results As.

5. In the Save <Grid or Text> Results dialog box, in the Save as type box, click All files (*.*).

6. In the File name box provide a name, in the format <name>.sqlplan, and then click Save.

To save an execution plan by using SQL Server Management Studio options

1. Generate either an estimated execution plan or an actual execution plan by using Management Studio. For more information, see Display the Estimated Execution Plan and Display an Actual Execution Plan.

2. In the Execution plan tab of the results pane, right-click the graphical execution plan, and choose Save Execution Plan As.

   As an alternative, you can also choose Save Execution Plan As on the File menu.

3. In the Save As dialog box, make sure that the Save as type is set to Execution Plan Files (*.sqlplan).

4. In the File name box provide a name, in the format <name>.sqlplan, and then click Save.

To open a saved XML query plan in SQL Server Management Studio

1. In SQL Server Management Studio, on the File menu, choose Open, and then click File.

2. In the Open File dialog box, set Files of type to Execution Plan Files (*.sqlplan) to produce a filtered list of saved XML query plan files.

3. Select the XML query plan file that you want to view, and click Open.

   As an alternative, in Windows Explorer, double-click a file with extension .sqlplan. The plan opens in Management Studio.

See Also

SET SHOWPLAN_XML (Transact-SQL)
SET STATISTICS XML (Transact-SQL)