Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Estimated Total Subtree Cost Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 5:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37, Visits: 318
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.



Post #1532995
Posted Tuesday, January 21, 2014 7:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:54 PM
Points: 2,866, Visits: 2,871
Hi,
The plan's EstimatedTotalSubtreeCost is equal to the EstimatedTotalSubtreeCost for the NodeId with zero value.

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1533038
Posted Tuesday, January 21, 2014 7:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37, Visits: 318
Thank you very much.



Post #1533048
Posted Tuesday, January 21, 2014 7:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 15,451, Visits: 27,840
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1533082
Posted Tuesday, January 21, 2014 8:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37, Visits: 318
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.



Post #1533117
Posted Tuesday, January 21, 2014 8:29 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 15,451, Visits: 27,840
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1533123
Posted Tuesday, January 21, 2014 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37, Visits: 318
Ok, i thought that it was the best way to determine the cost threshold for parallelism because the values are approximate.




Post #1533158
Posted Tuesday, January 21, 2014 10:02 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 15,451, Visits: 27,840
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1533253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse