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

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

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