How To Identify Duplicate Indexes in SQL Server

Duplicate indexes can slow things down on a database. There is no need for them, so if you identify any get rid of them.

T-SQL Script that identifies duplicate indexes

The below script is pretty simple and you can use it as a beginning point for a duplicate index investigation.

— exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path(”)) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path(”)) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + ‘.’ + object_name(c1.id) as ‘table’,
c1.name as ‘index’,
c2.name as ‘exactduplicate’
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid and c1.cols = c2.cols and c1.inc = c2.inc; ---------------------------------------------- [/sql] Remember to dig deeper and make sure of what you are doing, its always a good idea to do some performance benchmarking as well. I found this query here

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.