I have a 5GB sql2000 database which has recently been upgraded to sp3. I need to access data via views wich involve simple joins of a number of tables. All the joins use clusterd indexes.
I have found that if a small volume of data is imported into a couple of those tables the query optimiser becomes very inefficient when accessing that view. For example, to return 2000 rows takes 25 mins.
If I manually update statistics on all the tables in the view then the the vew returns my 2000 row result set in 10 seconds. however if I then import some more rows the performance problem re-occurs.
Auto stats is on. This seems a serious performance issue. Are there any known optimiser or autostats problems with the sp3 release?