SQL Overlapping and Multi-Column Indexes

Question

I am attempting to tune some stored procedures and have a question on indexes. I have used the tuning advisor and they recommended two indexes, both for the same table. The issue is one index is for one column and the other is for multiple columns, of which it includes the same column from the first. My question is why and what is the difference?

CREATE NONCLUSTERED INDEX [_dta_index_Table1_5_2079723603__K23_K17_K13_K12_K2_K10_K22_K14_K19_K20_K9_K11_5_6_7_15_18]
ON [dbo].[Table1]  (    
    [EfctvEndDate] ASC,     
    [StuLangCodeKey] ASC,
    [StuBirCntryCodeKey] ASC,
    [StuBirStOrProvncCodeKey] ASC,
    [StuKey] ASC, 
    [GndrCodeKey] ASC,
    [EfctvStartDate] ASC,
    [StuHspncEnctyIndctr] ASC,
    [StuEnctyMsngIndctr] ASC,
    [StuRaceMsngIndctr] ASC,
    [StuBirDate] ASC,   
    [StuBirCityName] ASC 
) INCLUDE (
    [StuFstNameLgl],
    [StuLastOrSrnmLgl], 
    [StuMdlNameLgl],
    [StuIneligSnorImgrntIndctr],
    [StuExpctdGrdtngClYear]
) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) 
ON [PRIMARY] go

CREATE NONCLUSTERED INDEX [_dta_index_Table1_5_2079723603__K23]
ON [dbo].[Table1]  (
    [EfctvEndDate] ASC 
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)     
ON [PRIMARY]

Answer 1

The "stand alone" EfctvEndDate index, while being functionally available in the other index, will be much smaller, and hence more efficient (with regards to number of reads required, to its ability to be cached, remain in the cache etc. etc.).

This of course depends much on the usage patterns etc. but yes, in general, it is very plausible that having multiple, apparently redundant indexes be a sensitive approach.

The drawbacks of index "duplication" are mainly (and probably in order of bigger to smaller impact):

• INSERT/UPDATE/DELETE queries on the underlying table, incur a performance overhead to maintain the extra index(es).
• competition for cache usage
• [very slightly] longer time to produce query plans.
• storage overhead (typically a non-issue; does increase backup times, however...).

One must therefore estimate if the improved performance with SELECT queries which can potentially benefit from the extra index(es) offset the drawback listed above. Database performance tuning is typically a case-by-case exercise...

Answer 2

If, as in your case above, the single column is the first column defined in the multi-column index: It doesn't always get it right, OR the query workload has changed over time. If the multi-column index is beneficial and being used, you can delete the single column index. BUT, profile and check the index usage report.

If not, then it is applicable to different queries. One thing I have noticed the DTA likes to do is to create an index which is essentially a copy of the entire table, especially in situations where the query workload is emitted by an ORM.

As with this case and all others, it is important that you profile to determine the effectiveness of any indexes with respect to your 'normal' query workload.