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

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


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