An execution plan is a roadmap that shows how SQL Server retrieves the data for a query. It breaks down the exact steps like which indexes to use, how tables are joined, and in what order operations are performed. The query optimizer creates this plan, evaluates multiple options, and chooses the most efficient one. Once generated, plans are stored in the plan cache for reuse.
In SQL Server, execution plans can be viewed as Graphical, Text, or XML formats.
Types of Execution Plan in SQL
There are two types of Execution Plans in SQL:
1. Actual Execution Plan
The actual execution plan is produced after the query has been executed. It reflects the real operations carried out by SQL Server, along with runtime performance details.
- Generated after the query runs.
- Includes runtime details like resource usage and warnings.
- Shows the exact plan used by the Database Engine.
- Displays the actual rows processed and other execution statistics.
2. Estimated Execution Plan
The estimated execution plan is created before the query executes. It represents the query optimizer’s prediction of how the query will run, based on available statistics.
- Generated before the query runs.
- Represents the query optimizer’s prediction of execution steps.
- Based on database statistics, schema, and indexes.
Execution Plan Generation and Saving in SSMS
Before and after the execution of the query, the execution plans in SQL Server. Actual and estimated execution plans can be achieved by the given steps:
Generation of Actual Execution Plans
The actual execution plan can be achieved in the following ways in SQL Server:
- After completely writing the query, Press Ctrl+M, and the actual execution plan will be generated.
- Go to the query window and right-click on it, then click on the context menu and select ‘Display Actual Execution Plan’.
- Or the ‘Display Actual Execution Plan’ icon can be directly selected from the toolbar.
Generation of Estimated Execution Plans
An estimated execution plan can be achieved using the following ways in SQL Server:
- After completely writing the query, Press Ctrl+L, and the plan will be generated.
- Go to the query window and right-click on it, then click on the context menu and select "Display Estimated Execution Plan".
- Or the "Display Estimated Execution Plan" icon can be directly selected from the toolbar.
How to save a Query Execution plan?
One has to save the query plan after interpreting the plan produced by the query. SQL Server Management Studio has an extension of ".sqlplan" for saving the plan in the system.
Steps to save an execution plan:
- Go to the plan window and right-click.
- Click on ‘Save Execution Plan As’.
- Click on the folder or location where you want to save the execution plan, then give the name to the plan and click on ‘Save’.
