Re: Reasonable query never finishes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Eric Deutsch (EricDeutsch_at_discussions.microsoft.com)
Date: 11/17/04 

Date: Wed, 17 Nov 2004 10:19:01 -0800

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
> >
>
>
>

Relevant Pages

  • Re: Slow server
    ... update statistics if auto create and auto update statistics are turned off. ... Run your query again and examine the execution plan using the 'graphical ...
    (microsoft.public.sqlserver.server)
  • Re: SEQUENTIAL SCANS
    ... table A has another individual index on column t. ... Above query correctly takes INDEX PATH. ... you did run update statistics low for table B, ...
    (comp.databases.informix)
  • Re: SEQUENTIAL SCANS
    ... Above query correctly takes INDEX PATH. ... It's perplexing why would optimizer behave like this. ... you did run update statistics low for table B, ...
    (comp.databases.informix)
  • Re: Update Statistics and poor performance
    ... Is it possible that most sql's run efficiently during update stats ... 9.30UC2 is the last version released before the update statistics code was rewritten in 9.30xC3. ... For your server version you can enable this updated algorithm in dostats by adding the -o flag which inverts the server version detection logic. ... particularly odd looking query. ...
    (comp.databases.informix)
  • Re: urlencode vs rawurlencode
    ... | This method was used to make query URIs easier to pass in systems ... in the query component of an HTTP URL. ... A URI may contain ... Syntax and Escaping", ...
    (comp.lang.php)