Estimated Operator Cost Calculation

Everywhere I have read says that the Estimated Operator Cost is the sum of the Estimated CPU Cost and the and the Estimated I/O Cost. However, in many operators I see, this is not the case. Here is an example:

SELECT Column2
INTO   Object1
FROM   Object2
WHERE  Column3 >= Variable2
       AND Column3 <= Variable1
       AND ( Column4 = Variable5
              OR Variable5 = ? )

EstimateIO="0.01"
EstimateCPU="0.000246492"

Sum: 0.010246492

Yet SSMS shows this 0.073823 as the Estimated Operator Cost. I am at a complete loss as to how this is getting calculated. Below is the execution plan xml (anonymized). Node Id 0 is the node in question.

```

The only way to properly answer that question is to fire up the debugger and see what choices were made by the optimizer along the way. The costs are not only IO and CPU. There are additional costs associated with a given operator that are reflected in the total cost, but are not reflected in the IO and CPU cost estimates. You can read more about some of the additional costs in this excellent article by Paul White.

I don't have a precise answer to your question (I've not doubt, Paul would). However, I'm willing to take a guess. What you're seeing is added overhead for the operation as determined by the optimizer above and beyond what it is displaying as the overhead for the IO and CPU as determined by the estimated rows, etc.. I believe it's a calculation based on what would necessary in terms of IO to create the table and store the 246.492 rows 9b worth of data on each that is calculated as being in the INSERT statement. 246.492 9 / 1024 = 2.1664 is less than an 8k page. However, we have to create at least a page, so when you calculate 8 * the cost of .01, it puts us just a little above the estimated .073832. That's my guess, and it is a guess. However, I do know that there is overhead in the costs that isn't displayed by the strict addition of CPU + IO in all cases.

StackExchange Database Administrators Q#143548, answer score: 3