How to Identify Non-Active SQL Server Indexes

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

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |