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