Intro

When looking at your index usage stats, you might see that all of your indexes are being used, and that life is good.

But when you have similar indexes on tables in your database, they could both have some seeks / scans / lookups, since the queries could randomly be selecting one.

Duplicate indexes take up unneeded space, and will slow down update / insert / delete operations.

 

Tracking down these bad boys

 

The below script will help you track down these overlapping indexes, currently up to 3 key columns deep.

It will notify you if one, two or all three key columns match an other index in the same table.

Then it will also display other information about the index and the other similar indexes.

On my DB with +600 indexes, it worked almost instantly, but to be sure you should execute this on a dev copy of the production database.

 

 

/**
Script created by Randi Vertongen, first part to get the index + key columns is not mine, need to find where i got it from.
Created on : 30/05/2018.

Last edited on: 31/05/2018

This script will try to find all indexes with the same key_columns, at this point up to 3 levels deep. 
It will issue a print statement for eacht level (first key column matches, first and second, first,second and third)

Could be rewritten more efficiently.

**/



use Climate
create table #temp(
id int identity(1,1),
table_name nvarchar(255),
is_unique nvarchar(50),
key_columns nvarchar(max),
include_columns nvarchar(max),
index_id int,
index_name nvarchar(4000),
TypeIndex nvarchar(100))

insert into #temp(table_name,is_unique,key_columns,include_columns,index_id,index_name,TypeIndex)
SELECT 
    quotename(sc.name) + N'.' + quotename(t.name) AS table_name,
	  CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END as Is_unique,
	key_definition as key_columns, 
	include_definition as include_columns,
    si.index_id,
    si.name AS index_name,
	case when si.index_id = 1 then 'CLUSTERED' else 'NONCLUSTERED' end as TypeIndex
FROM sys.indexes AS si
JOIN sys.tables AS t ON si.object_id=t.object_id
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS stat ON 
    stat.database_id = DB_ID() 
    and si.object_id=stat.object_id 
    and si.index_id=stat.index_id
LEFT JOIN sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
    (SELECT QUOTENAME(c.name) +N'^^ '
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.key_ordinal > 0
    ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'')) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
    SELECT MAX(QUOTENAME(c.name)) AS column_name
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + QUOTENAME(c.name)
    FROM sys.index_columns AS ic 
    JOIN sys.columns AS c ON 
        ic.column_id=c.column_id  
        and ic.object_id=c.object_id
    WHERE ic.object_id = si.object_id
        and ic.index_id=si.index_id
        and ic.is_included_column = 1
    ORDER BY c.name FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS includes ( include_definition )
/* Partitions */ OUTER APPLY ( 
    SELECT 
        COUNT(*) AS partition_count,
        CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
        CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
        SUM(ps.row_count) AS row_count
    FROM sys.partitions AS p
    JOIN sys.dm_db_partition_stats AS ps ON
        p.partition_id=ps.partition_id
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
    ) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
    FROM sys.partitions AS p
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
        and p.data_compression = 1
    ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
    (SELECT N', ' + CAST(p.partition_number AS VARCHAR(32))
    FROM sys.partitions AS p
    WHERE p.object_id = si.object_id
        and p.index_id=si.index_id
        and p.data_compression = 2
    ORDER BY p.partition_number FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'')) AS page_compression_clause ( page_compression_partition_list )
WHERE 
    si.type IN (2) /* heap, clustered, nonclustered */
ORDER BY table_name, si.index_id
    OPTION (RECOMPILE);
GO
set nocount on;
declare @tablename nvarchar(4000 ) 
declare @index_name nvarchar(4000 ) 
declare @index_name2 nvarchar(4000 ) 
declare @sql nvarchar(max)
declare @searchstring nvarchar(max)
declare @searchstring2 nvarchar(max)
declare @searchstring3 nvarchar(max)
declare @searchcolumn nvarchar(4000)
declare @searchcolumn2 nvarchar(4000)
declare @searchcolumn3 nvarchar(4000)
declare @pkname nvarchar(500)

create table #indexing(id int identity(1,1),
					  Tablename nvarchar(4000),
					  Indexname nvarchar(4000), 
					  Overlappingindex nvarchar(4000),
					  AmountOfColumnsMatching tinyint,
					  KeyColumnsMatched nvarchar(4000), 
					  AllKeyColumns nvarchar(4000), 
					  IncludedColumns nvarchar(4000),
					  IncludedColumnsOverlappingIndex nvarchar(4000),
					  KeyColumnsOverlappingIndex nvarchar(4000),
					  AmountofKeyColumns int )

