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?

1 Answer 1

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);
2
  • 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, 2018 at 13:04
  • @upInCloud You can try getting creative with join syntax like this: brentozar.com/archive/2015/05/forcing-join-order-without-hints Oct 13, 2018 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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