Posted by Andrew on 02/14/06 00:29

"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.
>
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough

[Back to original message]