Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Hash Match Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 11:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
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".


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1567650
Posted Monday, May 5, 2014 12:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
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.

Post #1567666
Posted Monday, May 5, 2014 3:11 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:33 PM
Points: 8,557, Visits: 9,051
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
Post #1567720
Posted Monday, May 5, 2014 3:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1567724
Posted Wednesday, May 14, 2014 8:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:04 PM
Points: 260, Visits: 52
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.
Post #1570888
Posted Wednesday, May 14, 2014 9:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:00 AM
Points: 1,595, Visits: 4,585
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.
Post #1570898
Posted Wednesday, May 14, 2014 6:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:45 AM
Points: 5,916, Visits: 8,169
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1571081
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse