Redundant Indexes | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Redundant Indexes

I use Query Analyzer, one statement at a time PLEASE: Verify before drop any index The output is: tab, idx, "contained index", "index that contains" USE …. — step 1: get tab,idx,col,order
create view listaidxcols as
select SO.name as tabname,
SI.name as idxname,
IK.keyno as keyno,
SC.name as colname
from sysindexkeys IK,
syscolumns SC,
sysindexes SI,
sysobjects SO
where — Liga syscolumns
IK.id=SC.id
and IK.colid=SC.colid
— Liga sysindexes
and IK.id=SI.id
and IK.indid=SI.indid
— Liga a sysObjects (tablas)
and IK.id=SO.id
and SO.xtype=’U’
and SI.name not like ‘_WA_Sys_%’
and SI.name not like ‘hind_%’ –step 2 get # of cols x index
create view cantcolsidx
as select tabname,
idxname,
count(*) as numllaves
from listaidxcols
group by tabname,idxname –step 3 get redundant index list
select A.tabname as tabla,A.idxname as Aidx, B.idxname as Bidx
from cantcolsidx A, cantcolsidx B
where A.tabname = B.tabname
and A.numllaves < B.numllaves
and A.idxname <> B.idxname
and A.numllaves in (
select count(*)
from listaidxcols C, listaidxcols D
where C.tabname=A.tabname
and C.idxname=A.idxname
and D.tabname=B.tabname
and D.idxname=B.idxname
and C.idxname<>D.idxname
and C.colname=D.colname
and C.keyno =D.keyno
) — step 4 clean up
drop view listaidxcols;
drop view cantcolsidx;
(Moved from Contribute SQL Scripts forum)
tabla Aidx Bidx Alquiler PK_Alquiler IX_Alquiler_CodAlq_Estado
Alquiler PK_Alquiler IX_Alquiler_CodAlqFechasAge This is my output, but there is no redundant indexes.
I missing something?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
please post the table script.
I suspect, that the PK is a clustered index and the other indexes normal indexes
that share fields
Made some small changes to the script, to use temporary tables instead of creating and dropping views … In one of our databases, it returns one result: Aidx: a non-clustered index on a single field that does not have a unique constraint.
Bidx: the primary key constraint, which is a clustered index on three fields, and the first field is the one that is covered by the other index. That’s the kind of result you were hoping to get, right? You should make more intentional design mistakes on tables to see if it picks them out as well.
]]>