Indexing issue, inconsistent performance

[Steph]

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.

[Andrew J. Kelly]

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" <[e-mail]> wrote in message
news:OY**************@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.

[Andrew]

"Steph" <[e-mail]> wrote in message
news:OY**************@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

[Erland Sommarskog]

Steph ([e-mail]) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

ouch.
i hate these.

this is real world stuff, and this happens more then you think.

Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.

To do that, look at making things unique. Post some code and we can
help.