Databases Reference
In-Depth Information
table, called the probe input, will be read and compared to the hash table. If rows are
matched they will be returned. On the execution plan, the table at the top will be used as
the build input, and the table at the bottom as the probe input.
Finally, note that a behavior called "role reversal" may appear. If the Query Optimizer is
not able to correctly estimate which of the two inputs is smaller, the build and probe roles
may be reversed at execution time, and this will not be shown on the execution plan.
In summary, the Query Optimizer can choose a Hash Join for large inputs where there
is an equality operator on the join predicate. Since both tables are scanned, the cost of a
Hash Join is the sum of both inputs.
Parallelism
I will finish this discussion of the Execution Engine operations with a quick introduction
to parallelism. SQL Server can introduce parallelism to help some expensive queries to
execute faster by using several processors simultaneously. However, even when a query
may get better performance by using parallel plans, it may still use more resources than a
similar serial plan.
In order for the Query Optimizer to consider parallel plans, the SQL Server installation
must have access to at least two processors or cores, or a hyper-threaded configuration.
In addition, both the
affinity mask
and the
max degree of parallelism
advanced
configuration options must
allow
the use of at least two processors.
The affinity mask configuration option specifies which processors are eligible to run SQL
Server threads, and the default value of 0 means that all the processors can be used. The
max degree of parallelism configuration option is used to limit the number of processors
that can be used in parallel plans, and its default value of 0 similarly allows all available
processors to be used. As you can see if you have the proper hardware, SQL Server allows
parallel plans by default, with no additional configuration.
