Execution Plans in SQL Server

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:

Access order for the source tables. DBMS can access the base tables in different sequences to construct the result set. For example, SELECT statement referencing three tables has six different variants of table access order.

The techniques of extracting data from each table. Different methods exist for accessing data in each table. For instance, if only a few rows with specific key values are needed, the database server can utilize an index. On the other hand, SQL Server might opt for a table scan disregarding indexes if all rows in the table are required. In cases where all rows are needed, but there is an index with key columns specified in an ORDER BY clause, 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.

The approaches to computing calculations, filtering, aggregating, and sorting the data. Since data is retrieved from tables, different methods of calculations (e.g., computing scalar values), filtering data (e.g., using WHERE or HAVING clauses), 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.

Estimated Execution Plan

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.

Actual Execution Plan

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:

It illustrates the precise steps taken by the SQL Server to execute the query
It provides real-time information from the query processor (execution warnings, used CPU time, etc)
It offers comprehensive details about the steps involved in executing the query, allowing for in-depth analysis and optimization

Have questions? Contact us