• GilaMonster - Monday, January 23, 2017 3:24 AM

    Welsh Corgi - Monday, January 23, 2017 2:58 AM

    So are you telling me if a have for example an index CallID as a single column index and I have another Index that starts off with CallID and several other columns I do not need the index with CallID as the single column Index?

    In general, yes. The narrower index is redundant.

    John Mitchell-245523 - Monday, January 23, 2017 3:20 AM

    Have you looked in sys.dm_db_index_usage_stats?

    Index usage stats will usually show both indexes being used, because the optimiser will happily use them both (also happens when there are multiple identical indexes)

    Yes, precisely.  What I didn't mention is that once we've established that it's being used, we then have to decide whether the marginal gain from having the narrower index is worth the cost of maintaining it.  If the wider index is much wider, then it's possible the narrower index provides a significant performance advantage, or maybe a performance advantage on an important query.  These are all questions whose answers can only be known from an understanding of the data, usage patterns and business requirements.

    John