Databases Reference
In-Depth Information
Chapter 2: The Execution Engine
The Execution Engine is, at its heart, a collection of physical operators that perform the
functions of the query processor, which is to execute your query in an efficient way. Or,
to look at it from the other direction, these operations implemented by the Execution
Engine define the choices available to the Query Optimizer when building execution
plans. The Execution Engine and its operators were briefly introduced in the previous
chapter, and now we'll cover some of the most used operators, their algorithms and their
costs. In this chapter, I will focus on operators related to data access, aggregations, joins,
and parallelism, as these ones are the most commonly used in queries, and also the ones
more used in this topic. Of course, there are many more operators implemented by the
Execution Engine, and you can find a complete list and description on SQL Server 2008
R2 in Books Online. Since the Query Optimizer is the primary focus of this topic, this
chapter will illustrate how it decides between the various choices of operators provided
by the Execution Engine. For example, I will show you how the Query Optimizer reasons
about choosing between a Nested Loops Join or a Hash Join, or between a Stream
Aggregate and a Hash Aggregate operator.
This chapter starts with a look at the data access operations, including the operators
to perform scans, seeks, and bookmark lookups on database structures like heaps and
both clustered and non-clustered indexes. The concepts of sorting and hashing are also
explained, showing how they impact some of the algorithms of both physical joins and
aggregations, which are shown later. The next section focuses on aggregations, and
explains the Stream Aggregate and Hash Aggregate operators in detail. In the same way,
the joins section presents the Nested Loops Join, Merge Join and Hash Join physical
operators. The chapter concludes with an introduction to parallelism and how it can help
to reduce the response time of a query.
