Is it possible to give the optimizer more or all time it needs?

Question

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

Answer 1

Next to trace flag 2301, there is 8780 which really does make the optimizer 'work harder' since it just gives it more time (not unlimited, as described in detail here (russian) and less detailed here) to do its thing.

Detailed description in english of the original author of the russian article. which includes the author's own warning:

it is not recommended ever use it in production.

Combining the two and applying them (very selectively via query hint OPTION (QUERYTRACEON 2301, QUERYTRACEON 8780) to a query of 4-level nested inline TVFs (where only the one at the bottom would do any real work and the upper levels would correlate results via EXISTS subqueries) resulted in a nice MERGE JOIN and several LAZY SPOOLs that cut down execution time by half.

Answer 2

No, you can't.

You can make your queries "optimiser-friendly" by understanding how it works (complex beast, not necessary to know it inside out). I'd suggest that if you something so time critical, then fix the query rather than changing how SQL Server operates.

For example, you'd want to know when a query starts scaling less efficiently than O(n) as data volume + data distribution changes: giving more time to the optimiser adds no value here.