SQL Server 2017 introduces adaptive query processing, which includes, among other enhancements, Batch Mode Adaptive Joins. Batch mode refers to the query processing method used to process many rows in bulk, or batch. Batch mode execution is closely integrated with the columnstore storage format, although they are different technologies, and is best suited for analytical workloads because of its better parallelism and faster performance.
Normally if cardinality estimations are skewed, SQL Server may choose an inadequate physical join based on wrong data, which results in performance degradation. To avoid this, adaptive joins will defer the choice of using a Hash join or Nested Loops join until after the first join input has ...