-- Table that will be checked - cursor
declare c cursor for
SELECT distinct  table_name from #temp
open c
fetch next from c into @tablename
while @@FETCH_STATUS = 0
begin

	-- Index that will be checked - cursor
	declare D cursor for
	SELECT   index_name from #temp where table_name = @tablename
	open D
	fetch next from D into @index_name
	while @@FETCH_STATUS = 0
	begin	

			-- Other indexes on table cursor 
			declare F cursor for
			SELECT   index_name from #temp where table_name = @tablename and @index_name != index_name 
			open F
			fetch next from F into @index_name2
			while @@FETCH_STATUS = 0
			begin
				--Search for the first Column of other index
				set @searchstring= (
				SELECT case when charindex('^^',key_columns) = 0 then '' 
				else LEFT(key_columns,(charindex('^^',key_columns)-1)) end   
				FROM #temp p2  where  TABLE_NAME = @tablename and index_name = @index_name2 --get the first key column of  all other indexes
				FOR XML PATH(''))

				--Search for the first Column of own index
				select @searchcolumn = case when  (charindex('^^',key_columns)) = 0 then NULL else left(t1.key_columns,(charindex('^^',key_columns)-1)) end
				from #temp t1 where TABLE_NAME = @tablename and @index_name = index_name 

				--If it matches, proceed
				if (CHARINDEX(@searchcolumn,@searchstring)) > 0
				BEGIN
					insert into #indexing(tablename,indexname,overlappingindex,AmountOfColumnsMatching,AmountofKeyColumns ,KeyColumnsMatched,allkeycolumns,includedcolumns,keycolumnsoverlappingindex,includedcolumnsoverlappingindex)
					select @tablename,@index_name,@index_name2,1,(SELECT count(c.name)
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name
					and ic.key_ordinal > 0)
					,@searchcolumn, 
					(SELECT QUOTENAME(c.name)+','
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name
					and ic.key_ordinal > 0
					ORDER BY key_ordinal asc
					FOR XML PATH('')) ,
					(SELECT QUOTENAME(c.name)+','
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name
					and ic.key_ordinal = 0
					ORDER BY key_ordinal asc
					FOR XML PATH('')), (SELECT QUOTENAME(c.name)+','
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name2
					and ic.key_ordinal > 0
					ORDER BY key_ordinal asc
					FOR XML PATH('')) ,(SELECT QUOTENAME(c.name)+','
					FROM sys.indexes i
					inner join 
					sys.index_columns AS ic on ic.index_id = i.index_id and ic.object_id = i.object_id
					JOIN sys.columns AS c ON 
					ic.column_id=c.column_id  
					and ic.object_id=c.object_id
					WHERE 
					i.name = @index_name2
					and ic.key_ordinal = 0
					ORDER BY key_ordinal asc
					FOR XML PATH(''))

					--Check the second key columns
					set  @searchstring2 = (
					select case when charindex('^^',key_columns) = 0 then '' 
					when CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))) =0 then ''
					else 
					LEFT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)+1))))) end 
					FROM #temp p2  
					where  TABLE_NAME = @tablename and index_name = @index_name2 --get the first key column of  all other indexes
					FOR XML PATH(''))

					

					select @searchcolumn2 =  
					case when charindex('^^',key_columns) = 0 then ''
					else 
					RIGHT(t1.key_columns,(len(t1.key_columns)-(charindex('^^',key_columns)+1)))end 
					from #temp t1 where TABLE_NAME = @tablename and index_name = @index_name

					if CHARINDEX('^^',@searchcolumn2) >0
					BEGIN
						set @searchcolumn2 = LEFT(@searchcolumn2,(charindex('^^',@searchcolumn2)-1))
					END
					ELSE
					BEGIN
					set @searchcolumn2 = ''
					END

					--If the second key columns match, proceed
					if (CHARINDEX(@searchcolumn2,@searchstring2)) > 0
					BEGIN
					update 
					#indexing
					set 
					AmountOfColumnsMatching=2 ,
					KeyColumnsMatched = KeyColumnsMatched +','+@searchcolumn2
					where indexname=@index_name and
					overlappingindex= @index_name2

						--Check the third key columns
						set  @searchstring3 = (
						select case when charindex('^^',key_columns) = 0 then '' 
						when CHARINDEX('^^',RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))))) = 0 then ''
						else 
						LEFT(RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))-1)),(CHARINDEX('^^',RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))-1)))-1))
						end 
						FROM #temp p2  
						where  TABLE_NAME = @tablename and index_name = @index_name2 --get the first key column of  all other indexes
						FOR XML PATH(''))
			

						set  @searchcolumn3 = (
						select case when charindex('^^',key_columns) = 0 then '' 
						when CHARINDEX('^^',RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))))) = 0 then ''
						else 
						LEFT(RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))-1)),(CHARINDEX('^^',RIGHT(RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns)))),((len(key_columns)-(charindex('^^',key_columns))-CHARINDEX('^^',RIGHT(key_columns,(len(key_columns)-(charindex('^^',key_columns))))))-1)))-1))
						end 
						FROM #temp p2  
						where  TABLE_NAME = @tablename and index_name = @index_name --get the first key column of  all other indexes
						FOR XML PATH(''))

						--If the third key column matches, proceed
						if (CHARINDEX(@searchcolumn3,@searchstring3)) > 0
						BEGIN
						update 
						#indexing
						set 
						AmountOfColumnsMatching=3 ,
						KeyColumnsMatched = KeyColumnsMatched +','+@searchcolumn3
					where indexname=@index_name and
					overlappingindex= @index_name2

						END
					END
				END
			fetch next from F into @index_name2
			end
			close F
			deallocate  F
		 
	fetch next from D into @index_name
	end
	close D
	deallocate  D

fetch next from c into @tablename
end
close c 
deallocate  c

drop table #temp

select id,Tablename,Indexname,Overlappingindex,AmountOfColumnsMatching,AmountofKeyColumns,KeyColumnsMatched,AllKeyColumns,IncludedColumns,KeyColumnsOverlappingIndex,IncludedColumnsOverlappingIndex from #indexing

drop table #indexing