Drop indexes that hurt performance. If an application performs data modifications during the day and generates reports at night, you may want to drop some indexes in the morning and re-create them at night.
Many system designers create numerous indexes that are rarely, if ever, actually used by the query optimizer. Make sure that you base indexes on the current transactions and processes that are being run, not on the original database design.
Check query plans to determine whether your indexes are being used.
Foe more information on maintaining indexes see “Maintaining index and column statistics” and “Rebuilding indexes”.