Find Duplicate Indexes on SQL Server (Script)

Like other mainstream commercial database systems, Microsoft
SQL Server allows you to create multiple indexes on the same column of a table.
This  increases the likelihood of
having duplicate indexes in the database because SQL Server does not
prevent you from creating duplicate indexes, infect we can create up to 999 duplicate indexes on each
table inside database. Having duplicate indexes on tables columns can
significantly hurt the performance of your database because SQL Server has to
maintain each duplicate index separately (such as updating these duplicate indexes
during DML operations and calculating and updating statistics for these
duplicate indexes). Moreover, SQL Server query optimizer will consider each of
them when it optimizes queries, which can cause serious performance impact. In
this article, we’ll understand what a duplicate index is and how we can find
and remove the duplicate indexes from database.

Duplicate indexes are indexes of same type, created on the
same set of key columns in the same order, and same set of non-key columns
(also known as included columns) with same or different order, but have
different names. We should not create duplicate indexes on the table, and
remove them if you find them.

No one deliberately creates duplicate indexes on the table.
Sometimes, you can create duplicate indexes unknowingly. For example, creating
primary key and unique key on the same column creates duplicate indexes because
by default SQL Server creates clustered index and unique key created
non-clustered index. Although, you can find duplicate indexes using SQL Server
Management Studio (SSMS), however this is not a quickest way to find duplicate
indexes, especially when your SQL Server contains many databases, containing
many tables and indexes. This is where following SQL Server system catalogs
comes handy, which we can use and write a quick script to find duplicated
indexes inside SQL Server databases:

  • sys.indexes – Returns
    index type, filegroup or partition, and index option settings.
  • sys.objects – Returns row
    for each user-defined, schema-scoped object that is created within a
    database.
  • sys.index_columns
    Returns Column ID, column position in index, key or non-key, and sort
    order.
  • sys.columns – Returns
    information about column use in tables and views.

For example, you can use the following script, which I have
written using the SQL Server system catalogs that help you find duplicate
indexes for all databases hosted on the SQL Server instance:


Finding_Duplicate_Indexes.sql

Example output when executed against my test SQL Server, I
found few duplicate indexes inside databases:

Take extra care when designing indexes for your database
queries, as duplicate indexes could cause poor database performance during DML
operations. Review existing database indexes prior to designing new database
indexes. Find and delete duplicate indexes that are found within your databases
because removing them not only improve database performance but also reduces
the database and backup size.

                                                                                                              




Array

2 Responses to “Find Duplicate Indexes on SQL Server (Script)”

  1. Great article – I had to make these changes in order for this to work on our DBs.

    CREATE TABLE #AllIndexesDetailedInfo
    ([TableName] [varchar](256)
    ,[IndexName] [varchar](256)
    ,[IndexType] [varchar](13)
    ,[KeyColumns] [varchar](1024)
    ,[NonKeyColumns] [varchar](1024)
    ,[KeyColumnsOrder] [varchar](1024)
    ,[NonKeyColumnsOrder] [varchar](1024)
    ,[IsUnique] [char](1)
    ,[HasNonKeyColumns] [char](1))

  2. The script works well but if you have multiple schema it will match tables of the same name in different schema.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |