Problems with estimated rows being MUCH lower than actual rows

niks 2016-06-16 11:03:40

Having some problems with the number of estimated rows being much lower than the actual rows. Data spills to tempdb. Statistics has been updated but the problem remains. Thankful for any hints how to begin investiation.

/Niks

SQLkiwi 2016-06-20 12:47:55
There's very little to say based on an old-style anonymized plan alone (you should update your version of Plan Explorer) but the mis-estimation starts here:

alt text

The lower input to the hash join is not a problem because it is just a scan of a table with no predicate. The upper input is much more complex, so it is hard for the optimizer to assess accurately how many rows will join.

The construction at and below the Merge Interval indicates a join on mismatched types or a dynamic matching condition, perhaps involving LIKE and a variable. You should avoid those if you can.

Materializing the subtree on the hash join build input into a temporary table is another option. That would hold only 1461 rows, but provide much better statistical information to the optimizer.