Query Optimizer and "GoodEnoughPlanFound"

Question

Query Optimizer and "GoodEnoughPlanFound"

amoericke

SSC Eights!

Points: 978
More actions
March 23, 2010 at 2:46 pm

#220305

I'm looking at a query plan, and I noticed in the XML plan that the optimizer settled for a "good enough" plan instead of continueing to search for the "best" plan. Here's a snippet of the XML plan.
...
<StmtSimple StatementText=......
......StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<QueryPlan CachedPlanSize="141" CompileTime="93" CompileCPU="93" CompileMemory="1760">
.....
I understand that if you have a very complicated statement, SQL may settle for a "good enough" plan vs. spending a bunch more time searching for the best possible plan. That makes complete sense to me.
I'm wondering if there are other reason's why the optimizer may abort with a "good enough" plan. For example, might this happen if the system is very busy, like if the CPU's are pegged at 100%??
Thanks,
Adam

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The optimiser's job has never been to find the best plan. It's job is to find a good plan fast. To find the best plan, on anything other than a reasonably simple query, could take significant amounts of time and resources.

It's pointless spending several minutes looking for the best plan for a query that may run in seconds. Generating the best plan is, in general, a very difficult problem (hit google scholar and do some reading around plan optimisation and global search if you're interested)

GoodEnoughPlanFound means that the optimiser found a plan that it considers close enough the the best and it found it within the time allocated for optimisation (there is a time limit). As far as I'm aware, there's no optimisation level 'Best plan'. The ones I've seen are 'GoodEnoughPlanFound' and 'Timeout' (There are probably a couple others too)

Timeout's the one to be concerned about. It means that the optimiser did not find a plan it considered good enough in the time that it was allowed to optimiser and hence returned the best one that it found.

http://scarydba.wordpress.com/2009/07/17/execution-plan-compile-termination/

Edit: removed mention of Trivial. Shouldn't write complex posts after midnight.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amoericke SSC Eights! Points: 978 More actions · Answer 2

Thanks for the reply Gail.

I understand the optimizer's duties are to find a good plan fast, and not necessarily the best plan (although, it may very well have found the best plan). However, the optimizer doesn't always abort prematurely. Notice the XML plan says: StatementOptmEarlyAbortReason="GoodEnoughPlanFound"

But the property "StatementOptmEarlyAbortReason" is not going to show up in every plan you have........I've found it doesn't show up in the majority of the plans I've checked. "GoodEnoughPlanFound" is the reason why the optimizer aborted prematurely. I presume "StatementOptmEarlyAbortReason" doesn't show up in your plan if it doesn't abort prematurely. (I have yet to find a definition of "prematurely" - my only guess is that prematurely means it hasn't checked every possible plan??)

The other reasons are "Timeout" and "MemoryLimitExceeded". See:

http://msdn.microsoft.com/en-us/library/ms189298(SQL.90).aspx

I realize the optimizer may abort with "GoodEnoughPlanFound" because it doesn't make sense to spend another 10 minutes looking for a better plan if it already found a good plan. I realize the optimizer takes into account the cost of the best plan it has so far vs. the amount of compile time it's spent. I'm wondering if anyone knows if the optimizer also takes into account system resources, like high CPU. For example, if the CPU is pegged, is it going to abort quicker? Are the qualifications of a "good plan" lower on a system with 100% vs. a system with 0% CPU?

Also, in my example the "CompileTime" was 93. Does anyone know if that's in miliseconds or microseconds? And any idea what might be considered a long amount of time??

Thanks!!

Adam

BTW: A trivial plan is one in which there are no cost-based decisions to make, so there is only one possible execution plan, or an obvious best plan. For example: select col1 from myTable, where myTable is a heap with no indexes. SQL has to do a table scan.

That's identified in the plan property "StatementOptmLevel". In my case, it's "FULL". (I've only seen "FULL" and "TRIVIAL"....I'm not sure what other levels there are)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Yes I know this thread is 10 months old but I'm wondering if you guys have a way to trace this in prod?

I'm running a trace with xml plan but the data about actual rows and plan optimization is nowhere to be found.

So other than manually rerunning the queries after the fact, is there another way to found this out?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Optimisation level you should be able to pull out of the plan cache, though the xquery necessary won't be pretty. Probably best to dump the cache to a table, index the plan column then search.

What event are you tracing? I believe (without investigation or testing) that the actual plan is statistics xml (not showplan xml which is an estimated plan)

Watch the impact of that trace...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

GilaMonster (11/1/2010)
Optimisation level you should be able to pull out of the plan cache, though the xquery necessary won't be pretty. Probably best to dump the cache to a table, index the plan column then search.
What event are you tracing? I believe (without investigation or testing) that the actual plan is statistics xml (not showplan xml which is an estimated plan)
Watch the impact of that trace...

Plan cache seems the way to go. I couldn't find an event in the perf category with the info I wanted.

Looks like it's time for me to learn xquery! :w00t:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Ninja's_RGR'us (11/1/2010)
Plan cache seems the way to go. I couldn't find an event in the perf category with the info I wanted.

Bear in mind that you won't find actual row counts in the plan cache. The plans in cache have no run-time info, only compile-time.

If it's not critically urgent, I'll have a fiddle later this week (if I remember). I know there is a profiler event that produces the actual exec plan, complete with all the run-time info.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

GilaMonster (11/1/2010)
Ninja's_RGR'us (11/1/2010)
Plan cache seems the way to go. I couldn't find an event in the perf category with the info I wanted.
Bear in mind that you won't find actual row counts in the plan cache. The plans in cache have no run-time info, only compile-time.
If it's not critically urgent, I'll have a fiddle later this week (if I remember). I know there is a profiler event that produces the actual exec plan, complete with all the run-time info.

It's not urgent, but I have not time to play with this today, it'll have to be tommorrow at the soonest on my end.

Thanks for the help.