Why is there a wrong estimation on the hash join?

Brycet 2016-02-17 21:05:54

There is a hash match occurring within the plan (tables tMemberLOBAssignments and tLineOfBusiness) however the estimation is immediately skewed at this point,going from 24K rows down to 11K.

Aaron Bertrand 2016-02-19 20:30:39
Honestly, assuming this isn't simply a mis-estimate due to out-of-date statistics, I think you've simply exceeded the threshold of joins where the optimizer has a hard time estimating rows. I would play with materializing some of these joins into #temp tables first (with indexes), then the optimizer might have a more reasonable chance…