473,242 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

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.

Feb 13 '06 #1
4 3232
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: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.

Feb 13 '06 #2

"Steph" <ss@nospam.com> 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
Feb 13 '06 #3
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, 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
Feb 13 '06 #4
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.

Feb 26 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Chuck Chekuri | last post by:
Hi, We have a 3TB oracle db. 24x7 operation with data constantly coming in to the DB. Users from all over the world use a web based applicaton to query and work on the data. Out transaction...
2
by: Craig Stadler | last post by:
I have a fairly large database on my (2 million records) WIN32 mySQL 4.0.20 The question is: I am adding large amounts of data to it (300,000 to 500,000 at a time) with the standard (INSERT...
1
by: Danny | last post by:
sorry for posting a question that has probably been posted hundreds of times, but i can't seem to find the answer i need...We're using a Sql Server 7.0 database and recently started getting timeout...
3
by: sjs | last post by:
I am in the process of testing an EMC Clarion install with our Data Warehouse. Performance is fast and consistent for queries using a clustered index but very poor for queries using non-clustered...
1
by: Perre Van Wilrijk | last post by:
Hi, I've got a full text index which works fine, SQLSERVER2000/WIN 2000 SERVER. The system requires to update indexes immediately, so I use a timestamp field to enable this. No problems so...
1
by: jon wayne | last post by:
Hi Am trying to replace the classic switch case construct (am workng on a telecom stack devlpmnt)with an array of function ptrs. The problm am facing is that of indexing. - the case vals can be...
17
by: Danny J. Lesandrini | last post by:
The following code works with a standard MDB to navigate to a particluar record (with a DAO recordset, of course) but it's giving me problems in an ADP I'm working on. Dim rs As ADODB.Recordset...
6
by: Hennie7863 | last post by:
Hi everybody, After days reading stuff about indexing, extents, pages, 8KB, 64 KB, IGNORE_DUP_KEY, CREATE INDEX, bla bla, lalalala, lllllll, grrrrrrr and testing with different kinds of queries,...
7
by: Ryan | last post by:
I have a bit of a problem with regards an indexing strategy. Well, basically there is no indexing strategy on a set of data I have at work. Now, I didn't create the design as I would have allowed...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.