Adaptive joins

Adaptive join is a feature released on the first version of intelligent query processing as part of the originally called adaptive query processing family of features. Additionally, adaptive query processing originally included memory grant feedback and interleaved execution. Although memory grant feedback started with batch mode and was later enhanced to include row mode, adaptive join remains a batch mode-only feature.

The main purpose of an adaptive join is to leave the decision of which physical join to utilize until the execution time when the number of rows needed to be processed is known. Only Nested Loops and Hash Joins are considered on the current release, and the choice is deferred until after the first input has been scanned.

This feature will be selected by the query optimizer only if the best choice for a join could be either a Nested Loops join or a hash join. If the best choice is a Merge Join operator, an adaptive join will not be considered at...