Hash Match

Question

Hash Match

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125562 More actions · Answer 1

Jeff Moden (5/5/2014)
Steve Jones - SSC Editor (5/5/2014)
Jeff Moden (5/3/2014)
I think a much better question would be, "What determines what will be the first (top) table (according to books online) in a hash match join"? 😉
There's no reliable way to determine this. It should be the smaller input most of the time, but sometimes the optimizer doesn't get that one and that usually means the DBA's phone rings with a complaint.
Yep... that's my whole point. It would be wonderful to see a QOTD where the correct answer is, "It Depends". 😛

Sorry - you were wrong
Correct answer: The first input
Explanation: In a hash match join, the first input is used to build the hash table and the second input is used to probe the hash table. Size does not matter for this operator.

But technical details of the algorithm and experimentation seem to suggest that size does matter when it comes to inserting a hash table. It favors smaller inputs.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Jeff Moden (5/5/2014)
Steve Jones - SSC Editor (5/5/2014)
Jeff Moden (5/3/2014)
I think a much better question would be, "What determines what will be the first (top) table (according to books online) in a hash match join"? 😉
There's no reliable way to determine this. It should be the smaller input most of the time, but sometimes the optimizer doesn't get that one and that usually means the DBA's phone rings with a complaint.
Yep... that's my whole point. It would be wonderful to see a QOTD where the correct answer is, "It Depends". 😛

Well, no, the correct answer in this case would be "The table which the optimizer thinks is smallest", because that is always the first input accessed by the hash join operator (even when the optimizer gets it wrong and this input is very much bigger than the other input so that it doesn't end up forming the build table).

Or did you mean "It depends what the optimizer thinks is best"?

Tom

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

TomThomson (5/5/2014)
Or did you mean "It depends what the optimizer thinks is best"?

That would be the the one because I've also seen it where the optimizer reused a plan with hashes that it probably shouldn't have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robert.diley SSC Eights! Points: 860 More actions · Answer 4

I do have a text in front of me: Fritchey, Grant (2012) SQL Server Execution Plans, 2nd ed.

From p. 88:

"we can discuss the Hash Match join operator.

It occurs when SQL Server has to join two large data sets, and decides to do so by first

hashing the rows from the smaller of the two data sets, and inserting them into a hash

table. It then processes the larger data set, one row at a time, against the hash table,

looking for matches, indicating the rows to be joined."

I am inclined to trust this source.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 5

robert.diley (5/14/2014)
I do have a text in front of me: Fritchey, Grant (2012) SQL Server Execution Plans, 2nd ed.
From p. 88:
"we can discuss the Hash Match join operator.
It occurs when SQL Server has to join two large data sets, and decides to do so by first
hashing the rows from the smaller of the two data sets, and inserting them into a hash
table. It then processes the larger data set, one row at a time, against the hash table,
looking for matches, indicating the rows to be joined."
I am inclined to trust this source.

Grant provides the best answer, but it makes no difference who says what. There are many occasions where "it depends" and technical documentation provides a simplified answer that at best is only generally true or an answer that's intended to be true according to specifications but isn't actually. Which input SQL Server will choose for the hash table can be easily confirmed by setting up a couple of sample tables, one big and one small, then examine the execution plans for multiple selects with different join configurations. My eyes are my most trusted source.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

robert.diley (5/14/2014)
I do have a text in front of me: Fritchey, Grant (2012) SQL Server Execution Plans, 2nd ed.
From p. 88:
"we can discuss the Hash Match join operator.
It occurs when SQL Server has to join two large data sets, and decides to do so by first
hashing the rows from the smaller of the two data sets, and inserting them into a hash
table. It then processes the larger data set, one row at a time, against the hash table,
looking for matches, indicating the rows to be joined."
I am inclined to trust this source.

It's defintely a good source, but please check the detailed post I made earlier in this discussion. There is a difference between what the optimizer attempts (which is to make sure that the smaller table is used as the top, build input) and the reality (where the first, topmost input is used for the build phase, even when the optimizer got it wrong).

BTW, I have been asked to be the technical editor for the next edition of Grant's book; I hope that makes me an equally trustworthy source.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/