Indexes are the solution to many performance problems, but with too many indexes on tables can hurt the performance of your database. This is because SQL Server performs extra work in order keep these indexes up-to-date during insert/update/delete operations against tables where these indexes exist. This means that the SQL Server database engine needs more time when updating data in the table, which is based on the number and complexity of these indexes. Having duplicate indexes can even hurt the performance of your database more because SQL Server needs to examine more query plans and the SQL Server database engine needs to maintain, calculate and update these duplicate index statistics. As we know, SQL Server allows us to create indexes with multiple columns, which obviously increases the likelihood of having duplicate indexes in the database, infect we can create up to 999 duplicate indexes on each table inside database.

Check out my article (ie. Detecting and Removing Duplicate Indexes), in which I discussed, what a duplicate index is and how we can find and remove the duplicate indexes from database.

This article is published on SSWUG.org.