Posted by Andrew J. Kelly on 02/14/06 00:03

You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.

--
Andrew J. Kelly SQL MVP


"Steph" <ss@nospam.com> wrote in message
news:OY4MGMOMGHA.2580@TK2MSFTNGP14.phx.gbl...
> Hi -
>
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
>
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
>
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
>
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
>
> Ideas much appreciated.
>
>
>

[Back to original message]