Summary: A detailed look at costing, and more undocumented optimizer fun.
The SQL Server query optimizer generates a number of physical plan alternatives from a logical requirement expressed in T-SQL. If full cost-based optimization is required, a cost is assigned to each iterator in each alternative plan, and the plan with the lowest overall cost is ultimately selected for execution.
Graphical execution plans show the total estimated cost of an iterator, and the I/O and CPU cost components:
In the simplest cases (like that shown above) the total estimated cost of an iterator is a simple sum of the I/O and CPU components. In other circumstances, the final cost may be modified by other considerations – perhaps as a result of a row goal.
The I/O and CPU costs are calculated based on cardinality estimates, data sizes, statistical information, and the type of iterator. Iterators may also be costed differently depending on context. For example, a Merge Join iterator has a zero estimated I/O cost when performing a one-to-many join. When running in many-to-many mode, a worktable in tempdb is required, and an associated I/O cost appears: Read More...