"Erland Sommarskog" <sommar@algonet.se> ha scritto nel messaggio
news:Xns94F09D378E44FYazorman@127.0.0.1...
> Eugenio (Ciao@Eugenio.it) writes:
> > This query run on my SQL7 server and it takes about 10 seconds.
> > This query exists on another SQL7 server and until last week it took
about
> > 10 seconds.
> > The configuration of both servers are same. Only the hardware is
> > different.
> >
> > Now, on the second server this query takes about 30 minutes to extract
> > the s ame details, but anybody has changed any details.
> >
> > If I execute this query without Where, it'll show me the details in 7
> > seconds.
> > This query still takes about same time if Where is
>
> With out knowledge about the underlying tables in the view and their
> indexes, I can only answer in general terms.
>
> SQL Server uses a cost-based optimizer. This optimizer evaluates a number
> of possible query plans, and estimates which plan will give the best
> performance. As basis for its decisions it uses statistics about the
> table which holds the distribution of the data in the various columns.
> By default these statistics are updated automatically, usually in
> conjunction with SQL Server querying the tables.
>
> There are plenty of possibilities for optimizer to go wrong. For instance
> if the statistics are somewhat skewed, a small error in the first table
> when computing the cost of a certain join order, may be a large error
> in the last, and incorrectly lead to the wrong plan. There are also
> systematic errors; the optimizer does not know about correlation between
> columns, for instance that OrderDate and InvoiceDate tend follow each
> other.
>
> Because of this, it can well happen that a query that executed well
> yesterday, suddenly executes much slower, because you've execeeded some
> threshold which causes SQL Server to pick a bad plan.
>
> The first you should do is to verify that the involved tables have the
> same set of indexes in both servers. You could also try UPDATE STATISTICS
> WITH FULLSCAN on the problematic server, this may remove inaccuracies in
> the statistics. You should also use DBCC SHOWCONTIG on the tables and
> observe fragmentation. Use DBCC DBREIDNEX to defragment the tables.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp


Thanks Erland
I'll try it immediatly.
Bye
Eugenio