MANTENIMIENTO: Reorganización de Índices – Actualización de Estadísticas

Dentro de las tareas habituales de Mantenimiento de las Bases de Datos se encuentran aquellas destinadas al control y respaldo de las mismas como ser: Control de Integridad, Chequeo de Consistencia, Copias de Seguridad o Compactación de las bases.

Pero también es necesario ejecutar trabajos de mantenimiento cuyos objetivos sean el de mantener la performance de las bases de datos y evitar su degradación.

Esos trabajos son la Reorganización de Índices y la Actualización de Estadísticas.

Estos trabajos son independientes del estado de la base de datos. Puede ocurrir que a la base le falten estudios de optimización pero, al menos, mantendremos la performance  actual.

Si la base se encuentra optimizada, entonces más aún, son necesarios para evitar la degradación producto del uso continuo.

Cualquiera de estos trabajos deben realizarse fuera de línea por motivos de: alto consumo de recurso y bloqueo de las tablas en el momento de ejecución.

Por lo tanto debemos agendarlos en horarios, en donde las bases de datos se encuentren libres de cualquier actividad de producción.

La frecuencia de ejecución de estos trabajos depende básicamente de dos factores: tamaño de la base y tiempo libre para ejecutarlos.

La frecuencia ideal sería reorganizar todos los índices de todas las tablas una vez por semana y, de aquellas tablas más accedidas, una vez entre semana. La misma frecuencia sería deseable para la actualización de las estadísticas.

Reorganización de Índices

Las tablas que contienen índices al ser actualizadas o por inserción de nuevos datos, generan fragmentación de estos índices. Estas fragmentaciones conllevan a la pérdida de performance al acceder a ellas.

La instrucción DBCC DBREINDEX reorganiza el índice de una tabla o todos los índices definidos para una tabla.  La reorganización de realiza dinámicamente sin necesidad de conocer la estructura de la misma o las restricciones que ella tenga. Por lo tanto no es necesario conocer si una tabla tiene clave primaria o si esta clave es única y además pertenece a algún índice, ya que la reorganización no necesita eliminar y recrear éstas restricciones para realizar su trabajo.

La sintaxis de esta instrucción es:

DBCC DBREINDEX
    (    ’basededatos.dueño.nombre_de_tabla‘    
            [ , índice
                [ , fillfactor ]
            ]
    )    [ WITH NO_INFOMSGS ]

Fillfactor es el porcentaje de espacio de página destinado a ser ocupado. El valor definido reemplaza al que fue generado en el momento de la creación del índice. Si se quiere mantener el valor original, entonces se utiliza el valor 0.

WITH NO_INFOMSGS se suprimen los mensajes generados en la ejecución.

No es necesario conocer los nombres de todos los índices de todas las tablas, ya que si utilizamos la instrucción de la siguiente forma:

DBCC RBINDEX (Movimientos, ‘’, 0)

Se reorganizarán todos los índices que contengan la tabla Movimientos, conservándose el fillfactor original de cada índice en particular.

Una de las formas de utilizarlo es, escribir un script con una sentencia DBCC RBINDEX por cada tabla que necesitemos reorganizar y agendarlas en forma periódica mediante un trabajo de mantenimiento dentro de algún horario disponible.

Por lo tanto, la recomendación será: elegir las tablas más accedidas y/o actualizadas, y reorganizarlas una vez entre semana. Para reorganizar todas las tablas que contengan índices se utiliza el mismo concepto, pero dentro de un procedimiento que recorra todas la tablas de la base y las reorganice, sin necesidad que escribamos todas la tablas que contiene la base de datos. Estos procedimientos se pueden encontrar en el Forum bajo el nombre de  Tips, y la idea es generar un trabajo de mantenimiento que se ejecute, por ejemplo, en el fin de semana.

Esta instrucción es utilizable tanto en SQL SERVER versiones 7 y 2000.  Una instrucción similar, con la ventaja de poder ejecutarse en línea es DBCC INDEXDEFRAG pero no se encuentra disponible en la versión 7.

Actualización de Estadísticas

Esta tarea permite actualizar la información sobre la distribución de los valores de las claves, para una o un grupo de estadísticas en una tabla o vista.

Esto podría realizarse en forma automática dado que, en las propiedades de la base de datos, existe la opción de Actualización de Estadísticas. Pero no es recomendable que esta opción se encuentre habilitada, ya que degrada la performance de la base de datos que se encuentra en producción y en uso diario.  (La opción que debe estar habilitada es la de Crear Estadísticas Automáticamente, esta no consume recursos y es fundamental para mejorar la performance).

Es por ello que es conveniente realizar este trabajo fuera de línea, es decir en horarios libres de uso de la base.

Si se analiza el trabajo de mantenimiento Asistente para Planes de Matenimiento de Bases de Datos , se encuentra la opción: Actualizar las Estadísticas para el Optimizador de Consultas con un muestreo  10%. Esto implica que las tablas serán recorridas solamente un 10% de todos sus datos.

Esta opción la podemos utilizar para la actualización semanal (fin de semana), cambiando el valor de 10% a 100% para asegurarnos que se recorran todos los datos que contienen todas las tablas.

Como esta tarea tomará muchas horas, dependiendo del tamaño de la base, no podemos utilizarla diariamente. Pero, de la misma forma que con la reorganización, podemos elegir las tablas más importantes y actualizar las estadísticas de las mismas en forma diaria y fuera de horario normal.

Para ello utilizamos la siguiente instrucción simplificada en su sintaxis:

UPDATE STATISTICS nombre_de_tabla WITH FULLSCAN

Con la opción FULLSCAN nos aseguramos que todos los datos de la tabla sean recorridos, es equivalente al 100% definido en el párrafo anterior, con la diferencia que aquí se trata de una tabla o vista en lugar de todas.

Por lo tango generamos un script con una instrucción UPDATE STATISTICS por cada tabla que necesitemos. Este script lo ejecutamos mediante un trabajo de mantenimiento y lo agendamos con frecuencia diaria.

Resumen

La reorganización de índices también actualiza las estadísticas sobre la tabla reorganizada.

Consecuentemente el día de semana que reorganizamos todos los índices, no es necesario actualizar las estadísticas.

Como sugerencia general con el objetivo de mantener la performance se recomienda: Reorganización de Índices completa, una vez por semana en días no laborables y de aquellas tablas más importantes una vez en la semana. Actualización de Estadísticas completa una vez por semana y de las tablas más importantes todos los días laborables.




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 |