THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

The Phases of Query Optimization

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

 

clip_image002

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)

 

clip_image004

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)

 

clip_image006

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.

clip_image008

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.

Published Thursday, August 20, 2009 12:11 AM by Ben Nevarez
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at http://www.benjaminnevarez.com and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement