The execution plan refers to the detailed roadmap or strategy devised by the database engine to execute an SQL query. It outlines the steps and operations the database will perform to retrieve and manipulate the data requested in the query. The execution plan provides insights into the query's performance and helps identify areas for optimization. By analyzing and understanding the execution plan, developers and database administrators can make informed decisions to enhance the query's efficiency and overall system performance.
In SQL Server query qxecution plan explores:
SELECTstatement referencing three tables has six different variants of table access order.
ORDER BYclause, an index scan may be performed instead of a table scan to avoid a separate sort operation. Table scans can be the most efficient method when dealing with very small tables.
HAVINGclauses), aggregating data (e.g., with
GROUP BY), and sorting data (e.g., with
ORDER BY) may be applied. The query execution plan defines how these operations are carried out based on the query text.
SQL Server provides three types of execution plans: Estimated plan, Actual plan and Live Query Statistics.
When you submit a query to the SQL Server, the query optimizer immediately generates a graphical execution plan that provides an estimated execution plan time. The SQL Server then follows the steps outlined in the query plan, and you will receive the actual cost of executing the estimated plan. Being created before execution of the query, this plan is just an estimation made by the query processor that is not supplied with any runtime information.
This estimated execution plan allows you to assess the expected performance of your query without actually executing it. It provides valuable insights into how the SQL Server intends to process your query, including the order in which tables are accessed, the methods used to retrieve data, and the operations performed on the data. By examining the estimated execution plan, you can make informed decisions about query optimization and performance tuning.
The Actual Execution Plan is generated only after execution of the query is completed. This plan is utilized for troubleshooting performance issues during query execution in order to enhance and optimize performance.
Features of the Actual Execution Plan: