Dramatical performance degradation after removing / adding indexes

Very strange problem occurs when we removed from few tables indexes and added it again. Database after that operation is noticeably slower than the same DB before.
Also the DB after removal / adding indexes seems to be different in a unknown way.
I could prove that running SQL Analyzer - query plan analysis on both DBs are different before operation and after that!!! (?)
So the DBs are identical but in some strange way different.
Could you have any ideas or maybe it is known SQL Srv 2000 problem?
We run SQL 2000 SP3.
Kind regards,
Adam Heczko
Just recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov
|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko
|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon
|||> are you sure you rebuilt the indexes in the same way they were built before
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczko

Dramatical performance degradation after removing / adding indexes

Very strange problem occurs when we removed from few tables indexes and adde
d it again. Database after that operation is noticeably slower than the same
DB before.
Also the DB after removal / adding indexes seems to be different in a unknow
n way.
I could prove that running SQL Analyzer - query plan analysis on both DBs ar
e different before operation and after that!!! (?)
So the DBs are identical but in some strange way different.
Could you have any ideas or maybe it is known SQL Srv 2000 problem?
We run SQL 2000 SP3.
Kind regards,
Adam HeczkoJust recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon|||> are you sure you rebuilt the indexes in the same way they were built before">
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczkosql

Dramatical performance degradation after removing / adding indexes

Very strange problem occurs when we removed from few tables indexes and added it again. Database after that operation is noticeably slower than the same DB before
Also the DB after removal / adding indexes seems to be different in a unknown way
I could prove that running SQL Analyzer - query plan analysis on both DBs are different before operation and after that!!! (?
So the DBs are identical but in some strange way different
Could you have any ideas or maybe it is known SQL Srv 2000 problem
We run SQL 2000 SP3
Kind regards
Adam HeczkoJust recompile your stored procedures and views - generate script for
drop/create and run it.
Bojidar Alexandrov|||Adam
Try to update statistics
"Adam Heczko" <a.heczko@.zibico.com.pl> wrote in message
news:933E6C88-A99E-40BB-A080-B0C8FBB722C7@.microsoft.com...
> Very strange problem occurs when we removed from few tables indexes and
added it again. Database after that operation is noticeably slower than the
same DB before.
> Also the DB after removal / adding indexes seems to be different in a
unknown way.
> I could prove that running SQL Analyzer - query plan analysis on both DBs
are different before operation and after that!!! (?)
> So the DBs are identical but in some strange way different.
> Could you have any ideas or maybe it is known SQL Srv 2000 problem?
> We run SQL 2000 SP3.
> Kind regards,
> Adam Heczko|||are you sure you rebuilt the indexes in the same way they were built before
?
Fill Factor Settings
Pad_Index Settings
File Group, etc etc etc
?
Greg Jackson
PDX, Oregon|||> are you sure you rebuilt the indexes in the same way they were built before
> ?
> Fill Factor Settings
> Pad_Index Settings
> File Group, etc etc etc
>
> ?
> Greg Jackson
> PDX, Oregon
>
Yes, we tried to rebuild stored procedures, views etc. - nothing really
helped.
Adam Heczko

Dont use clustered indexes?

Hi,

The more I read, the more confused I'm getting ! (no wonder they say
ignorance is bliss)

I just got back from the bookstore and was flipping through some SQL Server
Administration books.

One says, that to get the best query performance, youi do two things:

1. Cover all the columns used in each SELECT (including the WHERE, ORDER
BY , etc.) with an index

2. Make sure it's a NON-CLUSTERED index.

In this way, the author says, you avoid ever going directly to the base
tables for data to resolve the query - i.e. it's resolved in the index.

So, for example, he argues if you have:

SELECT Lname,Fname, CompanyName
from Contacts
inner join Customers
on (contacts.custid = customers.custid)

that you use two non-clustered indexes:
1. Lname,Fname and custid from the Contacts table
2. CompanyName and custid from Customers

(as opposed to the standard approach of a clustered index on the PK's of
each table)

He says that clustered indexes don't speed up performance because they're
the same as a full table scan. Should I drop clustered indexes from my
large tables, given that there are multiple non-clustered indexes on them?
Is it better to just use multiple non-clustered indexes on a heap table?

SteveThe best indexing strategy depends on a number of factors. It is true that
a covering non-clustered index will be beneficial to some queries,
especially when only a few columns are selected and most of the rows in the
table are needed. However, you need to balance the cost of maintaining the
index with the benefits of using it. Too many indexes can slow down
insert/update performance and increase the likelihood of blocking and
deadlocks. It's overkill to create a lot of non-clustered indexes to cover
queries unless the database is read-only, the additional disk space
requirements aren't a concern and you can anticipate the queries beforehand.

