Indexes are essential to all databases for optimal query performance.  However, most index tuning is a laborious trial-and-error process that starts when a query does not perform well (usually resulting in user complaints), and ends with the addition of at least one new index that appears to improve performance.  However, an index that helps one query may not improve the performance of similar queries.  Therefore, the process of determining an appropriate index structure is very labor-intensive, even when the DBA staff knows how to do it.

  

All good DBA's know that every index incurs overhead whenever new records are inserted into a table or the keys of an existing record are changed.  However, it is not uncommon to find databases that are over-indexed, resulting in poor query performance and a DBA's nightmare of being unsure of how to provide an appropriate index strategy.  The ideal index strategy is one that utilizes as few indexes as possible, yet includes all of the unique and necessary indexes to enable queries to run quickly.  Unfortunately, determining optimal indexes and finding all of the unused or unnecessary ones can take many weeks of trial and error.

SQLRx provides a report that you can use to see what database tables are burdened with unused or unnecessary indexes, how much additional index overhead is being performed to maintain unused indexes, and how many missing indexes are reported by SQL Server.  Take a look at the Sample Report below (derived from actual production system data):