Reading and understanding an execution plan graphical presentation

Problem: Reading and understanding an execution plan graphical presentation

Solution:

An execution plan is generally a logical algorithm of query processor chooses to execute the query.

Execution plan is portrayed graphical from a SSMS output window, when you select the include estimated or actual execution plan.

Today we are going to see what all operations are important to look for and how to read the execution plan and understand it step by step.

It is a norm to read the execution plan from right to left and from top to bottom to understand what operations are done insider the query optimizer. When you have multiple batch queries in the same procedure, each batch gets individual execution plan and cost relative to batch.

Execution plans are shown as nodes and each node is represented as an icon and the relations are shown by parent or child based on the split of a query either a trigger or a nested statement or a cursor. 

When a graphical interpretation of execution plan is shown, it shows in a symbol and each symbol represents a logical operation. And each logical operator symbol connects to an arrow to represent the flow of data. An execution plan comprises of icons and arrows to show data flow and tool tip for each operator. There are around 70 icons, just like road signs these also represent each icon for a purpose of logical operation.

For example: 

Individual colors have been associated with each of the three icon types: iterator (logical and physical operators) icons are blue, cursors icons are yellow, and language elements are green.

On each operator if you hover over or put the cursor on the icon, the tool tip shows the below information:

For example :

Physical Operation	The physical operator used, such as Hash Join or Nested Loops. Physical operators displayed in red indicate that the query optimizer has issued a warning, such as missing column statistics or missing join predicates. This can cause the query optimizer to choose a less-efficient query plan than otherwise expected. For more information about column statistics, see Using Statistics to Improve Query Performance.
Logical Operation	The logical operator that matches the physical operator, such as the Inner Join operator. The logical operator is listed after the physical operator at the top of the ToolTip.
Estimated Row Size	The estimated size of the row produced by the operator (bytes).
Estimated I/O Cost	The estimated cost of all I/O activity for the operation. This value should be as low as possible.
Estimated CPU Cost	The estimated cost of all CPU activity for the operation.
Estimated Operator Cost	The cost to the query optimizer for executing this operation. The cost of this operation as a percentage of the total cost of the query is displayed in parentheses. Because the query engine selects the most efficient operation to perform the query or execute the statement, this value should be as low as possible.
Estimated Subtree Cost	The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.
Estimated Number of Rows 1	The number of rows produced by the operator.