Personally, I rarely create heap tables in SQL 7 and above. The clustered
index eliminates the I/O overhead of maintenance to non-clustered leaf nodes
due to page splits because the clustered key rather than physical location
is used to as the bookmark to data rows. See the Books Online
<architec.chm::/8_ar_da2_8sit.htm> for a discussion on clustered and
non-clustered indexes.

Covering non-clustered indexes are appropriate to address specific
performance issues but, IMHO, are the exception rather than the rule.
Exercising a little common sense in creating useful indexes goes a long way
in preventing performance problems.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Steve_CA" <steveee_ca@.yahoo.com> wrote in message
news:5cccd.67695$3C6.2513201@.news20.bellglobal.com ...
> Hi,
> The more I read, the more confused I'm getting ! (no wonder they say
> ignorance is bliss)
> I just got back from the bookstore and was flipping through some SQL
> Server Administration books.
> One says, that to get the best query performance, youi do two things:
> 1. Cover all the columns used in each SELECT (including the WHERE, ORDER
> BY , etc.) with an index
> 2. Make sure it's a NON-CLUSTERED index.
> In this way, the author says, you avoid ever going directly to the base
> tables for data to resolve the query - i.e. it's resolved in the index.
> So, for example, he argues if you have:
> SELECT Lname,Fname, CompanyName
> from Contacts
> inner join Customers
> on (contacts.custid = customers.custid)
> that you use two non-clustered indexes:
> 1. Lname,Fname and custid from the Contacts table
> 2. CompanyName and custid from Customers
> (as opposed to the standard approach of a clustered index on the PK's of
> each table)
> He says that clustered indexes don't speed up performance because they're
> the same as a full table scan. Should I drop clustered indexes from my
> large tables, given that there are multiple non-clustered indexes on them?
> Is it better to just use multiple non-clustered indexes on a heap table?
> Steve

Does views use table indexes.

Hi.
I have a situation where I have a table containg data, but several columns
can be used to designate that the data is "hidden". E.g. if we delete a
record, it isn't actually deleted but a datetime field called deleted_date
is changed from null to the current date-time.
Therefore I made a view like
create view vw_LiveData as select * from Data where deleted_date is null
The original table had a full-text indexed column, but using that search
criteria on the view didn't work
select * from vw_LiveData where contains(fullTextField, 'blahblahblah')
The line produces an error saying that the view doesn't have a fulltext
index.
What I speculate now is all the other indexes defined on the table, do they
have any importance when searching through data in the view?
Suppose the Data table has a foreign key to a user called User_ID that is
indexed on the table
Clearly the index is used when I type
Select * from Data where User_ID=@.User_ID
Is it also used when I type
select * from vw_LiveData where User_ID=@.User_ID
or is it necessary to created index views if I want both easy to read sql
code AND acceptable performance?
Thanks in advance,
Peter StrimanPeter Striman wrote:
> Hi.
> I have a situation where I have a table containg data, but several
> columns can be used to designate that the data is "hidden". E.g. if
> we delete a record, it isn't actually deleted but a datetime field
> called deleted_date is changed from null to the current date-time.
> Therefore I made a view like
> create view vw_LiveData as select * from Data where deleted_date is
> null
> The original table had a full-text indexed column, but using that
> search criteria on the view didn't work
> select * from vw_LiveData where contains(fullTextField,
> 'blahblahblah')
> The line produces an error saying that the view doesn't have a
> fulltext index.
> What I speculate now is all the other indexes defined on the table,
> do they have any importance when searching through data in the view?
> Suppose the Data table has a foreign key to a user called User_ID
> that is indexed on the table
> Clearly the index is used when I type
> Select * from Data where User_ID=@.User_ID
> Is it also used when I type
> select * from vw_LiveData where User_ID=@.User_ID
> or is it necessary to created index views if I want both easy to read
> sql code AND acceptable performance?
No. The index is used. It may be different with full text indexes -
check with BOL. Btw, IMHO it's better to name all columns in the view
explicitely. That way your view does not change if the underlying table
changes.
Kind regards
robert|||Thanks for the reply, just what I wanted to hear :)
It is different with full-text indexes - and that was what gave me the
concern. But it's no issue in my case since I would only use the
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:%23F0oyoLnFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Peter Striman wrote:
> No. The index is used. It may be different with full text indexes -
> check with BOL. Btw, IMHO it's better to name all columns in the view
> explicitely. That way your view does not change if the underlying table
> changes.
> Kind regards
> robert
>|||
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:<#F0oyoLnFHA.2484@.TK2MSFTNGP15.phx.gbl>...
<snip>
Btw, IMHO it's better to name all columns in the view

> explicitely. That way your view does not change if the underlying table

> changes.

>

> Kind regards

>

> robert

>
I STRONGLY agree. A view using a SELECT * will do very strange things if
fields are added to the table.
Daniel Wilson <Mailto://d.wilson@.embtrak.com>
Senior Software Solutions Developer
Embtrak <http://www.Embtrak.com> Development Team
DVBrown Company
(864)292-5888