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 ...
Batch mode adaptive joins
Get SQL Server 2017 Developer's Guide now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
