Given that the optimizer cannot take all 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 turns out that you can give the optimizer more time with trace flag 2301. It's not exactly what I was asking but it comes close.
The best information I found on this is in Query Processor Modelling Extensions in SQL Server 2005 SP1 by Ian Jose.
Use this trace flag with care! But it can be useful when coming up with better plans. See also:
- Articles tagged "optimization level" by Grant Fritchey.
- Before you upgrade to SQL Server 2008… by Brent Ozar.
- Tuning options for SQL Server when running in high performance workloads by Microsoft Support.
I was thinking about queries with lots of joins where the solution space for join order explodes exponentially. The heuristics that SQL Server uses are pretty good but I was wondering if the optimizer would propose a different order if it had more time (in the range of seconds or even minutes).