Execution Plans in SQL Server

Introduction

In this article, I’m going to explain what the Execution Plans in SQL Server are and how to understand the details of an execution plan by reading the various metrics available once we hover over the components in the plan.

Being a Data Professional, it is very essential that we must understand how to write efficient queries that return faster results and how to tune the slow performing queries to achieve a boost in performance. In order to understand how the database engine works behind the scenes, we need to know the logical operations performed by the query processor. This can be done by investigating the execution plans generated by the query processor. An execution plan in SQL Server is a simple graphical representation of the operations that the query optimizer generates to calculate the most efficient way to return a set of results.

What is an Execution Plan?

As already mentioned earlier, an execution plan in SQL Server Management Studio is a graphical representation of the various steps that are involved in fetching results from the database tables. Once a query is executed, the query processing engine quickly generates multiple execution plans and selects the one which returns the results with the best performance. There are two types of execution plans to be specific –

1. Estimated Execution Plan – As the name suggests, this type of execution plan is just a guess by the query processor about how the specific steps that are to be involved while returning the results. It is often generated before the query has been executed
2. Actual Execution Plan – The Actual Execution Plan is generated after the query has been executed. It shows the actual operations and steps involved while executing the query. This may or may not differ from the Estimated Execution Plan

How to generate Execution Plans?

As we have learned, the Execution Plans in SQL Server can be generated before and after the query has been executed, I’m going to mention the various steps on how you can get the estimated and actual execution plans. 

 

In the execution plan depicted in the above Figure 5, if you hover the cursor over the components, you can view the detailed stats for each of the operations and components being displayed in the execution plan. The plan is interpreted from right-to-left and top-to-bottom. Since our plan consists of only one single row, there is no need for the top-to-bottom approach. 

 

 

     We can distinguish the execution plan into the following five steps:

1. Clustered Index Scan
2. Data Flow from Clustered Index Scan (Arrow)
3. Sort Operator
4. Data Flow from Sort Operator (Arrow)
5. Select Operator

For the sake of this tutorial, let’s only understand in detail the various metrics that are being involved for the Clustered Index Scan operator. The details of the other operators can also be viewed similarly; however, the detailed explanation of the metrics of the other operators is beyond the scope of this article. 

 

Clustered Index Scan

The first component when we traverse from right-to-left is the Clustered Index Scan component. This Index Scan is performed on the Primary Key of the table i.e. “PK_Dimension_Customer“. Let’s understand each of the metrics that are being displayed while we hover over the clustered index scan operator. You can refer to the Figure 7, for understanding more. 

 

 

• Physical Operation: These are the operators that implement the operation as directed by the logical operators. All the physical operators are usually object which perform an operation. Some examples are Clustered Index Scan, Index Seek etc.
• Logical Operation: These operators describe the actual algebraic operation that is used to process the query. Examples are Right Anti Semi Join, Hash Join etc.
• Actual Execution Mode: This is the actual execution mode that is used by the processing engine to execute the query. Examples – Row and Batch
• Estimated Execution Mode: This is similar to Actual Execution Mode but shows the estimated value
• Storage: This tells us how the query optimizer will store the results that are being extracted by the query
• Number of Rows Read: This returns the total number of records that are being read by the operator from the table index
• Actual Number of Rows: This tells us the total number of records that have been returned based on the condition in the WHERE clause
• Actual Number of Batches: If the execution mode for the query is a batch, then it will list the number of batches being executed to fetch the results
• Estimated I/O Cost: This tells us the cost of the input/output operations of the result set
• Estimated Operator Cost: This is not an actual cost but relative information with respect to the other operators in the execution plan
• Estimated CPU Cost: The cost that the CPU will incur in order to process the operation
• Estimated Subtree Cost: The cost of the execution tree that is being currently read from right-to-left and top-to-bottom
• Number of Executions: This tells us about the number of executions that the optimizer can handle in a single batch
• Estimated Number of Executions: This is also similar to the Number of Executions just the estimated value
• Estimated Number of Rows: The number of rows that the optimizer thinks will be returned by the operator
• Estimated Number of Rows to be Read: The number of rows that the optimizer thinks will be read by the operator
• Estimated Row Size: The storage size each row in the operator
• Actual Rebinds: This tells us about how many times the reevaluation of the object must be done in order to process the operator
• Actual Rewinds: This property tells us if there were any changes in the correlated values for the object that is being processed
• Ordered: This property determines if the dataset on which the operation is to be performed in a sorted state or not
• Node ID: It is the automatic assignment of a number in the order in which the operator is called in the execution plan reading from right-to-left and top-to-bottom

Additionally, if you see below, there are these three properties that tell us more about the query and the object on which the plan is generated.

• Predicate: This is the value retrieved from the WHERE clause in the SQL statement
• Object: The table on which the operation is being performed
• Output: The selected columns are being displayed in the result set

You can also right-click any operator or arrow in the plan and select Properties to learn the more detailed explanation of the metrics displayed in the tooltip. 

 

Conclusion

In this article, we have learned what execution plans in SQL Server are and how to generate one. We also walked through various metrics that are being considered in the operators used in the plan. Finally, we have seen how to save an execution plan in SQL Server Management Studio to the file system and use it for future references.