Tell me more ×
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.
up vote 5 down vote favorite

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.

Edit 1

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:
http://blogs.msdn.com/b/ianjo/archive/2006/04/24/582219.aspx

Use this trace flag with care! But it can be usefull when coming up with better plans

also
http://www.scarydba.com/tag/optimization-level/

http://www.brentozar.com/archive/2008/10/before-you-upgrade-to-sql-server-2008/

http://support.microsoft.com/kb/920093

share|improve this question
This would be a nice capability because optimization time can be amortized over thousands of executions. It usually doesn't matter much. – usr May 15 '12 at 11:43
flagged for dba.se. You'll get better eyeballs on this Q there – gbn May 15 '12 at 11:53
@gbn What is dba.se ? – buckley May 15 '12 at 12:14

migrated from stackoverflow.com May 15 '12 at 16:03

2 Answers

up vote 1 down vote accepted

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.

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.

share|improve this answer
up vote 4 down vote

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 exponentially (> O(n)) as data volume + data distribution changes: giving more time to the optimiser adds no value here.

share|improve this answer
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). I will wait some more before accepting your answer, maybe someone knows about an unsupported switch. – buckley May 15 '12 at 12:19
I updated my question with a trace flag I found. I think that's also a good answer. – buckley May 15 '12 at 13:06
Also, the optimizer disables sophisticated rules when it thinks they add no value. But it might be wrong. – usr May 15 '12 at 15:19

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.