Fragmentación y desfragmentación de índices

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)