Given that the optimizer cannot take al the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off.
I was wondering if this can be overridden so that you can give the optimizer all the time in needs (or a certain amount of milliseconds).
I don't have a need for this (atm) but I can imagine a scenario where a complex query is executed in a tight loop and you want to come up with the optimal plan and cache it before hand. Of course it you have a tight loop you should rewrite the query so it goes away but bear with me :)
This is more a question out of curiosity and also to see if there is sometimes a difference between a short circuited optimization and a full one.
It's not exactly what I was asking but it comes close. It turns out that you can give the optimizer more time with trace flag 2301
The best information I found on this is here:
Use this trace flag with care! But it can be usefull when coming up with better plans