Definition :
An execution plan is a set of instructions generated by the database engine’s query optimizer that outlines the most efficient way to execute a SQL query. The execution plan is a detailed roadmap that includes information about the tables and indexes involved in the query, the order in which they will be accessed, and the operations that will be performed to produce the query result set.
SQL execution plans are crucial for optimising database performance by minimising the time and resources required to execute queries. By analysing and understanding the execution plan generated by the query optimizer, database administrators and developers can identify areas for optimization and improve the overall performance of their database systems. The execution plan can be viewed using various SQL Server tools such as SQL Server Management Studio, SQL Server Profiler, or SQL Server Data Tools.
How to generate query execution plan :
The query optimizer is responsible for generating execution plans in SQL. When a query is submitted to the database engine, the optimizer analyses the query and generates an execution plan that specifies the steps required to execute the query and return the desired results.
To generate an execution plan, the query optimizer considers several factors, including:
- Table and index statistics: The optimizer uses statistics about the tables and indexes involved in the query to estimate the number of rows that will be returned by each step in the execution plan.
- Query structure: The optimizer considers the structure of the query, including the join conditions, filter conditions, and grouping requirements, to determine the most efficient plan for executing the query.
- Available resources: The optimizer considers the available system resources, such as memory and CPU, when generating the execution plan. It attempts to generate a plan that minimises resource usage while still producing the desired results.
- Database schema: The optimizer considers the structure of the database schema, including relationships between tables, to generate a plan that retrieves the required data in the most efficient manner.
- Query hints and options: The optimizer considers any hints or options specified in the query, such as indexes to use or the maximum number of rows to return, when generating the execution plan.
Once the optimizer has generated an execution plan, it is returned to the database engine, which uses the plan to execute the query and return the results to the user. The optimizer may generate multiple execution plans for a given query, and it will choose the one that is estimated to be the most efficient based on the factors listed above.
Different formats of execution plan:-
- Textual format : The textual format of an execution plan is a detailed description of the steps involved in executing a query. This format is easy to read and understand.
- Graphical format : The graphical format of an execution plan provides a visual representation of the steps involved in executing a query. It is easier to analyse for complex queries and allows for better understanding of the query execution flow.
- XML format : The XML format of an execution plan is a machine-readable format that can be used by tools to analyse and optimise the query execution. It is useful for automation and can be easily processed by other applications.
- JSON format : Some database management systems also support execution plans in JSON format. JSON is a lightweight, human-readable format that is easy to process and analyse.
The format of the execution plan that is used depends on the database management system and the tools used to analyse the query performance. In general, the graphical and XML formats are preferred for complex queries, while the textual and JSON formats are suitable for simpler queries.
Types of execution plan
- Actual execution plan : An actual execution plan is generated when a query is executed, and it shows the actual steps taken by the query optimizer to execute the query. The actual execution plan can be used to analyse the performance of the query and to identify potential performance issues.
- Estimated execution plan : An estimated execution plan is generated by the query optimizer before a query is executed, and it shows the estimated steps that will be taken to execute the query. The estimated execution plan can be used to evaluate the performance of a query before it is executed, and to identify potential performance issues.
Both types of execution plans include details such as the tables and indexes involved in the query, the order in which they will be accessed, and the operations that will be performed to produce the query result set. The estimated execution plan may be less accurate than the actual execution plan, as it is based on assumptions about the data and the query, whereas the actual execution plan is based on the real data and conditions at the time of execution.
Actual execution plan : It refers to the process and strategy that are actually implemented during query execution.
Steps to view the actual execution plan:-
To view the actual execution plan, first write the query, then press Ctrl+M, and in the query window, right-click and select “Include actual Execution Plan”.
The “Include actual execution Plan” icon can be directly selected from the toolbar.
Estimated execution plan : The query optimizer predicts the exact steps that will be involved in producing the query result set, and it typically generates the execution plan before the query execution takes place.
Steps to view the estimated execution plan:-
To view the estimated execution plan, first write the query, then press Ctrl+L, and in the query window, right-click and select “Display Estimated Execution Plan”.
The “Display Estimated Execution Plan” icon can be directly selected from the toolbar.
Saving an Execution Plan in SQL Server Management Studio
To save the execution plan, right-click in the plan window and select “Save Execution Plan As”.
Choose the folder or location where you want to save the execution plan, provide a name for the plan, and then click on the “Save” button.
Conclusion:
Understanding the execution plan is essential for optimizing query performance. By analyzing the execution plan, you can identify potential performance bottlenecks and make changes to the SQL statement or database schema to improve performance. The execution plan provides a roadmap for how the database engine processes a particular SQL statement and includes information about access methods, joins, filters, sorts, and more. By understanding the execution plan, you can optimize indexes, identify performance bottlenecks, and improve query performance.