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 un todo se disgrega en partes. ¿Por qué es perniciosa la fragmentación?
La fragmentación provoca una degradación del rendimiento ya que los índices se vuelven ineficaces. El grado de fragmentación de un índice determina si este va a ser usado o no por el optimizador de consultas de SQL Server. En algunos casos el optimizador puede usar un índice que se encuentra muy fragmentado y que puede afectar el rendimiento de la base de datos. Otro caso que puede ocurrir es que el optimizador de consultas de SQL Server no utilice el índice. En esta situación estaríamos manteniendo un índice que no estamos usando, con lo cual estamos desperdiciando recursos del servidor (cpu, disco, memoria).
En el caso particular de la informática tenemos varias clasificaciones:
Clasificación por “Tipo de Objeto”
- A nivel de fichero, fragmentación tradicional, un fichero está disperso en el disco duro. Este tipo de fragmentación se solucionan con herramientas del sistema operativo. Por ejemplo Defrag.
- De Densidad. Se denomina así a la fragmentación que se produce en el espacio libre. A continuación tenemos una ilustración donde se podemos observar fragmentación a nivel de fichero y en el espacio libre.
- Lógica. Se produce en los índices, este tipo de fragmentación es el que vamos a tratar en el post. El orden lógico (clave del índice) y el orden físico (páginas) son diferentes.
- Fragmentación de extensión. Las extensiones en SQL Server son conjuntos de 8 páginas, al igual que en el caso anterior las extensiones de memoria no son contiguas.
Clasificación por el “Lugar Donde se Produce”
Este tipo de clasificación solo aplica a índices, tenemos:
- Fragmentación Interna. Cuando se realiza un borrado de un registro en una página se libera espacio. Este hecho implica que hay parte de la página que se está utilizando ya que tiene datos y parte que no. Cuando existen huecos en generados por borrados en páginas se denomina fragmentación interna. Cuando tenemos fragmentación interna no estamos utilizando el espacio óptimamente. La defragmentación elimina esos huecos. Una vez eliminados tendremos la misma información en menos páginas. Al tener menos páginas en el caso que tuviéramos que leer esos datos recorreríamos menos páginas dedicando menos tiempo y obteniendo una mejora de rendimiento y utilización de recursos en nuestra instancia de SQL Server.
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)
Como su nombre indica regenera la estructura del índice completamente. En el caso de los índices agrupados (clustered index) es mucho más óptimo que eliminar el índice (drop index) y volver a crearlo (create index), pues los índices no agrupados sólo se reconstruirán una única vez en lugar de dos. La sentencia “Alter index rebuild” pretende sustituir a “DBCC DBREINDEX” ya que desaparecerá en futuras versiones.
Reconstruir un índice es una operación atómica, esto es, si se interrumpe habrá que comenzar de nuevo. Desde SQL Server 2005, y al utilizar “Alter Index”, se puede realizar con conexión (opción ONLINE=ON, manteniendo el acceso de los usuarios) o sin conexión. Se necesita disponer de espacio suficiente y admite paralelismo.
Cuando se reconstruye un índice se recalculan las estadísticas. El motivo por el que SQL Server hace esto es que ya que tiene que leerse todas las páginas del índice aprovecha esa lectura para tomar los datos de estadísticas y actualizarlos. Si se hiciera aparte tendría que hacerse otra pasada adicional por todas las páginas.
A continuación presento una tabla resumen proporcionada por Rubén Garrigós y Eladio Rincón en la que muestran las diferencias de comportamiento destacables entre las distintas versiones cuando en una tabla tenemos índices y los reconstruimos. Existen diferencias entre realizar el REBUILD mediante el comando “DBCC REINDEX” y el “ALTER INDEX REBUILD” supongo que por motivos de “retrocompatibilidad”.
La siguiente tabla conjuga los distintos escenarios en las versiones 2000, 2005 y 2008:
Hay que tener en cuenta esto de cara a planes de mantenimiento para no hacer trabajo doble o no presuponer que algo se hace y luego no.
Por último una observación importante respecto del comando rebuild es que si bien permite regenerar particiones esta operación la hace offline. Según Microsoft el que esté offline (ONLINE = OFF) tiene mucha importancia, cito textualmente “se impide el acceso de todos los usuarios a la tabla subyacente durante la operación”. El valor predeterminado es OFF.
Reorganize (Reorganizar)
La reorganización consiste en defragmentar 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.
¿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. Con la siguiente consulta identificamos los campos necesarios para medir la fragmentación:
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, null) 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 importantes 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)
Conclusiones
- Existen distintos tipos de fragmentación. Las que afectan a SQL Server son la fragmentación lógica y la fragmentación de extensión. Otra clasificación es la fragmentación externa e interna.
- Para los índices en entornos con una gran actividad a nivel de inserciones se aconseja fijar un porcentaje de “fill factor” para evitar los “page splits” y con ello la fragmentación.
- La manera de eliminar la fragmentación es mediante las sentencias “Alter index Rebuild” o “Alter index Reorganize”.
- Dependiendo de la versión de SQL Server con la que trabajemos el comportamiento ante la reconstrucción es distinto y nos condicionará el mantenimiento de los índices.
- Para niveles bajos de fragmentación es aconsejable la “reorganización” y cuando estos aumentan la “reconstrucción”.
