Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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

2 Answers 2

up vote 11 down vote accepted

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

share|improve this answer
2  
Is there any value in using such a mechanism to compute the "best" plan once (i.e., perhaps for use in a plan guide), or at that point is it best to "know the data" and use things like table/query hints as appropriate? Are there certain classes or characteristics of queries that might benefit from doing a more exhaustive plan search? –  Jon Seigel May 22 '13 at 2:29
    
@JonSeigel My answer is a qualified 'yes' to all of those :) –  Paul White May 23 '13 at 1:25
    
... arghhhhhh ... :) Can you suggest any resources for more information on that? –  Jon Seigel May 23 '13 at 13:52
    
@JonSeigel We could discuss it in chat or by email some time if you like. –  Paul White May 23 '13 at 22:36
    
Sure, that would be great. Could you send me an e-mail to the address in my profile? Thanks. –  Jon Seigel May 24 '13 at 14:37

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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