Is there any way to prevent the memo structure from being pruned?

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?

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?

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).

StackExchange Database Administrators Q#42721, answer score: 13