What are the components of an Execution Plan?

July 25, 2017 Execution Plans 1135 Views

/_include/blog-images/article-images/what-is-execution-plan-cartoon.jpeg

In order to read and understand an Execution Plan, it is important to know what an Execution Plan is made of.


The core building blocks of an Execution Plan are

  • Operators
  • Arrows
Below is a figure of a sample Execution Plan in which Operators and Arrows are labeled in red.
Sample Execution Plan
Sample Execution Plan
In the sample Execution Plan above, there are three operators called
  • Clustered Index Scan
  • Sort
  • and SELECT.
There are also two arrows connecting the operators together.


Execution Plan Operators

Operators are the smallest unit of work to execute a query. SQL Server breaks the query down into multiple steps and goes through all the steps one by one in order to execute the query.

Operators in an execution plan contain information about what SQL Server has done at that step. Possible values can range from reading a table or index, to merging data from two tables.

Here comes a couple of icons of operators in Execution Plans.

Nested Loops Join Merge Join Hash Match Repartition Streams Parallelism Operator Clustered Index Seek Operator Clustered Index Scan Operator Dynamic Cursor Operator NonClustered Index Spool Operator Spool Operator


Execution Plan Arrows

Since SQL Server breaks the query execution into steps of execution (operators), there should be a mechanism to transition between the steps and pass data from one step to another. Within an execution plan, Arrows are the mechanism to transition the execution from one step to another.

Apart from execution transition, Arrows serve other goals. They are also used to represent the data that each operator has passed to the next operator. In other words, every operator in an execution plan does whatever it is supposed to do, and passes the result data to the next operator; Arrows are used to represent the data flow.

How do you rate this topic?


Further reading:
How to read Execution Plans
Some SQL functions are constants
Blocking vs Non Blocking operators
What does arrow thickness mean in Execution Plans?
Some queries do not appear in Estimated Execution Plans.