Posted by Erland Sommarskog on 02/14/06 01:08

Steph (ss@nospam.com) writes:
> 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?

Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.

But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]