Fragmentación en SQL Server: diagnóstico y desfragmentación de índices

Detecta fragmentación y desfragmenta índices en SQL Server: reorganiza (5–30 %) o reconstruye (>30 %), mantén estadísticas y automatiza.

< Todos los temas
Imprimir
Fragmentación en SQL Server: diagnóstico y desfragmentación de índices

Fragmentación en SQL Server: diagnóstico y desfragmentación de índices

Resumen. La fragmentación ocurre cuando el orden lógico de un índice no coincide con su orden físico en disco. Esto incrementa lecturas y latencias, afectando el rendimiento. La solución es reorganizar o reconstruir (rebuild) los índices y mantener estadísticas actualizadas.

1) Diagnosticar la fragmentación

Ejecuta en la base objetivo (ajusta el nombre si filtras por DB):

 
-- Fragmentación por índice (omite estructuras pequeñas)
SELECT
DB_NAME() AS database_name,
OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
ips.page_count,
CAST(ips.avg_fragmentation_in_percent AS decimal(5,2)) AS frag_pct,
ips.index_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count >= 1000
ORDER BY frag_pct DESC, page_count DESC;

Umbrales recomendados (guía práctica):

  • < 5 % → No hacer nada.

  • 5–30 % → ALTER INDEX … REORGANIZE.

  • > 30 % → ALTER INDEX … REBUILD (ideal fuera de horas pico).

2) Desfragmentar índices

Opción A — Reorganizar (operación ligera, en línea)

 
ALTER INDEX [NombreDelIndice] ON [Esquema].[Tabla]
REORGANIZE WITH (LOB_COMPACTION = ON);
-- Tras REORGANIZE, actualiza estadísticas:
UPDATE STATISTICS [Esquema].[Tabla] [NombreDelIndice] WITH RESAMPLE;

Opción B — Reconstruir (rebuild) (más efectiva)

 
ALTER INDEX [NombreDelIndice] ON [Esquema].[Tabla]
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, ONLINE = ON); -- ONLINE si tu edición lo permite
-- Nota: REBUILD ya actualiza estadísticas con FULLSCAN

Sugerencias:

  • Usa ONLINE = ON si tu edición/licencia lo permite para minimizar bloqueos.

  • SORT_IN_TEMPDB = ON reduce presión sobre la base; asegúrate de tener espacio en tempdb.

  • Define FILLFACTOR si hay mucha inserción/actualización.

3) Plan de mantenimiento (automatizado)

  1. Crea un Job de SQL Agent nocturno.

  2. Paso 1: script de diagnóstico que guarda resultados en una tabla de control.

  3. Paso 2: REORGANIZE para índices 5–30 %.

  4. Paso 3: REBUILD para índices >30 % (ventana de baja carga).

  5. Paso 4: UPDATE STATISTICS para tablas con mucha modificación (si solo reorganizaste).

  6. Paso 5: Verificación y notificación (correo) con top índices aún fragmentados.

4) Verificar mejoras

 
-- Repite la consulta de diagnóstico y compara frag_pct antes/después

5) Consideraciones y riesgos

  • Crecimiento del log: en FULL recovery, asegúrate de espacio y políticas de backups de log.

  • Bloqueos: planifica fuera de horas pico; ONLINE ayuda si está disponible.

  • Espacio: REBUILD necesita espacio adicional (tabla, tempdb, log); monitorea disco.

Etiquetas: