Según la propia Microsoft:
El Motor de base de datos de SQL Server mantiene índices automáticamente cada vez que inserta, actualiza o elimina operaciones realizadas en los datos subyacentes. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse por la base de datos (se fragmente). La fragmentación ocurre cuando los índices tienen páginas en las que la ordenación lógica, basada en el valor de clave, no coincide con la ordenación física dentro del archivo de datos. Los índices muy fragmentados pueden reducir el rendimiento de la consulta y ralentizar la respuesta de la aplicación.
Se puede solucionar la fragmentación del índice reorganizándolo o volviéndolo a generar pero el primer paso es identificar los índices fragmentados, para ello podemos ejecutar la siguiente consulta SQL que nos sacará un listado de los índices, las tablas a las que pertenecen y el nivel de fragmentación de los mismos:
SELECT a.object_id as table_id,
OBJECT_NAME(a.object_id) as table_name,
a.index_id,
name as index_name,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), DEFAULT, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
Para índices cuya fragmentación está entre el 5 % y el 30 % solo es necesario reorganizarlo mientras que para índices cuya fragmentación supera el 30 % lo mejor es reconstruirlos.
La instrucción ALTER INDEX IX_name REORGANIZE nos permite reorganizar índices fragmentados entre un 5 y un 30 porciento mientras que ALTER INDEX IX_name REBUILD WITH ONLINE = ON nos reconstruye los índices más fragmentados manteniéndolos online, si no se especifica online = on la regeneración del índice se ejecutará sin conexión.
La reorganización de un índice siempre se ejecuta en línea por lo que su disponibilidad es del 100 % mientras que si se quiere lograr una disponibilidad similar a la opción de reorganización al regenerar se deben generar los índices con el comando anterior.
Existen planes de manteniendo en SQL Server que permiten automatizar estas tareas pero es recomendable correrlos la primera vez de manera manual porque pueden dar errores como el siguiente:
The index cannot be reorganized because page level locking is disabled
Para corregir este error lo único que debemos hacer es modificar el índice en cuestión con la siguiente consulta:
ALTER INDEX IX_name ON table_name
SET (
ALLOW_PAGE_LOCKS = ON
)
Comentarios