Una de las tareas más comunes y necesarias durante el proceso de optimización y mantenimiento de las bases de datos es la desfragmentación de los índices, es así mismo quizá la tarea más olvidada por los administradores de bases de datos.
Los índices altamente fragmentados pueden afectar de manera negativa el rendimiento del motor de bases de datos e incluso causar que su aplicación no responda de la manera adecuada.
Fragmentación: Proceso mediante el cual el motor de base de datos debido a las constantes tareas de Insert, Update y Delete, a medida que estas instrucciones se van ejecutando dentro de nuestra base de datos, la misma sufre un proceso de dispersión de los datos, más conocida como fragmentación. La fragmentación ocurre cuando los índices tienen páginas que se encuentran ordenadas de forma lógica, y basándose en la llave estos no coinciden con el orden físico dentro del archivo de datos.
La fragmentación se puede solucionar mediante 2 opciones, reorganizar y/o reconstruir los índices, para los índices particionados esta tarea se puede ejecutar tanto en el índice completo como en la partición del mismo.
Reconstrucción del índice (Rebuild): Este proceso elimina y crea nuevamente el índice, remueve la fragmentación y recupera espacio en disco compactando las páginas basándose en la configuración del fill factor o en el parámetro de la instrucción.
Reorganización del índice (Reorganize): Este proceso requiere menos recursos del sistema y realiza la desfragmentación al nivel de la hoja de la página, reorganizando a nivel físico las hojas para que coincidan con el orden lógico de las mismas, la reorganización también compacta las páginas de los índices, esta se da basándose en la configuración del fill factor.
Detección de la fragmentación de los indices
Lo primero es determinar que método de desfragmentación usar, para esta tarea se puede utilizar la función sys.dm_db_index_physical_stats, esta nos devuelve la fragmentación de un índice, de los índices en una tabla, de los índices en una base de datos o de todos los índices en todas las bases de datos, de igual manera para los índices particionados, esta función nos devuelve el estado de cada una de las particiones asociadas al índice.
Columna
|
Descripción
|
avg_fragmentation_in_percent
|
Porcentaje de fragmentación lógica
|
fragment_count
|
Cantidad de fragmentos
|
avg_fragment_size_in_pages
|
Numero promedio de páginas en un fragmento de un índice.
|
Tenga en cuenta las siguientes recomendaciones para determinar si debe reorganizar o reconstruir su índice.
Porcentaje de fragmentación
|
Instrucción a ejecutar
|
Entre 5% y 30%
|
ALTER INDEX REORGANIZE
|
Mayor al 30%
|
ALTER INDEX REBUILD
|
Consulta para determinar el porcentaje de fragmentación (En toda la base de datos)
WITH INDICES (BD, INDICETIPO, FRAGMENTACION, INDICE, TABLA)
AS (
SELECT DBS.NAME BASEDEDATOS, PS.INDEX_TYPE_DESC, PS.AVG_FRAGMENTATION_IN_PERCENT,
IND.NAME INDICE, TAB.NAME TABLA
FROM
SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) PS
INNER JOIN SYS.DATABASES DBS
ON PS.DATABASE_ID = DBS.DATABASE_ID
INNER JOIN SYS.INDEXES IND
ON PS.OBJECT_ID = IND.OBJECT_ID
INNER JOIN SYS.TABLES TAB
ON TAB.OBJECT_ID = IND.OBJECT_ID
WHERE IND.NAME IS NOT NULL AND PS.INDEX_ID = IND.INDEX_ID
AND PS.AVG_FRAGMENTATION_IN_PERCENT > 0)
SELECT DISTINCT
CASE
WHEN FRAGMENTACION > 5 AND FRAGMENTACION <= 30 THEN 'ALTER INDEX ' + INDICE + ' ON ' + TABLA + ' REORGANIZE'
WHEN FRAGMENTACION > 30 THEN 'ALTER INDEX ' + INDICE + ' ON ' + TABLA + ' REBUILD'
END QUERY, FRAGMENTACION, BD, INDICE, TABLA
FROM (SELECT FRAGMENTACION, INDICE, TABLA, BD FROM INDICES
WHERE FRAGMENTACION > 5) A
ORDER BY FRAGMENTACION DESC
Los índices pueden ser reconstruidos en línea o fuera de línea, la reorganización siempre se da en línea, para mantener niveles de disponibilidad similares a la de los índices reorganizados, la reconstrucción debe darse en línea y mediante la instrucción.
ALTER INDEX REBUILD WITH (ONLINE = ON)
