August 6, 2004 at 6:08 am
We have a purchased application that has one big table that has 9 million rows in it that has 13 indexes on it. In looking at it I say one of the indexes is a duplicate that will not be used even though its not an exact duplicate. In talking with the companies Tech. support they say it will be used and would prefer I not drop it... I know in DB2 and in Oracle it could safely be dropped but I am looking to see if SQL Server prof. say the same... here are the two ix:
CREATE INDEX [ix_item_RT_STT] ON [abc].[item]([REC_TYPE], [STATE]) ON [PRIMARY]
CREATE INDEX [ix_item_RC_ST_SR] ON [abc].[item]([REC_TYPE], [STATE], [SOURCE]) ON [PRIMARY]
I would say the first IX could be dropped as the same columns exist in the second ix, any reason why I should not drop the IX?
August 6, 2004 at 10:05 am
How big is the Source Column ?
August 6, 2004 at 10:56 am
Three thoughts:
1. any index can be dropped. Period. However, it can affect the efficiency of queries. If your queries don't/won't use the index, then dropping it will have no effect.
2. this is 3rd party software. They built it there and request you don't remove it. What will happen when they upgrade their application? Will you be able to apply the upgrade - or will your dropping the index interfere with an upgrade?
3. Along with #2, will the vendor still support the application if you drop the index?
I administer a DB that is built via a vendor's software...they have explicitly said that any changes to the database (triggers, etc) will mean that future updates will not work. And I will have to return the system to it's original state before applying updates.
-SQLBill
August 6, 2004 at 12:05 pm
depending on your query type...If a query only looks at REC_TYPE and STATE, then the second index appears obsolete. But it has one more key "SOURCE" so in case a query looks for REC_TYPE, STATE, and SOURCE, the optimizer may decide to choose the second one.
August 9, 2004 at 2:17 am
The second index appears like a "covering index". it will be usefull to read the data directly from index without going to the data pages.
Another thing i can see is All the other fields except source may be int. (I dont know, i am making some guess based on column names) Source could be varchar. as using varchar in indexes may degrade performance, some used to build indexes on that. However, In that case, I feel that the first index could be removed.
As SQLBill stated, if your product and database are developped by them, ALWAYS contact them before doing any change into database. If you see any performance issues, inform them on the problem, not on the solution.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
August 9, 2004 at 10:00 am
The vendor may be using index hints. If this is the case, then any query referencing that index will fail.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply