save-an-execution-plan-in-xml-format.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	ms.custom	helpviewer_keywords	monikerRange
Save an Execution Plan in XML Format	Learn how to use SQL Server Management Studio to save execution plans in XML format and to open them for viewing. You must have appropriate permissions.	MikeRayMSFT	mikeray	08/21/2017	sql	performance	how-to	ignite-2024	XML query plans [SQL Server] opening execution plans XML Showplans [SQL Server] execution plans [SQL Server], saving saving execution plans	=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Save an Execution Plan in XML Format

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB] Use [!INCLUDEssManStudioFull] to save execution plans as an XML file, and to open them for viewing.

To use the execution plan feature in [!INCLUDEssManStudio], or to use the XML Showplan SET options, users must have the appropriate permissions to execute the [!INCLUDEtsql] 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 [!INCLUDEssManStudioFull] open a query editor and connect to [!INCLUDEssDE].

2. Turn SHOWPLAN_XML on with the following statement:

   SET SHOWPLAN_XML ON;  
   GO  

   To turn STATISTICS XML on, use the following statement:

   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:

   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 [!INCLUDEssManStudio]. 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 [!INCLUDEssManStudioFull], 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 [!INCLUDEssManStudio].

See Also

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