What is SQL Server Query Optimizer architecture and how does it generate execution plans?

The Query Optimizer is a cost-based engine that evaluates multiple execution strategies and selects the lowest-cost plan.

SQL Server Query Optimizer works in multiple phases: parsing, binding, simplification, and cost-based optimization. It transforms SQL into a logical tree, then explores physical alternatives like joins (nested loops, hash, merge), access methods (scan vs seek), and parallelism. It uses statistics, heuristics, and transformation rules to estimate cost. The optimizer does not evaluate all possible plans due to combinatorial explosion; instead it uses pruning strategies and memoization.