Fragmentación de índices en SQL Azure, ¡Quién lo sabía!

He estado en mi proyecto durante más de un año y ha crecido significativamente como aplicación y en datos durante el año. Ha sido un flujo constante de nuevas características. Raramente he vuelto atrás y refactorizado código. La semana pasada noté que algunas de las páginas con mucho uso de datos se cargaban lentamente. En el peor de los casos, una vista podría tardar hasta 30 segundos en cargarse. 10 veces más que mi tiempo máximo de carga…

Llámame ingenuo, pero no consideré la fragmentación de índices en SQL Azure. ¡Es la nube! Se supone que debe ser inmune a los problemas locales… Aparentemente, la fragmentación de índices también es un problema en la nube.

Encontré un par de consultas en un blog de MSDN que identifican los índices fragmentados y luego los reconstruyen.

Después de ejecutar la primera consulta para mostrar la fragmentación de índices, encontré algunos índices con más del 50 por ciento de fragmentación. Según el artículo, cualquier cosa superior al 10% necesita atención.

Primera consulta: Mostrar fragmentación de índices

--Get the fragmentation percentage

SELECT
 DB_NAME() AS DBName
,OBJECT_NAME(ps.object_id) AS TableName
,i.name AS IndexName
,ips.index_type_desc
,ips.avg_fragmentation_in_percent
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
ORDER BY ps.object_id, ps.index_id

Segunda consulta: Reconstruye los índices

--Rebuild the indexes
DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
(
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )

 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
End Try
Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

Fuente