Databases Reference
In-Depth Information
And if you run the same query again, this time without the
MAXDOP
hint, you will get a
serial plan with the cost of 5.31282. Since the cost threshold for parallelism is now 6, the
Query Optimizer did not even try to find a parallel plan. Do not forget to change the cost
threshold for parallelism configuration option back to the default value of 5 by running
the following statement:
sp_configure
'cost threshold for parallelism'
,
5
GO
RECONFIGURE
GO
Listing 2-36.
Summary
This chapter described the Execution Engine as a collection of physical operators, which
also defines the choices that are available for the Query Optimizer to build execution
plans with. Some of most commonly used operators of the Execution Engine were
introduced, including their algorithms, relative costs, and the scenarios when the Query
Optimizer is more likely to choose them. In particular, we've looked at operators for data
access, aggregations, joins and parallelism operations.
The concepts of sorting and hashing were also introduced as a mechanism used by
the Execution Engine to match and process data. Data access operations included the
scan of tables and indexes, index seeks and bookmark lookup operations. Aggregation
algorithms like Stream Aggregate and Hash Aggregate were discussed, along with
join algorithms like the Nested Loops Join, Merge Join and Hash Join. An introduction
to parallelism was also presented.
