Batch mode adaptive joins

Logical joins are implemented through three physical join operators in SQL Server: Nested Loop, Hash Match, and Merge Join. For Merge Join, inputs must be sorted in the same manner, thus in most cases, you can see either a Nested Loop or Hash Match Join operator. The decision of which one to use is made during compilation. A significant part of the decision is estimating how many records will be processed in both join inputs. If the estimation is wrong, you can expect the wrong operator. Inappropriate join operators in the execution plan can lead to serious performance issues. Another case where the choice between these two operators could be a problem is stored procedures with parameter sniffing. In this case, the ...

Get SQL Server 2017 Developer's Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.