How to update statistics

MichaelP 2015-05-26 07:10:48

I have a problem with a clients database. The statistics does not calculate values that are even clos to the actial rews returned.

Any ideas on how to get some more accurate estimations?

All indexes are up to date.

SQLkiwi 2015-05-26 13:19:51
Which problem are you looking to solve here? In spite of the somewhat inaccurate cardinality estimation, the plan looks to be a reasonable one. The sort spill is undesirable in general, but it causes few problems here: the overall execution time is only 69ms and the IO_COMPLETION wait associated with the spill is only 1 ms.

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.