I am observing a situation in AdventureWorks database where the hash join operator is building hash table using a larger input (296 rows) and then probing with a much smaller input (3 rows). I'm not able to understand why this reverse mapping is favoured by SQL server.
Following is the query and plan:
SELECT * FROM HumanResources.Employee e INNER MERGE JOIN HumanResources.EmployeeDepartmentHistory edh ON e.BusinessEntityID = edh.BusinessEntityID INNER HASH JOIN HumanResources.Shift s ON edh.ShiftID = s.ShiftID
The plan is here: https://www.brentozar.com/pastetheplan/?id=Bk_YvD1iX
I wonder if I can specifically instruct SQL to use
HumanResources.Shift for build and the other input to probe?