I recently noticed that a particular table had two indexes that were nearly identical. In fact, the indexed columns were exactly the same. The only difference was in the included columns. This made me wonder if there were any other indexes that had duplicates or near duplicates, so I developed a query to get results from the entire database. The query returns the following columns:

• Table Name
• Index Name
• Indexed Column Names
• Included Column Names
• Page Count (for the Index)
• Total Index Size
• Fragmentation (of the Index)

This made it really easy to see not only which indexes had duplicates or near duplicates, but also how large those indexes were. This allowed me to focus my time on the larger indexes, which have a bigger impact in not only storage, but also rebuilding and reorganization tasks.

So, here it is:

WITH IndexSummary AS  
 (

SELECT DISTINCT sys.objects.name AS [Table Name],  
 sys.indexes.name AS [Index Name],  
 SUBSTRING((SELECT ', ' + sys.columns.Name as [text()]  
 FROM sys.columns  
 INNER JOIN sys.index_columns  
 ON sys.index_columns.column_id = sys.columns.column_id  
 AND sys.index_columns.object_id = sys.columns.object_id  
 WHERE sys.index_columns.index_id = sys.indexes.index_id  
 AND sys.index_columns.object_id = sys.indexes.object_id  
 AND sys.index_columns.is_included_column = 0  
 ORDER BY sys.columns.name  
 FOR XML Path('')), 2, 10000) AS [Indexed Column Names],  
 ISNULL(SUBSTRING((SELECT ', ' + sys.columns.Name as [text()]  
 FROM sys.columns  
 INNER JOIN sys.index_columns  
 ON sys.index_columns.column_id = sys.columns.column_id  
 AND sys.index_columns.object_id = sys.columns.object_id  
 WHERE sys.index_columns.index_id = sys.indexes.index_id  
 AND sys.index_columns.object_id = sys.indexes.object_id  
 AND sys.index_columns.is_included_column = 1  
 ORDER BY sys.columns.name  
 FOR XML Path('')), 2, 10000), '') AS [Included Column Names],  
 sys.indexes.index_id, sys.indexes.object_id  
 FROM sys.indexes  
 INNER JOIN SYS.index_columns  
 ON sys.indexes.index_id = SYS.index_columns.index_id  
 AND sys.indexes.object_id = sys.index_columns.object_id  
 INNER JOIN sys.objects  
 ON sys.OBJECTS.object_id = SYS.indexES.object_id  
 WHERE sys.objects.type = 'U'  
 )

SELECT IndexSummary.[Table Name],  
 IndexSummary.[Index Name],  
 IndexSummary.[Indexed Column Names],  
 IndexSummary.[Included Column Names],  
 PhysicalStats.page_count as [Page Count],  
 CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Size (MB)],  
 CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragment %]  
 FROM IndexSummary  
 INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)  
 AS PhysicalStats  
 ON PhysicalStats.index_id = IndexSummary.index_id  
 AND PhysicalStats.object_id = IndexSummary.object_id  
 WHERE (SELECT COUNT(*) as Computed  
 FROM IndexSummary Summary2  
 WHERE Summary2.[Table Name] = IndexSummary.[Table Name]  
 AND Summary2.[Indexed Cols] = IndexSummary.[Indexed Cols]) > 1  
 ORDER BY [Table Name], [Index Name], [Indexed Column Names], [Included Column Names]  

Table Name Index Indexed Cols Included Cols Pages Size (MB) Frag % MyTable Indx1 Col1 Col2, Col3 123 0.96 8.94 MyTable Indx2 Col1 Col2, Col3 123 0.96 8.94

For more tips on managing a SQL Server Database, see Managing a SQL Server Database: Tips for Beginners