To ensure that indexes evolve with your system:
Monitor queries to determine if indexes are still appropriate for your applications.
Periodically, check the query plans, as described in Chapter 5, “Using set showplan,” in the Performance and Tuning: Monitoring and Analyzing book and the I/O statistics for your most frequent user queries. Pay special attention to noncovering indexes that support range queries. They are most likely to switch to table scans if the data distribution changes
Drop and rebuild indexes that hurt performance.
Keep index statistics up to date.
Use space management properties to reduce page splits and to reduce the frequency of maintenance operations.
