Duplicate indexes
SQL Server curiously allows duplicate indexes to be created on a table with precisely identical definitions. There is absolutely zero benefit from this practise yet significant harm can result - including:
- Performance degradation from maintenance overhead during updates, inserts & deletes
- Increased transaction log activity (filling tlogs, disks & breaking Log Shipping)
- Increased backup sizes (indexes make backups bigger, requiring more disk space, longer maintenance windows & consuming more energy during backups)
Other major RDBMS platforms don't allow this nonsense yet MS apparently don't consider fixing this terribly important, given their response in this Connect item
I began a discussion about this topic in the SQL MVP dicussion forum last week & I think there was a fair consensus that something should be done. Fellow MVPs who work in performance tuning commented that they encounter duplicate indexes commonly in their work (I see it regularly). If you agree MS should do something to fix this, I encourage you to vote on this Connect item to try & influence change:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=313227
If you are wondering whether you have any duplicate indexes on your system, try this procedure out. Any duplicates listed should almost certainly be removed (unless they're explicitly referred to in query index hints, which would break if you remove the named indexes)
To use this proc, create it in the master database then call it using this syntax from within your db:
exec sp_MSForEachTable 'usp_duplicateindexes''?'''
Here's the proc script:
USE master
GO
create procedure [dbo].[usp_duplicateindexes]
@tablename varchar(255)
as
set nocount on
print @tablename
--dump sp_helpindex into temp table
if object_id('tempdb..#helpindex') > 0 drop table #helpindex
create table #helpindex (
index_name varchar (900) not null primary key
, index_description varchar (1000) null
, index_keys varchar (1000) null)
insert #helpindex exec sp_helpindex @tablename
--add [inccols] to temp table & cursor over output, adding included col defs
alter table #helpindex add inccols varchar(1000) null
declare cr cursor for
select si.name, sc.name
from sysobjects so
join sysindexes si on so.id = si.id
join sys.index_columns ic on si.id = ic.object_id and si.indid = ic.index_id
join sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id
where so.xtype = 'U'
and so.name = @tablename
and ic.is_included_column = 1
order by si.name, ic.index_column_id
declare @siname varchar(1000), @scname varchar(1000)
open cr
fetch next from cr into @siname, @scname
while @@fetch_status = 0
begin
update #helpindex set inccols = isnull(inccols , '') + @scname + ', ' where index_name = @siname
fetch next from cr into @siname, @scname
end
update #helpindex set inccols = left(inccols, datalength(inccols) - 2) where right(inccols, 2) = ', '
close cr
deallocate cr
--dump duplicates into second temp table & pump results if there are any
if object_id('tempdb..#helpindex2') > 0 drop table #helpindex2
create table #helpindex2 (
index_name varchar (900) not null primary key
, index_description varchar (1000) null
, index_keys varchar (1000) null
, inccols varchar(1000) null
)
insert into #helpindex2
select hi.index_name, hi.index_description, hi.index_keys, hi.inccols
from #helpindex hi
join #helpindex h2 on hi.index_keys=h2.index_keys
and hi.index_description=h2.index_description
and hi.index_name<>h2.index_name
if @@rowcount > 0
select @tablename as "tablename", * from #helpindex2 order by index_name, index_keys, inccols
--cleanup temp objects
if object_id('tempdb..#helpindex2') > 0 drop table #helpindex2
if object_id('tempdb..#helpindex') > 0 drop table #helpindex