Post reply

Estimated Total Subtree Cost

  • January 21, 2014 at 5:57 am

    #303135

    Hello.

    A question:

    In an execution plan, the plan has several EstimatedTotalSubtreeCost, one for each NodeId.

    The plan's EstimatedTotalSubtreeCost is the sum of all the EstimatedTotalSubtreeCost or it is only the EstimatedTotalSubtreeCost for the NodeId with zero value?

    Thanks for all.

  • January 21, 2014 at 7:08 am

    #1681880

    Hi,

    The plan's EstimatedTotalSubtreeCost is equal to the EstimatedTotalSubtreeCost for the NodeId with zero value.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • January 21, 2014 at 7:16 am

    #1681890

    Thank you very much.

  • January 21, 2014 at 7:45 am

    #1681922

    Those values accumulate as you go through the plan, each one reflecting the ones before it in the plan in the physical processing order (right to left).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 21, 2014 at 8:24 am

    #1681951

    Thank you very much Grant.

    I saw some plan with several NodeId with zero value.

    Is the total cost of the plan the sum of the EstimatedTotalSubtreeCost relationated with the NodeId that have zero value?

    Thanks.

  • January 21, 2014 at 8:29 am

    #1681957

    Yep. It's cumulative, so a plan with zero value ads zero to the number. Understand though, just because an operator says it has zero cost doesn't mean it actually did zero work. It just means that the optimizer didn't assign it a cost.

    Also remember, these are all fake numbers, just estimates, even on an actual plan. They are meaningful within a given plan, but you can't use them to compare one plan to another really. Instead you need to rely on query execution time and I/O costs.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 21, 2014 at 8:48 am

    #1681987

    Ok, i thought that it was the best way to determine the cost threshold for parallelism because the values are approximate.

  • January 21, 2014 at 10:02 am

    #1682078

    That is the number used to compare to the cost threshold for parallelism. That fact doesn't change where the numbers come from. The optimizer assigns them based on internal processes, not measures of actual performance.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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