Previous part.
In the previous article, you learnt about concepts of execution plan, operators, and their properties. In this article, you will learn about reading an execution plan.
Graphical Execution Plan in SSMS
You can get the graphical execution plan from SSMS toolbar. You can capture Estimated or Actual Execution plan from this toolbar. Details of Estimated or Actual Execution plans are discussed in first part of this article series.
Diagram - 2.1 Getting Execution plan from SSMS
Reading an Execution Plan
You can read an execution plan either from right to left or even left to right. Both are okay. However, most of the people read it from right to left. Now we are going to inspect an execution plan. Let's download and restore the AdventureWorks2022 database in your test SQL Server environment. Select Estimated Execution plan icon from toolbar then execute below query and you will see a query plan like Diagram - 2.2.
SELECT top 5 BusinessEntityID
,LastName
,FirstName
,NameStyle
,Demographics
FROM Person.Person
WHERE LastName LIKE 'lo%';
Query Listing - 2.1
There are 5 operators in below diagram. We are going to discuss them one by one.
Diagram - 2.2 Reading a plan.
First Operator SELECT
In SSMS, select the first operator SELECT in the execution plan and press F4 button or Properties window. You will find a window similar to Diagram - 2.3. Let's have a close to some important properties.
Diagram - 2.3 First operator SELECT
- Cached plan size - this indicates the size of the plan which is 40KB in this case
- CardinalityEstimationModelVersion - this is SQL Server version.
- CompileCPU - number of CPU required to generate the plan. For this plan, 1 CPU is used.
- CompileMemory - 256KB memory was required to produce this plan.
- CompileTime – it required 1 millisecond to compile this plan.
- Estimated Number of Rows for All Executions - this indicates optimizer's calculations about rows that are going to be returned by the all execution.
- Estimated Number of Rows Per Execution - this is optimizer's expectations about rows that are going to be returned.
- Estimated Operator Cost - displays estimated cost of the operator with respect to the execution plan assigned by the optimizer.
- Estimated Subtree Cost - this tells subtree generation cost from the query tree. This is generated by the optimizer.
- Optimization Level - it tells the level of optimization required to produce the plan. It will be either Trivial or Full.
- Reason For Early Termination Of Statement Optimization - common values are:
- FULL - if the plan is produced following the FULL optimization process
- Good Enough Plan Found - this is common value for simple queries. It tells that after at least one of the optimization phases, estimated cost of the cheapest plan was below the threshold.
- Timeout - indicates that after going through its full optimization process, the optimizer didn't find out the cheapest plan. So, it returned the least-cost plan so far it found out.
- Memory Limit Exceeded - tells query is too large and complex.
- RetrievedFromCache - true if it is retrieved from cache otherwise it will be false.
- QueryTimeStats - for actual plan this property shows the execution time.
- QueryHash - optimizer assigns a hash value to every query and stored it along with plan. It helps to identify plans with the same or very similar logic.
- QueryPlanHash - it is hash value for the plan itself. If two plans performs same operations in the order then they have same QueryPlanHash value.
- SET Options - different options are set when a plan is produced. Changing these settings can generate multiple plans for identical queries.
- Statement - actual query which is pushed down for execution.
Top Operator
In earlier sections, you already learnt the meaning of different properties of TOP operator. So, we are not going to explaint Diagram 2.4 again. However, you will see how the TOP operator works. You know every operator has GetNext method (refer to 1st part of this series. The TOP operator calls GetNext() method of Nested Loops. Nested Loops returns an eligible row, as per WHERE clause conditions. TOP operator repeatedly execute this cycle, until it retrieves required 5 rows.
Diagram - 2.4 Property window of TOP operator
Nested Loops Operator
The third operator of the execution plan stated in diagram 2.2 is Nested Loops. This is physical operator and associated logical operator is inner join. It takes data as input through the top side pipeline called outer input and compares it, one row at a time, to another set of data through the bottom side pipe called the inner input depicted in diagram 2.5. The nested loops operation will be efficient if it's outer input size is small and inner input search has low cost.
Diagram - 2.5 Nested Loops
As shown in diagram 2.6, most of the nested loops' properties are same with other operators. One difference is Outer References. If the outer input supplies five data to the inner input, then the inner input will be executed five times for searching rows. In our case, BusinessEntityID column value is supplied to inner input five times. The other value Expr1002 is an artifact of the process of comparison in the Key Lookup operator.
Diagram - 2.6 Nested Loops' Property window
INDEX SEEK
A seek operator retrieves small amount of rows from a large table efficiently. Similar to index of a book, seek operator goes directly to the page of the intended rows and retrieves it. If a seek operation occurs on Clustered Index then it is called Clustered Index Seek. On the other hand, if it occurs on Non-Clustered Index, then it is called Non-Clustered Index seek.
Diagram - 2.7 Non-Clustered Index Seek's Property window
SQL Server chooses INDEX SEEK operation if an index matches with the supplied predicate clause's column and it can returns all required columns requested in the query.
INDEX SEEK operator has an important property which is Seek Predicates. It shows each of the predicates used to determine the rows that will be fetched.
KEY LOOKUP
KEY LOOKUP operator works in tandem with INDEX SEEK operator. If the requested column list cannot be retrieved fully by the INDEX SEEK operator, then the optimizer will use KEY LOOKUP to read remaining columns. As per our query (Listing 2.1),
Diagram - 2.8 KEY LOOKUP Property window
INDEX SEEK operator retrieves, BusinessEntityID, LastName, FirstName columns (refer to Diagram -2.7 output list). For the remaining columns NameStyle and Demographics optimizer used KEY LOOKUP (refer to Diagram -2.8 output list).
Conclusion
Execution plan is a vast subject. I tried to cover some key concepts in this articles. I will cover more on this topic in future.