Hugo Kornelis goes deep into the rabbit hole:
Typically, when I look at an operator that does I/O, I expected to see values in both the Estimated CPU Cost and the Estimated I/O Cost. An Index Seek locates specific data in an index. The CPU has to do some work, but most of the cost of this operator is the actual I/O. So when I look at an execution plan and I see an Index Seek that has an Estimated CPU Cost of 0.0001787 and an Estimated I/O Cost of 0.003125 (almost 20 times as much), I am not surprise. That’s an expected ratio.
Conversely, operators that don’t do any I/O should of course be estimated to have a zero I/O cost. And that is indeed the case. Operators such as Nested Loops, Assert, or Row Count Spool will always have an Estimated I/O Cost of zero.
And then there are some operators where the Estimated I/O Cost may or may not be zero.
Hugo then walks us through a case where the optimizer is promising you a bad time if you run the query.