I was recently delivering session on Performance Tuning subject. I was asking if there is any harm having duplicate indexes. Of course, duplicate indexes are nothing but overhead on the database system. Database system has to maintain two sets of indexes when it has to do update, delete, insert on the table which has duplicate indexes. There is also a possibility that indexes are overlapped.

For example,

Index1 have Col1, Col2, Col3 but Index2 have Col1,Col2,Col3,Col4,Col5. Here Index1 and Index2 are overlapping and there is no need of Index1, which should be removed.

Following is the script which does the same task. You can run the script, get duplicate indexes and overlapping indexes. Carefully review each of them first and test this by deploying on your development server (not on production). If this script has any issue, do write back and I will do necessary modifications.

Hey Pinal – You don’t consider INCLUDEd columns, ASC/DESC, or nonclustered indexes that have the same keys as clustereds but are far narrower. Dangerous query to have people run. Left-based subsets the same doesn’t mean you can drop the wider one – they may satisfy vastly different queries and dropping the narrow one could seriously affect performance.

I’m interested in more information on this comment: “Left-based subsets the same doesn’t mean you can drop the wider one “. Using the example of two indexes (colA,colB,colC) and (colA,colB), assuming that everything else in the index is equal (includes, filters, etc) and that ColC is not very wide.

Can we say the index 2 is redundant because index 1 includes the same cols as index 2?

I’d like to know your ‘rule-of-thumb’ approach to this scenario as it’s fairly common.

Nice article. I tried to run this on my DEV instance and found this interesting thing. I am having same named table with same named primary key column in different Schema. This script display indexes on these columns as duplicates. I believe its not correct.

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;

— Script to get Overlapping Indexes
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
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'partialduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and (c1.cols like c2.cols + '%'
or c2.cols like c1.cols + '%') ;

Hope, it would be really help you to get exact/Overlapping duplicate indexes in any Database.
Note:- Please double check these scripts on Development server before executing it on Production server.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.