We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just consider every possible plan and select the best from all alternatives?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
There is, but I don't publicise it because it would be misunderstood and misapplied. In any case, it would not result in an exhaustive search of the plan space because only a limited set of transformations (ones that commonly produce good results) are implemented.
Preventing pruning and discarding would generally simply result in (much) longer compilation times without much of an improvement in final plan quality, if any.
Ultimately, the question is a natural and reasonable one, but it is predicated on a misunderstanding of the goals of the SQL Server query optimizer: it is designed to find good plans for common queries quickly. It is not built on a framework designed for exhaustive search.
If you have a real-world situation that would benefit from a different approach to optimization, you could make the case for it at the Connect website (though I do think it unlikely Microsoft would invest the necessary engineering resources).
There is no knob or trace flag that I know of to coerce this behavior in any way (though Paul White mentions some trace flags here that provide more visibility and allow you to coax some behavior deltas).
Microsoft provides plenty of weapons but this one would almost be guaranteed to be pointing squarely at your own feet 100% of the time. When running a query for the first time, I don't think you want SQL Server to spend an infinite amount of time constructing every single possible variation of a plan to get the desired results. As @ypercube mentions, this could be a very large number of plans, and would kind of defeat the purpose of running the query at all. Your goal in running a query in the first place, presumably, is to return data at some point, right? And "some point" must be within certain thresholds, since certain layers of your application will have various query / command timeouts enforced, and users will only wait so long for a page to load...