Open In App

Query-Execution Plan in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

The steps of the process performed at the time of query execution by the engine database are described by a set of instructions called a query plan. The query plan is also referred to as the SQL Server execution plan.

The query optimizer generates the SQL Server execution plan or query plan. The generation of an optimum and economic query plan is the main objective of the query optimizer. Multiple execution plans are generated by the query processing engine after query execution, and from those generated execution plans, a plan with the best performance is selected. A plan cache is a memory location where the execution plans are stored and can be reused.

Execution plans are available in 3 forms in the SQL server, which are XML plans, Graphical plans, and Text plans.

Types of Execution Plan:

Estimated Execution Plan and Actual Execution Plan are two kinds of execution plans:

1. Actual Execution Plan: When a query is executed then the Actual Execution Plan comes into the picture. At the time of query execution, the real process and strategy included are shown.

2. Estimated Execution Plan: The query processor only guesses the precise actions involved at the time of the result returning. Sometimes, it is generated before the query execution.

Generating and Saving Execution Plans in SQL Server Management Studio:

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:

  1. After completely writing the query, Press Ctrl+M, and the actual execution plan will be generated. 
  2. Go to the query window and right-click on it, then click on the context menu and select ‘Display Actual Execution Plan’.
  3. 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:

  1. After completely writing the query, Press Ctrl+L, and the plan will be generated. 
  2. Go to the query window and right-click on it, then click on the context menu and select “Display Estimated Execution Plan“.
  3. Or the “Display Estimated Execution Plan” icon can be directly selected from the toolbar.

How to save a plan?

One has to save the query 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:

  1. Go to the plan window and right-click.
  2. Click on ‘Save Execution Plan As’.
  3. 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’.

Last Updated : 31 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads