Reasonable query never finishes

Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a certain
query works against some datasets within a couple tables, but mysteriously
never finishes against other datasets, perhaps some recently loaded ones.
It's mainly just one big table with some smaller auxiliary tables (used in
this query).
I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC INDEXDEFRAG
without effect. However, if I do a DTS "Copy objects and data between SQL
Server databases" of the offending database to a fresh database, the problem
goes away (without touching indexes or anything).
As a further test, I restored a backup of the offending database onto a
different server, tried the query and it never returned, did the DTS copy
objects, queried again and it's fine. Past experience has shown that this
fresh, fixed database works fine for a while but can re-offend after a good
bit more data are loaded. It won't always be feasible to try this silly DTS
trick. The query plans for the query in the two databases are quite different
but I don't know enough to discern what the problem might be from comparing
them.
I've searched around but cannot see a similar issue, but maybe I haven't hit
upon the right search keywords.
Has anyone heard of or encountered such a problem? or other suggestions?
thanks,
EricEric
Run sp_updatestatistics stored procedure on destination server.
(For more details please refer to the BOL)
"Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
certain
> query works against some datasets within a couple tables, but mysteriously
> never finishes against other datasets, perhaps some recently loaded ones.
> It's mainly just one big table with some smaller auxiliary tables (used in
> this query).
> I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
INDEXDEFRAG
> without effect. However, if I do a DTS "Copy objects and data between SQL
> Server databases" of the offending database to a fresh database, the
problem
> goes away (without touching indexes or anything).
> As a further test, I restored a backup of the offending database onto a
> different server, tried the query and it never returned, did the DTS copy
> objects, queried again and it's fine. Past experience has shown that this
> fresh, fixed database works fine for a while but can re-offend after a
good
> bit more data are loaded. It won't always be feasible to try this silly
DTS
> trick. The query plans for the query in the two databases are quite
different
> but I don't know enough to discern what the problem might be from
comparing
> them.
> I've searched around but cannot see a similar issue, but maybe I haven't
hit
> upon the right search keywords.
> Has anyone heard of or encountered such a problem? or other suggestions?
> thanks,
> Eric
>|||Hi Uri, many thanks for the reply. This indeed fixes the problem! Why is it
that I needed to do this when I have "auto update statistics" set to TRUE for
this database?
Is it a good idea to run sp_updatestats periodically regardless of the "auto
update statistics"?
Many thanks!
Eric
"Uri Dimant" wrote:
> Eric
> Run sp_updatestatistics stored procedure on destination server.
> (For more details please refer to the BOL)
>
>
> "Eric Deutsch" <Eric Deutsch@.discussions.microsoft.com> wrote in message
> news:62A9AF7B-DE5E-4B97-B593-00585488FF17@.microsoft.com...
> > Hi everyone, I have a ~2GB database on SQL Server 2000 SP3 in which a
> certain
> > query works against some datasets within a couple tables, but mysteriously
> > never finishes against other datasets, perhaps some recently loaded ones.
> > It's mainly just one big table with some smaller auxiliary tables (used in
> > this query).
> >
> > I've rebuilt the clustered indexes and run DBCC REINDEX and DBCC
> INDEXDEFRAG
> > without effect. However, if I do a DTS "Copy objects and data between SQL
> > Server databases" of the offending database to a fresh database, the
> problem
> > goes away (without touching indexes or anything).
> >
> > As a further test, I restored a backup of the offending database onto a
> > different server, tried the query and it never returned, did the DTS copy
> > objects, queried again and it's fine. Past experience has shown that this
> > fresh, fixed database works fine for a while but can re-offend after a
> good
> > bit more data are loaded. It won't always be feasible to try this silly
> DTS
> > trick. The query plans for the query in the two databases are quite
> different
> > but I don't know enough to discern what the problem might be from
> comparing
> > them.
> >
> > I've searched around but cannot see a similar issue, but maybe I haven't
> hit
> > upon the right search keywords.
> >
> > Has anyone heard of or encountered such a problem? or other suggestions?
> >
> > thanks,
> > Eric
> >
>
>