SQL – Identify & Drop Duplicate Index

This post explains the simple steps to identify a duplicate indexes in all the databases.  The duplicate index are more than one indexes that differ only by it’s name but consist of identical fields internally. It generates overhead when performing updates, inserts or deletes, require lot of resources for rebuilds, take up space but are never used. Its always good cleanup.

Output of the below T-SQL are direct eligible items to delete. Please double check output before deleting any indexes.

Download the code here Duplicate_Index

Copy and paste the below code and execute it through SSMS

****************************

DECLARE @DBName [nvarchar] (128)
,@RowID [int]
,@LoopStatus [int]
,@DML varchar(max)

SET @RowID=1
SET @LoopStatus=1

DECLARE @DuplicateIndexFind TABLE
(
[table] [nvarchar](257) NULL,
[index] [sysname] NULL,
[exactduplicate] [sysname] NULL,
[DbName] varchar(100) NULL
)

DECLARE @DatabaseList TABLE ([RowNo] [smallint] identity (1, 1)
,[DBName] [varchar](200))

INSERT INTO @DatabaseList
SELECT ‘[‘+[name]+’]’ FROM [master].[sys].[databases] WITH (NOLOCK)
WHERE [state_desc] = ‘ONLINE’
AND [source_database_id] IS NULL
AND [database_id] > 4

WHILE @LoopStatus<>0
BEGIN
SELECT @DBName = [DBName]
FROM @DatabaseList WHERE [RowNo] = @RowID
IF @@ROWCOUNT=0
BEGIN
SET @LoopStatus=0
END
ELSE
BEGIN
SET @DML=’USE ‘+ @DBName +CHAR(13)+’;’+ ‘
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 ‘+ @DBName +’.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 ‘+ @DBName +’.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 ‘+ @DBName +’.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
order by object_schema_name(c1.id) + ”.” + object_name(c1.id)’

INSERT INTO @DuplicateIndexFind([table] ,[index],[exactduplicate]) exec (@DML)

update @DuplicateIndexFind
set DbName=@DBName
where DbName is NULL

SET @RowID=@RowID+1
END
END

select @@Servername ServerName,DbName DatabaseName,[Table],[INDEX],ExactDuplicate from @DuplicateIndexFind

*************************************************************

Cross verify output using the below statement

sp_helpindex ‘pl_collect_def’

Output:-

duplicate_index

Thanks for reading my space.

Happy Learning!!

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, T-SQL and tagged , , , . Bookmark the permalink.

4 Responses to SQL – Identify & Drop Duplicate Index

  1. Steve Hood says:

    I have something very similar and was trying to run this one to see how mine held up against it, but I wasn’t able to get it to work. Part of this could be me trying to get all of the ` changed to ‘, etc. There’s actually a post out there that has a program to copy paste better by altering the contents of your clipboard from formatted text to clean HTML in preformatted tags I use. The post is here: http://www.jeffblankenburg.com/2008/10/07/tutorial-6-visual-studio-code-highlighting-to-html/

    As for mine, I actually take it a step further than yours. Instead of looking at 100% duplicates, I have a variable at the beginning where you’re looking for any indexes that have the first X number of key fields the same and in the same order. Chances are if you have the first two or three key fields the same then you can combine indexes and reduce the overall load. Check it out here and let me know what you think: http://simplesqlserver.com/2013/05/16/indexes-unused-and-duplicates/

    I love the way you do the columns, which is something that I never changed mine to handle that way, so yours definitely is one up on me there.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s