How do I know where to use Hash hint join explicitly? How does sometimes query optimizer gets tricked and applies a hint which sometimes affects performance?
Asked
Active
Viewed 311 times
0
-
2"If you have to ask the price, you can't afford it" – Remus Rusanu May 15 '11 at 20:09
1 Answers
1
Personally, I've never used a join hint in. Ever.
They are only good for the data and statistics you have at that time. Where I have seen them used is to try and mask poor design or poor indexing or a poor query construct. A hash join isn't bad in itself
Can you update to show the query where you think you need a join hint please? And we can suggest other solutions...
gbn
- 422,506
- 82
- 585
- 676
-
1I have noticed when doing projects using CDC statistics can become out of sync quickly and can be the difference between a nested loop join and a hash join. In these cases the hash join, merge join or nested loop join is still ideal but SQL server doesn't know because stats are off. Sometimes updating stats + the query is slower than the few instances where the hash join/ merge join/ nested loop join won't make sense especially when it is not doing a read ahead because it thinks the data set it is going to be extremely small because stats are slightly off. – JStead May 15 '11 at 21:55
-
I've see huge performance gains when using hash. I only use it as a last resort when a query I know should run fast takes forever to finish. My envrionment is different though. I work in a 3rd party database that has no foreign-key relationships on any of their hundreds of tables. I'm inclined to think SQL Server uses these relationships to come up with an optimal plan. Is that a correct assumption? – MikeTeeVee Dec 08 '11 at 13:08
-
@MikeTeeVee: er... not sure TBH. I'm sure I've read something but will try to find an authoritative source – gbn Dec 08 '11 at 19:54