· Maintaining duplicate indexes increases your overhead associated with inserts, updates, and deletes.
· Reindexes/Reorgs of duplicate indexes takes valuable time out of your maintenance window.
· Increase storage costs since you have two of the same thing
You can identify duplicate indexes in your database by running the following query (note: you must be in the database you wish to run it on).
with AllIndexes (objectname, indexname, keyno, columnname)
as
( select so.name as objectname, si.name as indexname, sik.keyno, sc.name as columnname
from sysindexes si
inner join sysobjects so on so.id = si.id
inner join sysindexkeys sik on sik.id = so.id and sik.indid = si.indid
inner join syscolumns sc on sc.id = so.id and sc.colid = sik.colid
where so.xtype = 'u')
select objectname, indexname, [1] fld1, [2] fld2, [3] fld3, [4] fld4, [5] fld5, [6] fld6, [7] fld7, [8] fld8, [9] fld9, [10] fld10
into #temp
from (select objectname, indexname, keyno, columnname from AllIndexes) t
pivot (max(columnname) for keyno in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) as pvt
select t1.objectname, t1.indexname, t2.indexname
from #temp t1
inner join #temp t2 on
t1.objectname = t2.objectname and
t1.indexname < t2.indexname and
isnull(t1.fld1, '') = isnull(t2.fld1, '') and
isnull(t1.fld2, '') = isnull(t2.fld2, '') and
isnull(t1.fld3, '') = isnull(t2.fld3, '') and
isnull(t1.fld4, '') = isnull(t2.fld4, '') and
isnull(t1.fld5, '') = isnull(t2.fld5, '') and
isnull(t1.fld6, '') = isnull(t2.fld6, '') and
isnull(t1.fld7, '') = isnull(t2.fld7, '') and
isnull(t1.fld8, '') = isnull(t2.fld8, '') and
isnull(t1.fld9, '') = isnull(t2.fld9, '') and
isnull(t1.fld10, '') = isnull(t2.fld10, '')
drop table #temp
No comments:
Post a Comment