May 14, 2012 at 3:50 pm
I have a very large table with with over 40 fields and millions of records. There are currently 7 non-unique, non-clustered indexes on this table. There is also one clustered index.
Adding two or three more non-unique, non-clustered indexes would likely result in better query performance.
Is there any reason why I shouldn't add more indexes?
May 14, 2012 at 3:56 pm
The downside to more indexes is increased time for inserts/updates/deletes as indexes have to be maintained. Hard to say if the impact will be noticable not since we can't see how the table is currently configured, plus, your hardware my be able to handle the extra processing without much difficulty depending on the indexes you are adding.
May 14, 2012 at 4:14 pm
sdruid71 (5/14/2012)
I have a very large table with with over 40 fields and millions of records. There are currently 7 non-unique, non-clustered indexes on this table. There is also one clustered index.Adding two or three more non-unique, non-clustered indexes would likely result in better query performance.
Is there any reason why I shouldn't add more indexes?
I've found that such a multitude of indexes can normally be trimmed down to just two or three. It takes a bit of work and is more of an art than a science, but the rewards are pretty good especially when it comes to maintenance.
Of course, if you have a table with millions of rows and haven't partitioned to make index maintenance easier, you might want to look into it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply