The Query Optimizer creates more than one possible execution plan. How can I see all the plans generated before the executed one was chosen?
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?