Query Execution Plan in SQL

A query execution plan (also known as a query plan) is a sequence of steps used by a relational database management system (RDBMS) to access data efficiently when executing a query. 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. Its goal is to generate an optimal economic query plan. 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.

The execution plans are stored in the plan cache memory location, from where they can be reused. Execution plans are available in 3 forms in the SQL server: XML plans, Graphical plans, and Text plans.

Here, we will learn all about Query-Execution Plans in SQL, understand the types of query execution plans, how to create query plans, and at last how to save query plans in SQL Server.

There are two types of Execution Plans in SQL:

• Estimated Execution Plan
• Actual Execution Plan

1. Actual Execution Plan

An actual execution plan is the SQL Server query plan that is generated after a query has been executed. It contains runtime information, such as actual resource usage metrics and any runtime warnings that occurred during execution.

An actual execution plan displays the actual query execution plan that the SQL Server Database Engine used to execute the queries. You can find the information about actual number of rows processed, resource utilization, and other relevant statistics

2. Estimated Execution Plan

An estimated execution plan is a prediction made by the SQL Server query optimizer regarding the steps it expects to take when executing a query. The estimated execution plan is generated before the query is executed.

Estimated execution plans are created during query compilation, before the query is actually executed. These plans are based on statistical information about the database schema, indexes, and data distribution.

An estimated execution plan provides information about the expected sequence of operations. It includes details about the logical and physical operators involved (e.g., scans, joins, sorts).

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.

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:

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’.

In this article, we learn about query-execution plan in SQL. We covered the meaning of SQL Query plans, it’s types and how to create and save these query plans in SQL Server.

RDMS use these query execution plans to follow a sequence of steps while executing a query. An optimized query execution plan save time and money for querying the database.