How to Identify Non-Active SQL Server Indexes

Once executed, the following report is obtained. For example:

BASE: EXAMPLE

TABLE: [ dbo].[APLICACIONES ]

INDICES:

79.20 [ IXCY04.05.03_APLICACIONES_NroTransegr_NroTrans_RefVto ]

33.30 [ IXCY04.05.03_APLICACIONES_Aplicaciones_NroTransegr_RefVto_NroTrans_FechaVto_Importe ]

20.50 [ IXC03.11.24_APLICACIONES_NroTransegr_AplNrotrans_AplRefvto ]

02.10 [ IXCP03.11.12_APLICACIONES_Nrotrans_NroTransegr_Importe_FechaVto_AplrefVto ]

100.00 [ IXC04.05.27_APLICACIONES_NroTrans_NroTranselim_AplNroTrans_FechaVto_AplRefvto ]

00.10 [ IXC04.05.27_APLICACIONES_AplNrotrans ]

00.10 [ IXCY04.05.07_APLICACIONES_AplNrotrans_AplRefvto_FechaVto_NroTrans_NroTransing ]

TABLE: [ dbo].[AWItemsAcumHistoricos ]

INDICES:

00.10 [ IXC03.05.16_AWItemsAcumHistoricos_CodItm_Fecha ]

TABLE: [ dbo].[BANCOSCOD ]

INDICES:

100.00 [ IXCY04.05.14_BANCOSCOD_CodBan_Descripcion ]

Both results are important. The first, because it tells us which indexes are not being used. The second, because it lists the index sizes of each of the indexes, perhaps giving us a clue as to any indexes that should be more carefully examined. Very large index sizes may indicate a potential for a slowdown.

Summary

Once you have deleted all unnecessary or overly large indexes, you will want to keep a close watch on your database’s performance to ensure that an index you deleted was really needed, but is used so rarely that is did not show up in your trace.

Copyright by the author.

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 |