Reconstruir y optimizar indices fragmentados en SQL Server

5/5 - (1 voto)

Es muy común en SQL server que si una tabla tiene muchos cambios de estado, inserciones, borrados, etc, los indices queden fragmentados y las consultas a base de datos sean cada vez mas pesadas.

Pues con esta pequeña entrada voy a explicar como consultar el porcentaje de fragmentación de las tablas y como reconstruirlas y optimizarlas.

Lo primero que haremos es lanzar esta query que nos dirá el porcentaje de fragmentacion de las tablas.

SELECT 
dbtables.[name] as 'Table',
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() 
ORDER BY indexstats.avg_fragmentation_in_percent desc
Porcentaje de fragmentación de cada tabla

Una vez visto que los porcentajes de fragmentación son altisimos, lanzamos esta query que reconstruye todos los indices de las tablas.

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80 
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Porcentajes de fragmentación con indices de las tablas reconstruidas

Lanzamos estas querys periódicamente conseguiremos mantener las bases de datos en un estado optimo.

Deja un comentario