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. 



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)