Today I want to talk about a very “simple” topic: how to read an execution plan. At first this seems to be quite an easy question, but when you look into the details of the answer it can be surprising. There are 2 different ways to read an execution plan – from right to left, and from left to right. Let’s have a more detailed look at which alternative is the right one…

Right to Left?

When you start working with execution plans in SQL Server, you always hear the recommendation that you should read your execution plans from right to left. But why? We read books, magazines, newspapers from left to right, and now we should read an execution plan from right to left? This doesn’t make much sense at first. But when you know what happens during query execution, the right to left approach makes more sense.

Internally SQL Server uses a so-called Iterator Model for query execution. Every operator inside an execution plan is internally a C++ class that passes rows to the next upfront operator in the execution plan. Therefore the resulting data flow inside an execution plan is always from right to left, as you can see in the following picture.

Therefore it simply makes sense to read an execution plan from right to left, because you just follow the data flow within the execution plan. I always tell people to concentrate first on the right hand side of the execution plan, because that’s the area where all the trouble starts: you access your data through physical index data structures. If you see there that you are performing scan operations on quite large tables, you immediately know that you have to work on your indexing strategy.

Left to Right?

Another approach is to read the execution plan from left to right. But as you know from the previous section,that doesn’t really make sense, because the data flow within the execution plan is from right to left. Or can it still make sense to read from left to right? It depends…

Let’s have a more detailed look at why it depends. As I have mentioned previously internally SQL Server uses an Iterator Model for query execution. Every operator requests a row from the right operator, and the right operator returns that row back to the left. Therefore the execution plan is still physically executed from left to right. Let’s have a look at the following picture.

In this picture the first operator requests a row from the second one, the second one requests a row from the third one, and finally the third operator returns the row to the second one, and the second operator returns the row to the first operator. Physically the execution plan was executed from left to right, and you have a logical data flow from right to left. By the way, there can also even be parts in an execution plan, which are never ever executed. And the execution plan can also short-circuit a Scan operator in the execution plan, therefore a Scan isn’t really always a complete scan.

Summary

If you ask someone how to read an execution plan, there is no wrong answer. You can read it from right to left, or from left to right. Both approaches have their advantages/disadvantages. Normally I recommend reading an execution plan from right to left, because then you follow the logical data flow and it helps you to see in which areas you have performance problems because of large row counts.

But for some specific cases it can also make more sense to read the execution plan from left to right when you want to follow the physical execution of the query. So it really depends 😉

Thanks for your time,

-Klaus