How to Find and Remove Duplicate and Redundant Indexes in SQL Server

 Tags: SQL Server, Indexing, Performance Tuning, T-SQL

Keywords: Duplicate Indexes, Redundant Indexes, SQL Server Indexing, Indexing Best Practices, Performance Tuning, T-SQL Script

Article:

Indexes are a crucial aspect of database performance tuning. They help to improve query performance by allowing SQL Server to quickly locate the necessary data in a table. However, having too many indexes can have the opposite effect and slow down the database performance. In this article, we'll explore how to find and remove duplicate and redundant indexes in SQL Server.

Duplicate indexes are indexes that have the same key columns in the same order as another index. Redundant indexes are indexes that are not used by any queries or are used by queries that are already covered by other indexes. Both types of indexes can cause performance issues and should be removed from the database.

To find duplicate and redundant indexes, we can use the following T-SQL script

SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, STUFF(( SELECT ', ' + COL_NAME(ic.object_id, ic.column_id) FROM sys.index_columns ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH('') ), 1, 2, '') AS KeyColumns, STUFF(( SELECT ', ' + COL_NAME(ic.object_id, ic.column_id) FROM sys.index_columns ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ORDER BY ic.key_ordinal FOR XML PATH('') ), 1, 2, '') AS IncludedColumns, i.type_desc AS IndexType, i.is_unique AS IsUnique, i.is_primary_key AS IsPrimaryKey, COUNT(*) AS ColumnCount, SUM(a.total_pages) AS TotalPages, SUM(s.user_seeks) AS UserSeeks, SUM(s.user_scans) AS UserScans, SUM(s.user_lookups) AS UserLookups, SUM(s.user_updates) AS UserUpdates FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id WHERE i.name NOT LIKE 'PK_%' AND i.name NOT LIKE 'UQ_%' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND i.is_unique = 0 AND i.is_disabled = 0 AND i.is_hypothetical = 0 GROUP BY i.object_id, i.index_id, i.name, i.type_desc, i.is_unique, i.is_primary_key, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates HAVING COUNT(*) > 1 ORDER BY TotalPages DESC;

This script retrieves information about all non-clustered indexes in the database that have more than one column and are not primary keys, unique constraints, or disabled. The script groups the indexes by their key columns and shows their name, table name, type, whether they are unique or primary keys, and their total size in pages.

To remove the duplicate or redundant indexes, we can use the DROP INDEX the statement, like this:

DROP INDEX TableName.IndexName;