Control (or indirectly manipulate) the estimates of a hash join

Dan Holmes 2014-06-20 15:13:57

For the first (reading right to left) hash join on the top branch, how can i manipulate the estimates? The nodes to the right of it are really close and this one just isn't and i feel if this one was more accurate the operators for other parts of the plan would change and perhaps positively influence overall performance.

Interesting is that if i add OPTION(HASH JOIN), the statement completes in 20 seconds. That seems to validate my supposition that if the estimates were more accurate the optimizer wouldn't choose the NL joins which is what i was guessing was killing the performance.