Query cost in SQL Server Management Studio

Question

How does SSMS calculate the query cost (relative to the batch) value that is displayed when you select to include the actual execution plan?

Mainly, I have a few saved execution plan XML files that I want to compare with each other. It must be calculating that number somehow from the data in the XML files, but I am not seeing how this is done. If I open one of my .sqlplan files that has more than one query in it, it will show those relative to batch values correctly. I just want to do this myself.

FYI: To get this XML, on the Execution Plan tab, right click and select "Show Execution Plan XML..."

Answer 1

Each /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple has an attribute called StatementSubTreeCost.

They are summed across their batches and their share calculated.

You can change them manually in the XML file, save it with SQLPLAN extension and open it with SSMS. You will see that the relative costs will be recalculated.

Answer 2

This is long story to tell, in simply words the cost is the amount of physical and logical read performed by database in bytes and the cost of CPU. For each operation data has to be retrieve if the read is from hard disc the we have physical read, in other case database read it from cache. This is Estimated Operator Cost. For each step you have the cost of it and the cost of sub tree if any. the cost amount is then calculated in percentage for whole execution plan and presented, this is Estimated Subtree Cost ( The total cost to the query optimizer for executing this operation and all operations preceding it in the same subtree.)

For more