SQL Server: Fragmentación y desfragmentación de índices (es-ES)

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)