Indexing issue, inconsistent performance

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.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.
>
>|||"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|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Steph wrote:
> 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.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server|||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.