What is the SQL Server Query Optimizer?

The Query Optimizer is the cost-based component of SQL Server that evaluates possible execution strategies for a query and selects an execution plan it estimates will have a relatively low cost.

It considers available indexes, statistics, estimated row counts, join order, join algorithms, parallelism, predicates, and many other factors. The optimizer does not exhaustively test every possible plan for complex queries because that would itself be too expensive; it searches the plan space using optimization rules and cost estimates.

A performance problem can therefore arise when the optimizer makes a reasonable decision based on inaccurate estimates or incomplete information.