SQL Server Performance

Too many indexes?

Discussion in 'Performance Tuning for DBAs' started by westermanm, Oct 10, 2006.

  1. westermanm New Member


    We have an appllication vendor supplied database about 10Gb in size at the moment, 5gb of which seem to be indexes. There are currently about 125 indexes, 88 of which the Index Tuning Wizard says are not used at all (using a three hour Profile Trace from the busiest time - which includes T-SQL and Sproc events).

    Another 20 or so indexes are only used about 0.1% according to the Tuning Wizard.

    The database application is OLTP.

    The Index Wiards basically says drop most indexes, some tables have 10-14 indexes on them!

    Performance is rubbish, 900 user connections, new 4-way server cluster, SAN drives 16Gb RAM. No I/O bottlenecks.

    Would dropping these indexes be a good place to start. The application vendor doesn't want me to.

    Your thoughts please ladies and gents.
    Many thanks.
  2. Roji. P. Thomas New Member

    10-14 indexes on a table seems to be an overkill. But I will NEVER drop an index without individually analyzing its usage manually, just because ITW suggests so.

    Roji. P. Thomas
  3. bradmcgehee New Member

    Following the advice of the Index Tuning Wizard to drop indexes is somewhat risky. This is because the assumption is that virtually every query that can be run was evaluated, which is unlikely. This means that it is possible that you might drop a necessary index. Of course, if you get a very good Profiler trace, and are confident that it reflects all queries, then you should be OK.

    But, as you said, I also am agreeing that this database may have too many queries. Any table that has 10-14 indexes indicates a problem, either with too many indexes, or really poor database design.

    One option you have is to evaluate the indexes on each table individually, checking to see if there are redundant indexes or indexes on columns where there is little variation in values (such as a column that specifies a person's sex, where the data is not selective). Both of these types of indexes can be safely dropped.

    Once you have done the above, and if you still have lots of indexes, you can try dropping the ones recommended by the Index Tuning Wizard, one at a time, and observing performance continually, checking for any potential problems with the now missing index. As you know, it is very easy to add the index if you later find the index is necessary. Doing this one at a time might be time consuming, but it a safe approach to take.

    If you upgrade this database to SQL Server 2005, there is a new management view that allows you to see how often indexes are used, making it much easier to determine if an index is really needed or not.

    Also, be sure you are reindexing the indexes at least once a week and are using an appropriate fillfactor.

    Brad M. McGehee, SQL Server MVP
  4. aneeshattingal New Member

  5. peterlemonjello New Member

    Can you tell me the name of the view in SQL2K5.
  6. FrankKalis Moderator

    I'n fairly certain, Brad meant this view: sys.dm_db_index_usage_stats

    Frank Kalis
    Microsoft SQL Server MVP
  7. bradmcgehee New Member

    Yes, Frank is correct.

    Brad M. McGehee, SQL Server MVP
  8. cstrong New Member

    Although I agree with the number of the indexes on the tables is exessive, you didn't mention the state of the indexes.

    Is there regular maintenance? Is there a lot of fragmentation? Are the statistics up to date?

    Also, looking at the profiler trace, are there any specific queries/sprocs that stand out as long running? High IO?

    Clive Strong
    SQL Server DBA
  9. westermanm New Member

    Thanks for the feedback everyone.


Share This Page