8

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?

  • 3
    It's not clear what you want to prevent. Do you want to somehow force the optimizer to calculate a cost for all possible plans (which can be on trillions of zillions, even for a simple query with 10 joins)? – ypercubeᵀᴹ May 21 '13 at 15:51
  • yes, though it seems not possible. I'd like to force the optimizer to consider all possible plans, calculate their costs, and then choose the cheapest. – zli89 May 21 '13 at 15:59
  • 4
    And why should it do that? If it has calculated say a plan with cost=7, and then finds that any plan that includes a hash join b will cost 10 or more, why calculate all those million plans and not prune them? (not that the optimizer works in such a simplistic way) – ypercubeᵀᴹ May 21 '13 at 16:04
12

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

9

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.