Yup...
It appears that still in 2000 Server, the optimizer is lacking in working without current statistics. So far it's very inconsistent, but as our databases increase in #rows and transactions, we find that we need to continually update statistics.
In theory, if the data distribution does not change much over time (even if the data grows in size), the optimizer should chose similar plans as previously for traversing objects. It appears in practice, however, that as data grows regardless of distribution, more and more time is spent by the SQLServer optimizer evaluating which plan(s) to chose. This is very observable by watching CPU over time on identical queries.
We have reverted to updating stats every our on our heavily used databases in order to keep this *feature* from occuring.
We keep hoping SQLServer will grow up sooner than later....
1 comment:
One Issue, along this lines, is whether or not to use the autoupdate statistics with SQLServer, and if it makes much difference.
We've noticed with this turned on, by default it's set to reupdate stats when 500 changes have been done to an object (index/table). We plan on testing the two senarios to see how much it slows down statistics when we do bulk loads...
We'll let you know what we find out.
Post a Comment