Adding Indexes to Table hurts query performance (self-join)

I'm optimizing a query on SQL 2000.  The query joins a single table to itself 5 times to get various MAX(values).  

When I add a large covering clustered (or non-clustered index) to the table to help the query, performance suffers greatly.  This is a select statement only.

Why would a large index negatively impact read performance ?
LVL 6
JaffaKREEAsked:
Who is Participating?
 
JR2003Commented:
How many rows has the table?
Are you accessing columns that aren't in the index?
Can you paste the query in with the indexes you have created?
0
 
SQL_SERVER_DBACommented:
DO REBUILD
0
 
JaffaKREEAuthor Commented:
Rebuild the index ?  I've just created it.
0
 
JaffaKREEAuthor Commented:
> Are you accessing columns that aren't in the index?

After looking at this, I believe I made a mistake and excluded a necessary column from the index.  

I was concerned that the process, since it was accessing the same table/index 5 times, may have been deadlocking/blocking itself.


0
 
JR2003Commented:
sometimes it does take longer using an index depending one how many rows are accessed and what columns are on the index.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.