I've been using the estimated execution plan feature in SQL 2005 and I see that the "cost %" is sometimes just wacko (e.g. 333% or 25173%).
That makes me wonder just how much I can count on the estimated subtree cost value for a given path in the plan. I relied on this value (subtree cost) in SQL 2000 and would occasionally see the same kinds of wacko "cost%". I was hoping that 2005 would clean this up.
I am aware that the calculations in the plan depend upon a representative database of data. I think I am very experienced using the estimated execution plan tool in SQL 2000. I'm just concerned that, with the new release, that perhaps I shouldn't trust it as much, especially after continuing to see the wierdness with the cost% displayed.
Does anyone have any reliable information about whether the estimated subtree cost is something I should rely upon, even if the cost% is screwy?
Thanks for any insight.
To clarify the original question:
Is the calculation for an estimated subtree cost calculated independently of the displayed "cost %" for an individual, graphically-displayed (logical | physical) operator in the estimated execution plan?
I would guess that it could be independent since the subtree cost should be: a percent of the overall procedure being estimated
whereas the "cost %" for an individual, graphically-displayed (logical | physical) operator is a percent of the estimated cost of the command to be executed.
On the other-hand, the same operator cost value could also be used in both calculations.
Probably only the person who wrote that code might know, so I will do some experimenting to see if the values equate between SQL versions. Also, I am overall more pleased with what I see in the 2005 estimated execution plan presentation so don't judge this as a strong complaint. The new version is more useful.
