Debido a que toda instancia de SQL Server necesita mantenimiento para mantener unos niveles de rendimiento razonables, este post nace con la intención de profundizar en la fragmentación, definir sus tipos y aplicación de soluciones. La reorganización y reconstrucción nos van a permitir paliar este problema en función de la magnitud de la fragmentación. Veremos como funcionan y sus diferencias.
¿Qué es la fragmentación en SQL Server?
La fragmentación es un fenómeno por el cual los datos almacenados en los índices dejan de estar organizados de forma óptima, provocando una degradación del rendimiento. Esto sucede cuando el orden lógico del índice (según las claves) deja de coincidir con el orden físico de las páginas de datos.
La fragmentación puede hacer que el optimizador de consultas de SQL Server ignore un índice o lo use de forma ineficiente, consumiendo más recursos de CPU, disco y memoria.
Existen distintos tipos de fragmentación.
Clasificación por “Tipo de Objeto”
- Fragmentación a nivel de fichero: ocurre cuando un archivo se dispersa en el disco. Se resuelve con herramientas del sistema operativo (por ejemplo, Defrag).
- Fragmentación de densidad: se produce en el espacio libre dentro de las páginas.
- Fragmentación lógica: ocurre en los índices cuando el orden lógico y físico no coinciden. Es la que más afecta al rendimiento y la que abordaremos en este artículo.
- Fragmentación de extensión: cuando las extensiones (conjuntos de 8 páginas) no son contiguas en memoria.
Clasificación por el “Lugar Donde se Produce”
Este tipo de clasificación solo aplica a índices, tenemos:
- Fragmentación interna: aparece cuando hay huecos dentro de las páginas por eliminaciones de registros. Esto implica un uso ineficiente del espacio. Reorganizar o reconstruir el índice elimina estos huecos, reduciendo el número de páginas necesarias para leer los mismos datos y mejorando el rendimiento.
Una vez realicemos un “Alter table rebuild” o “Alter table reorganice” tendremos:
- Fragmentación Externa. Este tipo de fragmentación se produce con inserciones. Cuando se realiza un inserción los datos como hemos visto se guardan en páginas. Como los índices deben mantener el orden lógico por definición, si no cabe en la página se toma una página vacía y se inserta el valor nuevo. Esta página no suele estar junto a la original por lo que para leer se producirían saltos en el orden de lectura de las páginas, lo cual no es eficiente sobre todo si hay muchos. A este fenómeno se le denomina “Page Split”. Es como leer un libro donde tienes que ir dando saltos una vez te has leído la página. El no leer páginas contiguas exige que el disco tenga que trabajar más para ir situándose en el sector de disco donde está cada página. En entornos donde hay muchas inserciones es conveniente ajustar el parámetro fill factor del índice. Este parámetro le indica a SQL Server que debe dejar el porcentaje de espacio libre de página que le especifiquemos. De esta manera cuando se tenga que insertar un registro en la página utilizaremos este espacio en lugar de tener que utilizar una página nueva.
Rebuild vs Reorganize en SQL Server
Rebuild (Reconstrucción)
Reconstruir un índice implica regenerar completamente su estructura interna.
Es más eficiente que eliminar y volver a crear el índice manualmente, ya que los índices no agrupados solo se reconstruyen una vez.
- Actualiza las estadísticas automáticamente, ya que SQL Server aprovecha la lectura de todas las páginas del índice.
- Puede ejecutarse en línea (ONLINE = ON) para no interrumpir el acceso de los usuarios, disponible en ediciones Enterprise y posteriores.
- Requiere espacio adicional en disco y admite paralelismo.
- Si la operación se interrumpe, debe reiniciarse desde el principio.
ALTER INDEX nombre_indice ON nombre_tabla REBUILD;
A partir de 2024 el comando DBCC DBREINDEX está completamente obsoleto desde SQL Server 2016. Se recomienda usar siempre ALTER INDEX REBUILD. Además, las versiones más recientes de SQL Server y Azure SQL Database ofrecen la opción RESUMABLE = ON para pausar y reanudar una reconstrucción, lo que facilita el mantenimiento en entornos productivos.
Reorganize (Reorganizar)
La reorganización consiste en desfragmentar el índice a nivel de hoja, esto es, ordena físicamente las páginas del índice para que coincidan con el orden lógico. SQL Server hace esto sin asignar nuevas páginas, se reorganiza con las páginas existentes.
La reorganización compacta y si resultado de esta compactación quedan algunas vacías se eliminan mejorando el rendimiento ya que para leer todos los datos tenemos menos páginas.
La reorganización se realiza automáticamente en línea. Como en todo hay excepciones, no se puede realizar esta operación sobre índices deshabilitados e índices con la opción “ALLOW_PAGE_LOCKS”.
La sentencia que nos va a permitir reorganizar es “Alter index reorganize”, como en el caso anterior suplanta a “DBCC INDEXDEFRAG”.
En la reorganización no se actualizan las estadísticas por defecto ya que no lee todas las páginas del índice, solo los nodos hoja.
ALTER INDEX nombre_indice ON nombre_tabla REBUILD;
En versiones recientes de SQL Server y Azure SQL Managed Instance, puedes combinar ALTER INDEX REORGANIZE con LOB_COMPACTION = ON para compactar objetos grandes (LOBs), mejorando el rendimiento en índices con columnas de tipo varchar(max) o varbinary(max).
¿Cuándo utilizar Rebuild o Reorganize en SQL Server?
Existen distintos límites para utilizar Rebuild o Reorganize pero para ello es necesario cuantificar la fragmentación. Puedes cuantificar el nivel con la siguiente consulta:
SELECT
si.name,
si.index_id,
index_level,
index_type_desc,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent,
fragment_count,
page_count,
record_count
FROM sys.dm_db_index_physical_stats(
DB_ID('AdventureWorks'),
OBJECT_ID('HumanResources.Employee'),
NULL, NULL, 'SAMPLED') v
JOIN sys.indexes si
ON v.object_id = si.object_id
AND v.index_id = si.index_id;
GO
Como se puede ver estamos utilizando la dmv “sys.dm_db_index_physical_stats” que toma los siguientes parámetros de entrada:
- database_id. Id de la base de datos. El tipo es smallint
- object_id. Id de la tabla o vista. El tipo es int
- index_id. Id del índice. El tipo es int
- partition_number. Id de la partición. El tipo es int
- mode. Acepta los siguientes valores DEFAULT, NULL, LIMITED, SAMPLED o DETAILED
Los campos clave son:
- Avg_fragmentation_in_percent, determina si el índice contiene fragmentación externa.
- Avg_page_space_used_in_percent, muestra la fragmentación interna.
En base a estos campos podemos establecer los siguientes límites:
- Si la fragmentación externa es mayor que el 10% (Avg_fragmentation_in_percent > 10) se aconseja reorganizar (Alter index Reorganize).
- Si la fragmentación interna es menor que el 75% (Avg_page_space_used_in_percent < 75) se aconseja reorganizar (Alter index Reorganize).
- Si la fragmentación externa es mayor que el 15% (Avg_fragmentation_in_percent > 15) se aconseja reconstruir (Alter index Rebuild).
- Si la fragmentación interna es menor que el 60% (Avg_page_space_used_in_percent < 60) se aconseja reconstruir (Alter index Rebuild).
Estas cifras son orientativas y pueden ajustarse según la carga de trabajo, el tamaño de la base de datos y los recursos disponibles.
Conclusiones sobre índices en SQL
- La fragmentación afecta al rendimiento de los índices y, por tanto, a la velocidad de las consultas.
- La reorganización es útil para niveles bajos o moderados de fragmentación y no bloquea el acceso a los datos.
- La reconstrucción es necesaria cuando la fragmentación es alta o se requiere actualizar estadísticas.
- Ajustar el
FILLFACTORayuda a prevenir page splits en entornos con muchas inserciones. En SQL Server modernos, usa siempre
ALTER INDEXcon las opcionesONLINEyRESUMABLEpara optimizar el mantenimiento.
