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?
Best Answer
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