FAQ: What is optimizer in SQL Server?

Unlike procedural language SQL Statements does not state the exact steps that database engine should follow to extract the data or manipulate the data. In procedural language the statements are ran as per the sequence they have written. But in SQL , database engine must analyse the statement to determine the most efficient way to extract/manipulate data. This process is called optimization and the component which does this process is called Optimizer.

The input to the optimizer contains , Query , schema (table/indexes definitions) and the statistics available.

The output from the optimizer is query execution plan.



The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.