Thursday, November 11, 2004

SQLServer: When will we have a real optimizer ?


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....
Post a Comment