none
Reg:Estimated Operator Cost..

    Question

  • Hi Team,

    can i know what is the difference between Estemated IO cost,Estimated CPU cost,Estimated Operator cost and Estimated SubTree Cost in sql server ?

    Thanks in Advance.

    Wednesday, July 11, 2018 11:35 AM

All replies

  • Hi Team,

    i have attched the two pics.in those two pics i have cost parameter. can i know what is the difference between these  costs displayed in two pics?

    Thanks.

    Wednesday, July 11, 2018 9:45 AM
  • The first pic shows cost involved in table scan, actually optimizer spent almost all its resources in doing table scan as compared to other operators.

    For 2nd pic it is showing estimated I/O cost,w which means what would be cost incurred (relative terms) in performing I/O to get the page into memory.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, July 11, 2018 10:35 AM
    Moderator
  • Cost is summarized calculation of the CPU and IO resources... you can see below Estimated CPU cost 

    In your case SQL Server optimizer  estimated  that on table scan it spends more IO and less CPU 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 11, 2018 10:59 AM
    Answerer
  • IO -how much IO  resources SQL Server spends  to execute this operator

    CPU--how much CPU resources SQL Server spends  to execute this operator

    Actually in order  to create better execution plan SQL Server optimizer needs to calculate  CPU and IO  ... Means it has to choose between SCAN and SEEK  what operation would cheaper in terms of IO cost + CPU cost 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 11, 2018 11:59 AM
    Answerer
  • Hi Uri,

    Thanq for your reply.

    can i know what is the terms Estimated Operator cost and Estimated SubTree Cost  meant for?

    Thanks.

    Wednesday, July 11, 2018 12:10 PM
  • see:

    http://www.qdpma.com/CBO/CBO04_TableScan.html

    http://www.qdpma.com/CBO/CBO02_IndexSeek.html

    Key point: the cost model employed by the SQL Server query optimizer is only a model for assessing whether an index seek + key lookup is more or less expensive than a scan, when to use the 3 join categories (loop, hash, merge).

    the model is very old, probably more representative of the 1980's or early 1990's when systems had very little memory. Also, the cost of operators such as Compute Scalar is completely irrelevant because these would not impact the choice of execution plans,


    jchang


    • Edited by jchang61 Wednesday, July 11, 2018 12:48 PM
    Wednesday, July 11, 2018 12:12 PM
  • Estimated Operator cost 

    http://blogs.msdn.com/sqlqueryprocessing/archive/2006/10/11/What-is-this-cost.aspx

    The subtree cost represents the estimated cost of a plan. It can be useful when investigating why the query optimizer chose one plan over another


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 11, 2018 12:22 PM
    Answerer
  • Hi Ramesh Reddy Mallidi,

     

    Estimated Operator cost: The cost to the query optimizer for executing this operation. In parentheses, there is the cost of this operation as a percentage of the total cost of the query.

     

    Estimated Subtree Cost: The estimated cost of a plan. It’s a cumulative cost associated with the whole subtree up to the node.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 12, 2018 10:20 AM