How to read Execution Plans

July 25, 2017 Execution Plans 628 Views

/_include/blog-images/article-images/reading-execution-plan.png

Reading Execution Plans reveals a lot of details about the way SQL Server executed the query; and is the first step to optimizing query performance.


Graphical Execution Plans are read from right to left, and top to bottom. So, in the sample Execution Plan below, the order of the execution is:

  1. Clustered Index Scan happens
  2. Data gets passed to the Sort operator
  3. Sort happes
  4. Data gets passed to the SELECT operator
  5. SELECT happens
Sample Execution Plan
Sample Execution Plan


Reading Execution Plan properties

Every operator and arrow in Execution Plans also provides very detailed information about the action that happened. Details of every step can be found using the Operator's properties and details about every transition between steps can be found using the Arrow's properties

Reading Operator/Arrow Properties
There are two ways to access the properties of an operator or an arrow in an Execution Plan: Tooltip, or the Properties Window.

By hovering the mouse over every operator or arrow, a giant tooltip appears that contains important properties of that operator/arrow. The tooltip does not contain all the properties, but just the important ones. Below is the Tooltip for a sample operator.

Sample Operator Tooltip
Sample operator properties on Tooltip

By right-clicking on an operator or arrow and choosing Properties, the Properties Window will display all the details about that operator/arrow. Below is the Properties Window for a sample opeator

Sample Operator Properties
Sample Operator properties in Properties Window

How do you rate this topic?


Further reading:
Actual vs Estimated Execution Plans
What is an Execution Plan ?
Some SQL functions are constants
What does arrow thickness mean in Execution Plans?
Actual Execution Plan hides some queries!!!