May 18, 2012 at 12:48 am
Usually optimizer try to get the best plan in minimal time and thus might not consider all the possible plans as considering all the plans might itself take more time than actually executing the query. This happens when you have a complex query with large number of tables.
Thus the optimizer sometimes do not bring the best plan for your query. However, on dev environment I could have a flag or setting which I could use so that the optimizer will consider all the plans and then return the best. Then I can compare this plan with the plan optimizer is generating when this setting is off. In most of cases though these two plans should be same.
Thus I would like to know, if there isa documented or undocumented parameter or trace flag which could be used to force the optimizer to search for all possbile plans?
e.g. in Sybase they have
set plan opttimeoutlimit 0 --This means consider all the possible plans.
This might be useful during your development where you can compare the performance of the best plan generated by optimizer when it considered all the plan and best plan generated when it considered a sub set of these plans in a given time.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 24, 2012 at 3:30 pm
I believe TF 8757 disables the trivial plan, for your experimentations. Paul White has an excellent Optimizer Deep Dive blog series, worth the read.
Best of Luck,
~Tim
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply