How To Save Query Execution Plan In SQL Server

Query execution plan in SQL Server, in short, a result of query optimizer’s attempt to produce the least cost-effective and efficient plan for an executed query. SQL Server database engine uses advanced algorithms, available statistics, cardinality estimations to produce the most suitable plan. Particularly, the execution plan shows, how your submitted queries were executed by the database engine.

Moreover, SQL Server provides a graphical execution plan, presenting the graphical operators as icons and flow of data. Including bunch of useful statistics and information. Usually, I prefer a graphical execution plan over XML based query execution plans. SQL Server produces actual and estimated execution plans to help you optimize the performance of SQL queries.

In summary, we have understood the basics of execution plans and types. In addition to execution plans, whenever you are optimizing query performance, and need to share execution plans with your co-workers, or in online forums. Furthermore to have a look at your execution plan to help you find issues in it. You need a way to save the graphical execution plan so that it can be shared with anybody.

SQL Server provides a simpler way to save and share the execution plan from SQL Server Management Studio (SSMS).

We’ve created a table Employee and populated it with few rows before we generate can generate a graphical execution plan for the query.

USE tempdb;
GO

IF OBJECT_ID('Employee','U') IS NOT NULL
DROP TABLE Employee;
GO

CREATE TABLE Employee
(
    ID      INT PRIMARY KEY
   ,Name  VARCHAR(255)
);

INSERT INTO Employee VALUES
 (100, 'Ravi Shankar')
,(200, 'Ramesh Thakur')
,(300, 'Akshay Patel');

SELECT *
FROM   Employee
WHERE  ID = 200;

Click on Include Actual Execution Plan option or use CTRL+M keyboard shortcut. Next, execute the query and generate the graphical execution plan as below.

How To Save Graphical Execution Plan In SQL Server

You need to follow these steps to save graphical execution plan In SQL Server.

1. Go to Execution Plan tab and right-click anywhere on the pane.

2. You will see a context menu appeared, click on Save Execution Plan As option to save this execution plan.

3. Select where you want to save this execution plan. The file will be saved as *.sqlplan format.

You have successfully saved the execution plan. Open the .sqlplan file in notepad and you will notice that file has information stored in XML format. If you open this file with SSMS, it will open graphical execution as we have generated. It also stored the executed query for our immediate reference.

If you prefer to find and save the query execution plan from the query plan cache. You can run the following query which produces the list of plans cached by SQL Server. Subsequently, you can click on the query plan and save it following the steps mentioned earlier.

Get Query Plans From Plan Cache

SELECT queryplan.query_plan,
       cp.usecounts,
       SQLText.text
  FROM sys.dm_exec_cached_plans as cp
  CROSS APPLY sys.dm_exec_sql_text(plan_handle) as sqltext
  CROSS APPLY sys.dm_exec_query_plan(plan_handle) as queryplan
  WHERE objtype = 'Adhoc' and cp.cacheobjtype = 'Compiled Plan';

Summary

Consequently, you have learned the simplest way to save the graphical execution plan in SQL Server which can be shared with anyone easily. If you like this post you may read through tech-recipes database archive posts to learn some more useful stuff.