Problems with estimated rows being MUCH lower than actual rows

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

avatar image By niks 1 asked Jun 16, 2016 at 11:03 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

sp.png (64.9 kB)
avatar image By SQLkiwi ♦ 6.6k answered Jun 20, 2016 at 12:47 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x4
x4
x2

asked: Jun 16, 2016 at 11:03 AM

Seen: 39 times

Last Updated: Jun 20, 2016 at 12:47 PM