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.
4  3270 
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.co m> wrote in message
news:OY******** ******@TK2MSFTN GP14.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.co m> wrote in message
news:OY******** ******@TK2MSFTN GP14.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, es****@sommarsk og.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
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 performance is quite good but our query performance
sucks. looking up by ids and some combinations of search critera
return ok but some never return. We indexed the db for a lot of
combinations and we materialized some views and that helped a little....
|
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 INTO) command.
Question is, do I have to reindex, and when and how often?
Does mySQL automatically build indexes on the fly as I add data?
|
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 expired
errors on our views, which i'm assuming is due to the volume of
records in our tables. Unfortunately i'm not too experiences w/
indexing tables, so my question is:
1) how do you determine which fields to index (does it have to be a...
|
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 indexes.
Performance on non-clustered indexes is very slow and inconsistent
compared to our current production environment using EMC Symmetrix.
I eliminated the server and SQL install as the issue by testing the
same queries on local disk on...
|
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 far.
Now, I've got a stored procedures which nearly daily inserts about 10.000
rows. When doing this while full text indexing is active, all users start
complaining about performance. In order to work around this problem I tried
| | |
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
any of 0- 0xFF(1 byte long) - but unfortunately as of now I just need
to use about 15 of 'em.
In the sense I can't just make an array of function ptrs - coz the
case vals I need to use right now are high up in the range.
|
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
Set rs = Me.RecordsetClone
rs.Find "=" & lngContractID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
I must site the Heisenberb Uncertainty Principal here, as it
|
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, indexes, situations i'm
getting confused, irritated, etc.
SITUATION
I have the following situation. We have a fact table with 1,8 million
records (Testsituation) and i'm inserting 100000 records and i want to
|
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 for this.
OK, so there is primary key (clustered) indexes (mainly composite
keys), but no other indexes on the tables. As you would expect, the
performance leaves a lot to be desired. A hell of a lot. We have
several million rows in a lot of the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
