Mantenimiento de Indices en SQL y recomendacionesAndrés Esteban Tovar AcevedoCPOIndigo Technologies SAS

Conceptos Generales y Tipos de Fragmentación

Los índices son los únicos objetos que pierden su efectividad con el paso del tiempo si no se le da un mantenimiento adecuado.

La fragmentación de los índices tiene lugar cuando se modifican los registros de una tabla por inserción, update o borrado de datos y estas modificaciones afectan una o más páginas delíndice.

Hay dos tipos de fragmentación a nivel de índice, ambas afectan directamente la performance de los procesos que utilizan los mimos.

* Fragmentación Interna y Fragmentación Externa

Detección y Evaluación de la Fragmentación

A partir del Sel Server 2005 fue reemplazada la utilidad DBCC SHOWCONTIG por una vista del sistema llamadaa SYS.DM_DB_INDEX_PHYSICAL_STATS , la cual arroja datos más certeros y consume menos recursos del sistema en su ejecución.

Niveles de fragmentación:

a. Fragmentación Inócuab. Fragmentación bajac. Fragmentación mediad. Fragmentación alta

Métodos de Desfragmentación

Hay varios métodos para desfragmentar índices, es importante elegir el método adecuado acorde al entorno de aplicación del mismo.

1) Alter Index Reorganize

• Se recomienda usar este método cuando el % de fragmentación es de > % 5 y < = al %30.:• Se utiliza la instrucción ALTER INDEX con la clausula REORGANIZE. Esta instrucción reemplaza a la vieja DBCCINDEXDEFRAG• La reorganización se realiza en línea ya que no mantiene grandes bloqueos• Este proceso utiliza una mínima cantidad de recursos del sistema• Básicamente se desfragmentan los índices, se compactan las páginas vacías acordes al valor de fill factor y reordenan la páginas de índices a nivel físico, para que coincidan con el ordenamiento a nivel lógico.• Para reorganizar las páginas de un índice particionado en una de las particiones, se be utilizar la cláusula PARTITION• El reorganizar un índice No regenera las estadísticas.

2) Alter Index Rebuild / Create Index with Drop_Existing = ‘on’

• Se regenera el índice en su totalidad y, se hace un update de las estadísticas• Es una operación que demanda muchos recursos del sistema puesto que el motor de base de datos requiere el doble de espacio del que ocupa el índice para crear primero el índice nuevo y luego borrar el viejo. Además se toma un espacio adicional para hacer esta operación en el disco salvo que se le indique hacer el ordenamiento en la TempDb• Se usa sólo para reconstruir índices cuyo porcentaje de fragmentación esté por sobre el % 30• Puede ser realizada en línea excepto que el índice esté sobre columnas de tipo LOB (image, text, nvarchar, xml, varchar(max)), o que sean índices XML• Se puede hacer un Rebuild de un índice con la cláusula PARTITION, sólo en el caso del método Alter Index

3) Disabling Indexes

• Es el método de reconstrucción total de un índice que menos recursos consume.• Al deshabilitar un índice se impide que el usuario tenga acceso al mismo y a las tablas subyacentes. Esto hace que solo pueda ser usado en ambientes que permitan un downtime para llevar a cabo la operación.• No requiere a diferencia de la sentencia Rebuild del doble de espacio en disco que usa el índice, puesto que la definición del índice se conserva en los metadatos eliminándose físicamente el mismo. (para ello deben deshabilitarse todos los índices clustered y luego, en otra transacción, se debe implementar un Rebuild de todos los índices)

1. Comprobar los registros de eventos del S.O. y los registros de SQL Server.2. Verificar que todos los trabajos programados se han ejecutado correctamente.3. Confirmar que las copias de seguridad se han hecho y guardado con éxito.4. Monitorear el espacio en disco para asegurarse de que el Servidor de SQL no se quedarán sin espacio en disco. Para un mejor rendimiento, todos los discos deben tener 15% o más de espacio libre.5. A lo largo del día, hacer un seguimiento periódico de rendimiento utilizando Monitor de sistema y de perfiles / Traza de SQL.6. Monitorear regularmente e identificar problemas de bloqueo.7. Mantener un registro de los cambios que realice a los servidores, incluyendo la documentación de los problemas de rendimiento a identificar y corregir.8. Crear alertas de SQL Server para que le avise de posibles problemas, y las envíe por mail. Tomar las medidas necesarias.9. Regularmente restaurar copias de seguridad en un servidor de prueba para verificar que realmente los puede restaurar.