How to Identify Non-Active SQL Server Indexes
Once executed, the following report is obtained. For example:
TABLE: [ dbo].[APLICACIONES ]
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 ]
00.10 [ IXC03.05.16_AWItemsAcumHistoricos_CodItm_Fecha ]
TABLE: [ dbo].[BANCOSCOD ]
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.
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.