How to analyze the graphical components of the SQL execution plan (2023)

In previous articles in this seriesSQL Server Execution Plans OverviewmiSQL Server Execution Plan TypesWe review the different phases followed by the submitted SQL Server query and how it was processed by the SQL Server relational engine that generates the execution plan and the SQL Server storage engine that performs the operation to retrieve or modify the requested data. Additionally, we detail the different types and formats of SQL Server execution plans that can be used to troubleshoot query performance. In this article, we discuss the components of the graphical query plan and how to analyze them.

The SQL execution plan is often used to tune query performance by monitoring and troubleshooting how the query is executed internally. To take advantage of the SQL execution plan, we need to understand the various information provided by the SQL execution plan, whether it is clear on the surface or hidden between the operators. Remember that you must have permission from SHOWPLAN to create the show plan. Members of SYSADMIN, DBCREATOR, or DB_OWNER can generate execution plans without requiring additional privileges.

Let's start with a simple SQL execution plan. The following SQL execution plan is an actual plan generated after executing a simple SELECT statement. You may be wondering how I can tell if it is an actual execution plan or an estimated execution plan. The answer is the Results tab that can be seen in the image above, indicating that the query was executed before the plan was generated. If that part is removed from the plan, nothing at a glance can tell us what kind of graphical execution plan it is.

The top of the SQL execution plan shows the cost of this query against the total cost of the stack if the execution plan is generated for multiple queries in the same session, as shown below:

The submitted query statement is also displayed at the top of the SQL execution plan. For long queries, display as much of the query statement as will fit in the window. You can get the full query statement in a new window by clicking on the dotted square as shown below:

In some cases, the SQL Server query optimizer will suggest an index, the missing index, that it believes will improve query performance by a certain percentage, shown in green as a recommendation, as shown below:

With the ability to view the CREATE INDEX statement by clicking on the "Missing Index Details" option as shown below:

At the moment, we have a good understanding of the top of the graphical execution plan above the solid line. Let's start by looking at the information presented below this line. To correctly read the SQL execution plan, you must first know that the execution flow startsfrom right to left and from top to bottom, with the last operator on the left, which is the SELECT operator in most queries, contains the final result of the query. We'll take a closer look at all of the operators you might see in execution plans later in this series.

The arrows between the operators represent the direction and amount of data passed between these operators in the SQL execution plan. The thickness of the arrow indicates the amount of data that is transferred between carriers. From the following figure you can deduce that the amount of data passed in the second arrow is greater than the amount of data passed in the first arrow:

You can get the amount of data passed by this arrow from the arrow tooltip when hovering over this arrow as shown below:

Not only can the number of lines passed by the arrow be derived from the arrow, but other useful information is also hidden behind that arrow. This information includes the size of each row passed and the total size of the data passed by the arrow. The thickness of the arrow can also indicate a performance issue. For example, if the execution plan shows bold arrows, the number of rows passed by the arrows is large at the start of the plan, and the number of rows passed by the last arrow to the SELECT statement and returned by the query is small. a scan of a table or index that needs to be repaired runs incorrectly.

Below each operator is a number as a percentage that represents the SQL Server query optimizer's estimate of the cost of that operator in relation to the total cost of the query. In our example, you can see that all the estimated costs are related to the Clustered Index Lookup operator, as shown below:

By using the ApexSQL plan, a tool forSQL execution planVisualization and Analysis, the important numbers that represent the main statistics of each operator and the arrow are displayed without the need to click on the component as shown below:

Can all this be derived from the execution plan? The answer is certainly not. Additional information is hidden behind each operator in the execution plan. Simply hover your mouse over a carrier in the plan and that carrier's tooltip will appear, containing statistical information and a full description of the carrier. For example, hovering over the Clustered Index Search operator displays a tooltip for that operator.

The displayed popup contains:

• The definition of the selected operator is as follows:

  

• Statistical information about the operator, such as B. I/O and CPU costs. These numbers do not represent the actual amount of resources consumed by this operator, but do represent an estimated cost allocated by the SQL Server query optimizer at run time. This information helps to understand what happens internally during the execution of the query, as shown in the following figure:

  

Typically, we focus on the cost of the operator subtree that represents the execution tree that the SQL Server engine has examined so far, from right to left and top to bottom. In complex plans made up of a large number of operators, the total cost of the plan can be cumulatively derived from the last operation, which in our case is the SELECT operator.

Also notice the sorted boolean value in the operator tooltip. This value indicates whether the data being processed by the current operator is ordered or not, which explains why additional processing is required at this step of the query. The node ID value represents the numbered order of the operator assigned by the SQL Server query optimizer.

The Actual Execution Plan operator displays actual and estimated statistics. On the other hand, the estimated execution plan shows only estimated statistics. In most cases, the actual and estimated values ​​are the same. If not, the statistics for that database table or index are out of date and need to be updated as shown below:

• Information about the index used, the list of columns retrieved, and the condition used to filter the data in this operator as follows:

  

The operator property sheet provides us with additional information that does not appear in the operator tooltip. Right-clicking on any operator and selecting Properties Panel, or simply clicking the operator and pressing F4, displays the properties panel for that operator. The window that appears shows us other useful information that represents how SQL Server behaves when processing data inside this operator. For most, but not all, properties, clicking the property displays a description of that property, making it easier to understand. Properties with a plus sign (+) next to them are expandable properties that can display additional information, as shown below:

By now we have a good understanding of the components of the SQL execution plan and how to read them from right to left and top to bottom and that we should focus on the operator with the highest percentage, thickest arrow, largest subtree cost , unordered data, SCAN operators...etc.

In the next article, we'll start introducing the first set of execution plan operators. Stay tuned!

Index

frequent questions

Should we create an index suggested by the SQL execution plan?

Automatically? No. First you have to look at the impact.
Also, you should test it and make sure that it improves the performance of your query.

Should we focus on the thickness of the arrow in all cases?

You need to pay attention to the fact that the arrow is thick at first, and then it becomes thinner. For example, returning 2 rows after scanning thousands of records.

Can the result of the estimated SQL execution plan be trusted?

It depends on the statistics. When updated, the results should be the same. You need the estimated SQL execution plan in case the query takes a long time to execute and you need to troubleshoot.