SQL Server Query Optimizer

What is SQL Server Query Optimizer:-

SQL Server Query Optimizer is a built-in mechanism of SQL Server to optimize the query, submitted by user.

Query Optimizer try to prepare the best optimized plan for execution of SQL Queries. As exceptions are always there so statement that can’t be optimized such as flow-of-control and Data Definition Language commands, are compiled into an internal format so now here we can say there is no optimization for flow-of-control and Data Definition Language. Query Optimizer mainly concern with Data Manipulation Language statements.

Query Optimizer always try to use best optimized plan to execute a query but again sometimes it cannot be true depends on various conditions. There might be various other ways to execute the same query. So in other words we can say that SQL Query Optimizer’s job is determine the best execution plan out of various other ways. Execution plans are a result of the optimizer's calculations.

How SQL Server Query Optimizer works:-

SQL Server Optimizer is a cost based optimizer. It analyzes various ways of execution and estimates the cost of each of them and selects the lowest cost of choices considered.

When user submit a t-SQL query then at very first step to produce the execution plan, Query Optimizer normalize the query, which means that query potentially break down into multiple fine grained query.

After normalizing a query, Optimization step take place. Query Optimizer determines a plan to execute that query. If we are saying Query Optimizer is Cost based optimizer then it means it consider the cost of internal metrics that include estimated query requirements, CPU utilizations, number of required I/Os, availability of data in required tables, availability of indexes on required column and table. Based on the available information, the Query Optimizer considers the various access methods and processing strategies that it could use to resolve a query and chooses the most cost-effective plan.

Simultaneously, Query Optimizer also makes sure that it doesn’t take longer time to choose the execution plan.

Once normalization and optimization get completed, now this the time, when execution plan get compiled. Each command included in it, specifies exactly which table will be affected, which index going to be used (if there is any indexes), impact of constraints, A simple request to insert one row into a table with multiple constraints can result in an execution plan that requires many other tables to be accessed or expressions to be evaluated as well.