Understanding the Query Optimizer - (MS SQL Server) Chapter 13



    


  1. Understanding the Query Optimizer

Query optimization is the process of analyzing individual queries and 
determining the best way to process them. This involves understanding the 
underlying storage structures and the indexes defined on them to determine 
whether a way exists to process the query more efficiently. To achieve this 
end, SQL Server uses a cost-based optimizer. The query optimizer examines 
parsed SQL queries and, based on information about the objects involved, 
outputs a query plan. The query plan is the set of steps to be carried out 
to execute the query.

As a cost-based optimizer, the optimizer's purpose is to determine the query 
plan that will access the data with the least amount of processing time.

To allow the optimizer to do its job properly, you need to understand what 
types of queries can be optimized, and learn techniques to help the optimizer 
choose the best query path. Having a good understanding of the optimizer will 
help you write better queries, choose better indexes, and detect potential 
performance problems.


  2. Optimization Goals

The primary goal of the query optimizer is to find the cheapest access path to 
minimize the total time to process the query. To achieve this goal, the 
optimizer analyses the query and searches for access paths and techniques 
primarily to do the following:

1. Minimize logical page access
2. Minimize physical page access

Disk I/O is the most significant factor in query processing costs. Therefore, 
the fewer physical and logical I/Os performed the faster the query.


  3. Query Optimization Steps

When SQL Server process the query, it performs the following steps:

1. Parse and normalize the query validating syntax and object references.
2. Optimize the query and generate the query plan.
3. Compile the query plan.
4. Execute the query plan and return the results to the user


  4. The optimization Phases (step 2)

Phase I: Query Analysis
1. Find search arguments (SARGs).
2. Find or clauses.
3. Find Joins.

Phase II: Index Selection
4. Choose the best index for SARGs.
5. Choose the best method for ors.
6. Choose the best indexes for any join clauses.
7. Choose the best index to use for each table.

Phase III: Join Order Processing
8. Evaluate join orders
9. Compute costs
10. Evaluate other server options for resolving joins (reformatting strategy).

Phase IV: Plan Selection
If a query is a single table query containing no join clauses, SQL Server skips 
phase III (join Order Processing) and jumps directly to Phase IV 
(Plan Selection).

*** Read Chapter 13 for more details ***

Last update March 7, 1998.