SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Detecting and Removing Duplicate Indexes

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.

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.


Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...