SQL Server: Script to find Redundant and Duplicate Indexes

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am going to share one T-SQL script to find duplicate and redundant Indexes of the SQL Server.

I have found some of the junior and intermediate Database Developers, who are creating multiple duplicates indexes which are decreasing the overall performance of the database.

Now the job of the Database Administrator is to find and remove duplicate indexes from the database.

I have prepared a small demonstration by creating some of duplicate indexes and provided script to find that duplicate indexes.

First, create a sample table:

CREATE TABLE dbo.tbl_Students

(

StudID INT IDENTITY(1,1)

,FirstName VARCHAR(50)

,LastName VARCHAR(50)

,DOB DATETIME

,CONSTRAINT pk_tbl_Students_StudID PRIMARY KEY(StudID)

)

Create duplicate indexes on LastName column:

CREATE NONCLUSTERED INDEX idx_tbl_Students_LastName_FirstName_DOB

ON dbo.tbl_Students (LastName,FirstName,DOB)

CREATE NONCLUSTERED INDEX idx_tbl_Students_LastName_FirstName

ON dbo.tbl_Students (LastName,FirstName)

CREATE NONCLUSTERED INDEX idx_tbl_Students_LastName

ON dbo.tbl_Students (LastName)

Sample SELECT statements:
Below, both SELECT statements are using only one index (idx_tbl_Students_LastName_FirstName_DOB) because LastName column is duplicated in all the indexes.

SELECT *FROM dbo.tbl_Students WHERE LastName ='Patel' AND FirstName = 'Anvesh'

SELECT *FROM dbo.tbl_Students WHERE LastName ='Patel'

Script to find Duplicate Indexes:

SELECT

s.Name + '.' + t.Name AS TableName

,i.name AS IndexName1

,DupliIDX.name AS IndexName2

,c.name AS ColumnNae

FROM sys.tables AS t

JOIN sys.indexes AS i

ON t.object_id = i.object_id

JOIN sys.index_columns ic

ON ic.object_id = i.object_id

AND ic.index_id = i.index_id

AND ic.index_column_id = 1

JOIN sys.columns AS c

ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

JOIN sys.schemas AS s

ON t.schema_id = s.schema_id

CROSS APPLY

(

SELECT

ind.index_id

,ind.name

FROM sys.indexes AS ind

JOIN sys.index_columns AS ico

ON ico.object_id = ind.object_id

AND ico.index_id = ind.index_id

AND ico.index_column_id = 1

WHERE ind.object_id = i.object_id

AND ind.index_id > i.index_id

AND ico.column_id = ic.column_id

) DupliIDX

ORDER BY

s.name,t.name,i.index_id

The Result:

Nov 2, 2016Anvesh Patel

Comments: 3

Debiprasad Panda

March 5, 2018 at 11:55 am

HI Anvesh,

Nice article to start with finding out duplicate indexes.
But after reading this article , there were some questions popped up in my mind.

IndexA(Column1,Column2)
IndexB(Column1,Column2,Column3,Column4,Column5)
IndexC(Column2,Column3)
IndexD(Column1,Column3)
IndexE(Column1,Column4)

After completing this article , i am thinking that the duplicate indexe is IndexA.
But in your example , you have mentioned that IndexD ,IndexE along with IndexA are redundant.

Please reply.

Thanks in Advance.

Reply Cancel

Anvesh Patel
March 5, 2018 at 6:00 pm
First, we should check the execution plan of the query and then remove the same column from multiple indexes or similar indexes.
Reply Cancel

Laura Lefebvre

October 10, 2018 at 6:44 pm

There is an error in your query. In SQL Server, the ‘sys.index_columns.key_ordinal’ is the column which indicates the order of the columns within the index. In your query, I am assuming that this clause:

AND ic.index_column_id = 1

was meant to only look at columns which are first in the index. This should actually be:

AND ic.key_ordinal = 1

So the resulting query is this:

SELECT
s.Name + ‘.’ + t.Name AS TableName
,i.name AS IndexName1
,DupliIDX.name AS DuplicateIndexName
,c.name AS ColumnName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id — index belongs to this table
JOIN sys.index_columns ic
ON ic.object_id = i.object_id — columns for this index
AND ic.index_id = i.index_id — index to which column belongs
AND ic.key_ordinal = 1 — only want the first column in the index
JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id — to get the name of the first indexed column
JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
CROSS APPLY
(
SELECT
ind.index_id
,ind.name
FROM sys.indexes AS ind
JOIN sys.index_columns AS ico
ON ico.object_id = ind.object_id
AND ico.index_id = ind.index_id
AND ico.key_ordinal = 1
WHERE ind.object_id = i.object_id
AND ind.index_id > i.index_id — exclude the clustered index (which is the PK usually)
AND ico.column_id = ic.column_id
) DupliIDX
ORDER BY
s.name,t.name,i.index_id,c.column_id
GO

Reply Cancel

SQL Server: Script to find Redundant and Duplicate Indexes

Leave a Reply Cancel reply

Anvesh Patel

About Me!

About DBRND !

Recent Comments !

Follow Me !