SQL Server Query Optimizer

The SQL Server Query Optimizer is a cost-based optimizer. It analyzes a number of candidate execution plans for a given query, estimates the cost of each of these plans and selects the plan with the lowest cost of the choices considered.

 The core of the SQL Server Database Engine are two major components: the Storage Engine and the Query Processor, also called the Relational Engine.

Parsing and binding – the query is parsed and bound. Assuming the query is valid, the output of this phase is a logical tree, 

Generate possible execution plans – using the logical tree, the Query Optimizer devises a number of possible ways to execute the query 

Cost-assessment of each plan-The plan that the Query Optimizer deems to have the lowest cost of those it’s assessed is selected, and passed along to the Execution Engine;

Query execution, plan caching – the query is executed by the Execution Engine, according to the selected plan. The plan may be stored in memory, in the plan cache.