This stored procedure executes without parameters from the SQL Analyzer. The output is the report of the unused indexes.
BASE: EXAMPLE
TABLE: [ dbo].[APLICACIONES ]
INDICES:
[ IXC03.10.31_APLICACIONES_NroTrans ]
TABLE: [ dbo].[AWItemsAcumHistoricos ]
INDICES:
[ IX_AWItemsAcumHistoricosFecha ]
[ IX_AWItemsAcumHistoricosItem ]
[ IXC_AWItemsAcumHistoricos_Fecha_CodItm ]
TABLE: [ dbo].[CAJASREG ]
INDICES:
[ IXCP04.01.16_CAJASREG_CodCaj2_NroTrans ]
TABLE: [ dbo].[CHEQUES ]
INDICES:
[ IXC04.01.09_CHEQUES_FechaVto ]
[ IXCP04.01.16_CHEQUES_CodCtacte_NroTrans_Secuencia_NroTransegr_Tipo_Directo ]
Below is another stored procedure. It is designed to list the used indexes in our Profiler trace. We will use these results to let us know which indexes are very large.
CREATE PROCEDURE Indices_Usados AS
declare
it @Base varchar(15),
@Tabla varchar(50),
@Indice varchar(100),
@BaseAnt varchar(15),
@TablaAnt varchar(50),
@Uso varchar(50)
IndicesUsados declares for cursor
select Base, Table, Indice, to convert(varchar, max(convert(money, replace(Uso, ‘, ‘, ‘.’))))as Uso
from Analysis
where Indice like ‘ [ [ ]IX% ‘
group by Base, Table, Indice
having sum(convert(money, replace(Uso,’,’,’.’))) > 0
Order by Base, Table, to convert(varchar, max(convert(money, replace(Uso,’,’,’.’)))) desc, Indice
they open IndicesUsados
fetch next from IndicesUsados into @Base, @Tabla, @Indice, @Uso
while (@@FETCH_STATUS < > -1)
begin
if @Base < > @BaseAnt
begin
Print ‘ BASES: ‘ + @Base
select @BaseAnt = @Base
select @TablaAnt = ”
end
if @Tabla < > @TablaAnt
begin
Print ‘ TABLE: ‘ + @Tabla
Print ‘ INDICES: ‘
select @TablaAnt = @Tabla
end
if len(@Uso) = 4
begin
select @Uso = ‘ 0 ‘ + @Uso
end
Print @Uso + ‘ ‘ + @Indice
fetch next from IndicesUsados into @Base, @Tabla, @Indice, @Uso
continue
end
close IndicesUsados
deallocate IndicesUsados
GO