Eliminación de Índices no Usados

By : Luis Martin Mar 30, 2005

Printer friendly

La creación de índices se realiza tanto analizando los planes de ejecución o utilizando el ITW (Asistente para la Optimizacion de Indices).

Una vez que la base de datos se encuentra estabilizada en su performance, es probable que encontremos nuevos índices, producto de cambios en el software de aplicación o por el uso de nuevas funciones del mismo.

También es probable que los nuevos índices conlleven a que alguno o varios de los anteriormente creados no se utilicen más. Dado que los índices ocupan espacio en la base, resulta conveniente eliminarlos.

La pregunta es: cómo sabemos cuáles índices son utilizados y cuáles no. En este punto SQL no ofrece mucha ayuda automatizada, y es necesario realizar trabajos manuales.

El método aquí propuesto es simple, pero exige tiempo y paciencia para lograr el objetivo.

Metodología

La primera herramienta que necesitamos es el Analizador de SQL (Profiler). Con ella debemos recolectar las actividades que se realizan en la base durante al menos 4 o 5 días. Esto no es absoluto, trataremos de buscar los días de mayor actividad en el sistema y los días críticos.

Los eventos que debemos coleccionar son:

Procedimientos Almacenados

RPC:Completed

SP:SmtmCompleted

TSQL

SQL:BatchCompleted

SQL:StmtCompleted

Con respecto a las columnas las recomendables son:

ApplicationName, EventClass, TextData, Duration, LoginName, Reads y  NTUserName

A los efectos de generar colecciones útiles es recomendable filtrar la Duración de las transacciones mediante un valor razonable, por ejemplo mayores o iguales a 50 milisegundos.

Una vez que disponemos de la colección recogida durante los días definidos, la siguiente tarea es dividirla en una colección por LoginName. Por lo tanto tendremos un archivo de transacciones por cada usuario que utiliza el sistema.

Generación del Informe de Uso de Índices

Una vez realizada esta separación, ejecutamos el ITW por cada usuario con modo de optimización: Exhaustivo. Al finalizar encontraremos el informe sobre el uso en Análisis. El reporte Informe de Uso de Índices (Configuración Recomendable), nos informa sobre los índices usados y no usados como, por ejemplo, el grafico siguiente:

Este informe lo guardamos en formato txt.

Por lo tanto obtendremos un reporte txt por cada archivo de trace que hemos utilizado.

El siguiente paso es obtener un reporte consolidado con todos los txt generados. Para ello bastara utilizar un editor de texto y juntar en uno solo todos los anteriores. Es necesario remover la primera linea de cada reporte, ya que contiene los titulos de cada columna.

Veamos un pequeño subconjunto de ese reporte:

[dbo].[MVSITEMS] [IXCP03.11.17_MVSITEMS_NroTrans] 1,01596168

[dbo].[TRANSAC] [PK_TRANSAC]  3,2734424

[dbo].[LISTASPRECIOS] [LISTASPRECIOS_CODLIS]  17,6  221712

[dbo].[AWItemsAcumHistoricos] [IXC03.05.16_AWItemsAcumHistoricos_CodItm_Fecha]  0,1  144568

[dbo].[CMPASOCIADOS] [IXC03.10.31_CMPASOCIADOS_NroTrans]  2,2  97016

[dbo].[MVSCAJA] [IXC03.10.31_MVSCAJA_NroTrans] 0,2 91848

[dbo].[TRANSACREG] [PK_TRANSACREG] 0,0  83432

[dbo].[APLICACIONES] [IXC03.10.31_APLICACIONES_NroTrans]  0,0 77272

[dbo].[CHEQUES] [IXC03.11.26_CHEQUES_NroTransegr_NroTrans]  0,1 70072

[dbo].[VENCIMIENTOS] [PK_VENCIMIENTOS]  0,1  59232

[dbo].[ITEMSATRIB] [PK_ITEMSATRIB] 0,0  48328

[dbo].[VENCIMIENTOSREG] [PK_VENCIMIENTOSREG]  0,0 36184