查询优化的具体步骤（The Phases of Query Optimization）

The Phases of Query Optimization 

from: http://sqlblog.com/blogs/ben_nevarez/archive/2009/08/20/the-phases-of-query-optimization.aspx 

 

One of the most interesting tools that you can use to gain additional knowledge on how the Query Optimizer works is the sys.dm_exec_query_optimizer_info DMV. This view contains cumulative query optimizer statistics since the SQL Server instance was started and it can also be used to get optimization information for a specific query or workload.

 

In this post I will show you how you can use this DMV to get information regarding the phases of query optimization used by SQL Server. Unfortunately, all the optimizer events shown in this section are undocumented and marked as “Internal only” in Books Online.

 

As shown in the SQL Server documentation, this view has three fields: counter, which is the name of the optimizer event; occurrence, which is the number of occurrences of the optimization event for this counter; and value, which is the average property value per event occurrence.

 

To obtain the optimization information for a specific query you can take snapshots of this DMV before and after the query is executed and compare them to find the events that have changed. Keep in mind that if you execute a query that it is already on the plan cache, it may not cause a new optimization and may not be shown in this view. This DMV may also capture some other optimization events happening on the SQL Server instance at the same time that your query is executing.

 

To start, run the following code to create three tables

 

create table table1 (a int) create table table2 (a int) create table table3 (a int)

Trivial Plan

 

The SQL Server query optimizer is a cost-based optimizer but this cost-based optimization has an expensive startup cost. To avoid this cost for the simplest queries where cost-based optimization is not needed, SQL Server uses the trivial plan optimization. The next example shows a query that takes benefit of a trivial plan. The DMV output shows one trivial plan optimization of a query accessing one table with a maximum DOP of 1.

 

select * from table1

Of course, you can also find out if a trivial plan was used during optimization by looking at the properties of the graphical plan, shown as Optimization Level TRIVIAL, or by looking at the XML plan, shown as StatementOptmLevel="TRIVIAL". If a query does not qualify for a trivial plan both of these properties will be shown as FULL instead.

 

If a trivial plan is not found, the Query Optimizer will start the cost-based optimization.

 

Many SQL Server users believe that it is the job of the Query Optimizer to search for all the possible plans for a query and to finally select the most efficient one. Because some queries may have a huge number of possible query plans, this may not be possible or may take too long to complete. Instead, the Query Optimizer uses three search phases and the optimization process can finish if a good enough plan is found at the end of any of these phases. If at the end of a phase the best plan is still very expensive the Query Optimizer will run the next phase. These phases are shown as search 0, search 1 and search 2 on the sys.dm_exec_query_optimizer_info DMV.

 

Phase 0 – Transaction Processing

 

The first phase is called the transaction processing phase and it is used for small queries typically found on transaction processing systems. The following example shows an optimization on phase 0, using 233 tasks for a query accessing 3 tables.

 

select * from table1 join table2 on (table1.a = table2.a) join table3 on (table1.a = table3.a)

Phase 1 – Quick Plan

 

The next phase is called Quick Plan and it is appropriate for more complex queries. This phase may also consider parallelism. Note that, as shown in the next example, not every query qualifies for phase 0, so depending on the number of tables some queries may start directly on phase 1.

 

select * from table1 join table2 on (table1.a = table2.a)

Phase 2 – Full Optimization

 

The last phase, called Full Optimization, is used for complex to very complex queries. This phase applies more sophisticated transformations than the previous ones.

 

Timeout

 

The DMV can also show a timeout event. When a timeout is found, the Query Optimizer stops the optimization process and returns the least expensive plan it has found so far. This timeout event is also shown on the properties of a graphical plan as Reason For Early Termination of Statement Optimization or on an XML plan as StatementOptmEarlyAbortReason.

 

For example, the following output shows a timeout in phase 0, after 1,616 tasks on a query joining 12 tables.

To keep this post simple I have provided very small queries only, but you can experiment yourself with more complex and interesting queries. By the way, in Chapter 2 of Inside SQL Server 2005: T-SQL Querying, Lubor Kollar provides an excellent script to automatically extract the optimization information for a specific query from the sys.dm_exec_query_optimizer_info DMV.