Databases Reference
In-Depth Information
query optimization process, which extends both before and after the Query Optimizer
itself. So, if I mention terminology or concepts you've not seen before, don't panic - I'll go
into much more detail and explain everything as we go through the chapter.
Parsing and binding are the first operations performed when a query is submitted to a
SQL Server instance. They produce a tree representation of the query, which is then sent
to the Query Optimizer to perform the optimization process. At the beginning of this
optimization process, this logical tree will be simplified, and the Query Optimizer will
check if the query qualifies for a trivial plan. If it does, then a trivial execution plan is
returned and the optimization process immediately ends. The parsing, binding, simplifi-
cation and trivial plan processes do not depend on the contents of the database (such as
the statistics and the data itself), but only on the database schema and query definition.
These processes also don't use statistics, cost estimation or cost-based decisions, all of
which are only employed during the full optimization process.
If the query does not qualify for a trivial plan, then the Query Optimizer will run the
full optimization process, which is executed in up to three stages, and a plan may be
produced at the end of any of these stages. In addition, to consider all of the information
gathered in the previous phases, like the query definition and database schema, the full
optimization process will also use statistics and cost estimation, and will select the best
execution plan (within the available time) based solely on that plan's cost.
Peeking at the Query Optimizer
In this section I will show you two DMVs which you can use to gain additional insight
into the work being performed by the Query Optimizer. The first one,
sys.dm_exec_
query_optimizer_info
, which is only partially documented, provides information
regarding the optimizations performed on the SQL Server instance. The second one,
sys.dm_exec_query_transformation_stats
, which is also undocumented,
provides information regarding how the Query Optimizer is using the defined
transformation rules. Although both DMVs contain cumulative statistics, recorded
