Fragmentación de los índices en SQL Server

Hoy vamos a tratar un tema muy importante como es la fragmentación de los índices de las bases de datos en SQL Server.

¿Qué es la fragmentación de índices?

Con el tiempo y tras el uso en nuestras base de datos de comandos tipo INSERT, UPDATE y DELETE sobre nuestra tablas los índices asociados a ellas se van fragmentando, empiezan a producirse espacios libres en nuestras páginas de índices, y esto hace que nuestras consultas empeoren de forma notable su rendimiento.

¿Cómo conocer el nivel de fragmentación de los índices?

La forma más rápida y sencilla es utilizar el siguiente script que nos indicará el grado de fragmentación de nuestros índices y poder así actuar en consecuencia:

DECLARE @DatabaseID int

SET @DatabaseID = DB_ID()

SELECT DB_NAME(@DatabaseID) AS DatabaseName,
       schemas.[name] AS SchemaName,
       objects.[name] AS ObjectName,
       indexes.[name] AS IndexName,
       objects.type_desc AS ObjectType,
       indexes.type_desc AS IndexType,
       dm_db_index_physical_stats.partition_number AS PartitionNumber,
       dm_db_index_physical_stats.page_count AS [PageCount],
       dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
ORDER BY AvgFragmentationInPercent DESC

¿Qué hacer con los índices fragmentados?

De acuerdo a las buenas prácticas de Microsoft en función de la fragmentación que tengamos tendremos que:

• Reorganizar índices: si la fragmentación se encuentra entre un 5 y un 30%.
• Reconstruir índices: si la fragmentación se encuentra por encima del 30%.

Reorganizar índice

Para reorganizar un índice utilizaremos la siguiente consulta:

USE NOMBRE BASE DE DATOS;
GO
ALTER INDEX IX_NOMBRE_INDICE ON NOMBRE_TABLA
REORGANIZE;
GO

Reconstruir índice

Cuando los índices se encuentran muy fragmentado tendremos que reconstruirlos mediante la siguiente consulta:

USE NOMBRE BASE DE DATOS;
GO
ALTER INDEX IX_NOMBRE_INDICE ON NOMBRE_TABLA
REBUILD;
GO

Consejo plan de mantenimiento

Como habréis podido comprender es muy importante mantener la salud de los servidores de SQL Server por lo que en base a lo que os he explicado ya podríais crearos vuestros propios script y llevar así el mantenimiento de los índices, entre otras cosas, pero si vuestros conocimientos son escasos o no queréis complicaros os recomiendo que os descarguéis este conocido script completamente gratuito realizado por Ola Hallengren el cual os creará una serie de trabajos de SQL que os permitirán realizar copias de seguridad, comprobar la integridad y mantener los índices de vuestras bases de datos de forma sencilla. Esto es suficiente para mantener en buenas condiciones un servidor de SQL de tamaño medio.

Os recomiendo que os paséis por su página web donde podréis encontrar información detallada del script y su funcionamiento.

Espero os haya sido de utilidad.

Entradas relacionadas

• Monitorizar SQL Server con Zabbix
• SQL Server: Habilitar xp_cmdshell
• SQL Server: Permitir conexiones remotas
• Realización de copias de seguridad programadas en SQL Server Express