1

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?

2

When you hint a query like that, you force the join order -- you've actually answered your own question.

If you want to test join types and still leave the optimizer with room to explore things, use query options, like this:

SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeDepartmentHistory edh 
ON e.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Shift s 
ON edh.ShiftID = s.ShiftID
OPTION(MERGE JOIN, HASH JOIN);
  • Adding `OPTION(MERGE JOIN, HASH JOIN) made the optimized use hash joins in both places, but I get your point. Wish there was the way to specify build and probe tables as well but that's the way SQL it is then :( – upInCloud Oct 13 '18 at 13:04
  • @upInCloud You can try getting creative with join syntax like this: brentozar.com/archive/2015/05/forcing-join-order-without-hints – Erik Darling Oct 13 '18 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.