Curated SQL Posts

Bert Wagner gives us some tips for reading execution plans in SQL Server:

Execution plans show the steps SQL Server takes to execute your query. Each icon in the graphical execution plan is known as an operator, and the most common way to read a plan is by starting with the top right most operator and following the arrows to the left.

When you reach a join or concatenation operator where multiple branches merge into one operator, you can proceed to the right-most operator of one of the lower branches and start the process of reading right to left again. In general, this can be summed up as reading a plan right to left, top to bottom.

Right-to-left, top-to-bottom gives you the flow of information and that’s quite important. To understand how the engine works, though, you also need to read left-to-right, as Brad Schulz’s outstanding one-act play demonstrates.

Erik Darling explains that abstraction can be the cause of performance woes in SQL Server:

There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.

Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.

Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.

It’s a logical separation, not a physical one.

In addition, as your query gets more and more complex, the optimizer eventually gives up and gives you what will likely be an ugly version of its implementation because there are too many potential solutions.