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
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
Lanzamos estas querys periódicamente conseguiremos mantener las bases de datos en un estado optimo.