Is there a way to see what were the Candidate Execution Plans generated by the Query Optimizer?

Question

The Query Optimizer creates more than one possible execution plan. How can I see all the plans generated before the executed one was chosen?

Microsoft says:

The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost.

Please, notice that I'm not talking about Execution Plan Caching and Reuse, I'm talking about candidate execution plans, the ones that were generated but not selected to be executed.

According to Benjamin Nevarez's article The SQL Server Query Optimizer:

The generation of candidate execution plans is performed inside the Query Optimizer using transformation rules, and the use of heuristics limits the number of choices considered in order to keep the optimization time reasonable. Candidate plans are stored in memory during the optimization, in a component called the Memo.

Would it be possible to visualize the candidate plans rendered the way we can Display an Actual Execution Plan?

Answer 1

The optimizer has heuristics to cut down the search time by discarding plans and partial plans before they are fully explored if it believes they are worse than current best solutions it has seen so far in the search. So, it doesn't actually fully generate all of the alternatives. Also, the XML representation is really something that gets generated at the end of the search - the internal representation is somewhat different. Unfortunately, there is no current public feature for you to explore other possible plans for the same query. With a debugger we can look at the structure that holds all of this during the optimization process. It's called the Memo. You can read more about the Cascades framework on which SQL's optimizer is based here

Answer 2

As far as I know, there isn't a way to see the graphical / XML execution plan for candidate plans that were eventually discarded. One approach, if you expected a certain plan shape, is to use hints and then compare costing to see why the final plan was chosen over the hinted plan.

For instance, you might expect that a certain nonclustered index would be used for a portion of a query, but the final plan scans the clustered index instead. Adding a WITH (INDEX (IX_Your_Index)) table hint to the query could you show you how things turn out with that index. There are lots of hints that can be used for this purpose. You can see a lot of options with hinting indexes and joins though.

Setting aside full execution plans, there are ways to see more details about the optimization process, how a query is transformed by the optimizer, and different plan fragments that are considered and chosen or discarded. Paul White's Query Optimizer Deep Dive series goes into quite a bit of detail on this.

If you want to visualize how the "logical tree" of a query is transformed and simplified (as discussed in part 1 of Paul's series), there is a neat free tool for that: SQL Server Query Tree Viewer

Part 3 of the series talks about "intermediate" plans and how to view some information about them via the optimizer's memo data structures.

I know none of that is particularly straightforward, but hopefully it helps in your goal of understanding why some plans might get discarded over others! And regardless, I find all of the above-linked articles quite interesting anyway 😀