Disadvantages of indexes (4): Too many indexes will reduce performance

The purpose of creating an index is to speed up the query, but anything is too much or too little.

If too many indexes are created, performance will be reduced.

(1) How many indexes are calculated?

Let me tell you the truth first.

A few years ago, I wrote a simple SQL and thought it was slow to check it. I wonder if there was no index.

So I opened the ssms client, clicked on the index below the table, and found that the table had more than 100 indexes, and I was confused at the time. . .

There are too many of these more than 100 indexes, which also shows that this database has no DBA management and is very confusing. This may be a bigger problem.

Generally speaking, if there are about 5 indexes for a table, you can conduct actual tests for this specific number. If there are too many indexes and the insertion is slow, you can reduce or merge the indexes.

(2) Delete unnecessary indexes through monitoring

Delete only user_updates, but user_seeks is 0.

select
     DB_NAME(d.database_id),
     OBJECT_NAME(d.object_id),
        
     ,
     user_seeks,
     user_scans,
     user_lookups,
        
     user_updates --number of updates performed through user query
 from sys.dm_db_index_usage_stats d
 inner join i
         on d.object_id = i.object_id
            and d.index_id = i.index_id
 where database_id = DB_ID('database name')

(3) Find inefficient index

Find indexes with user_updates that are relatively large, while user_seek and user_scan are very small, and analyze why there is maintenance overhead, and the actual index usage is less. Is it because the index is created unreasonable, and the order and number of fields are incorrect.