Databases Reference
In-Depth Information
Chapter 1: Introduction to Query
Optimization
The SQL Server Query Optimizer is a
cost-based optimizer
. It analyzes a number of
candidate execution plans for a given query, estimates the cost of each of these plans,
and selects the plan with the lowest cost of the choices considered. Indeed, given that the
Query Optimizer cannot consider every possible plan for every query, it actually has to
find a balance between the optimization time and the quality of the selected plan.
Therefore, it is the SQL Server component that has the biggest impact on the perform-
ance of your databases. After all, selecting the right (or wrong) execution plan could mean
the difference between a query execution time of milliseconds, and one of minutes, or
even hours. Naturally, a better understanding of how the Query Optimizer works can
help both database administrators and developers to write better queries and to provide
the Query Optimizer with the information it needs to produce efficient execution plans.
This topic will demonstrate how you can use your newfound knowledge of the Query
Optimizer's inner workings and, in addition, it will give you the knowledge and tools to
troubleshoot the cases when the Query Optimizer is not giving you a good plan.
This first chapter starts with an overview on how the SQL Server Query Optimizer
works, and introduces the concepts that will be covered in more detail in the rest of the
topic. We'll also cover some of the background and challenges of query optimization
and, since this topic will make extensive use of execution plans, a section on how to
read and understand them is included as well. The chapter closes with a discussion of
join ordering, one of the most complex problems in query optimization, and shows how
joining tables in an efficient order improves the performance of a query but, at the same
time, can exponentially increase the number of execution plans that should be analyzed
by the Query Optimizer.
