How to update statistics
Any ideas on how to get some more accurate estimations?
All indexes are up to date.
The root cause of the mis-estimation is the row goal introduced by the TOP. Essentially, we are asking the optimizer to estimate how many rows it will need to process at each stage of the plan in order to produce 250 rows at the output. This type of estimation is notoriously hard, so mis-estimations are to be expected in many cases.
You could try the documented hint OPTION QUERYTRACEON (4138) to disable the row goal, but the resulting plan will be optimized for all rows, not the first 250, which may not be what you want either.
Another alternative to try is an OPTION (HASH GROUP) hint. This will not change the estimates, but the resulting hash aggregate or hash flow distinct may not spill like the sort does – it is hard to anticipate precisely.
You could also declare a variable, say @T, initialised to 250, use TOP (@T) in the query, and add OPTION (OPTIMIZE FOR (@T = 1000)) or some suitable number to get a plan optimized for a higher row goal.