multiple indexing issue [closed]

Question

I used Database Engine tuning advisor for indexing a table and when I am checking indexing , it showing multiple indexing on same table . as shown in fig

I am wondering for these question as follows :-

1. what will be the effect if i delete the blue highlighted indexing because it and indexing below it has 2 columns common .
2. Any effect on the performance ??
3. why multiple indexing ??
   4 . any other suggestion , other then delete ..

thanks in advance......

Answer 1

DTA is a good thing but you should always make the final decision yourself. You should not blindly believe what DTA or SQL Server Missing indexes suggests you regarding indexes as they do not take into consideration many performance aspects such as update/insert intensity on the the table, they only consider performance enhancement of the certain query/table regardless of the overall workload.

So the answer to your question will be:

1. This has happened because DTA considers these indexes will improve performance of queries hitting this table.

2. Over-indexing any table will result in degraded performance on update/insert/delete operations, in more storage used, in longer backup, checkdb, index maintenance operations. It will potentially lead to more intensive use of RAM. As you see - excessive indexes have a lot of drawbacks.

The answer to 3 and 4 - read through the articles recommended below and make sure you understand your workload (read/write activity) and how indexes operate. It is also a good practice to test such things first and apply in production if necessary.

1. SQL101: Indexing Basics by Kimberly Tripp
2. Are your indexing strategies working? (aka Indexing DMVs) also by Kimberly Tripp
3. SQL Server Index Design Guide in the TechNet Library