Optimización de Índices – Desfragmentación

Los índices en una base de datos con el tiempo tienden a fragmentarse, esto contribuye a disminuir el rendimiento de la base de datos. En SQL server tiene una opción para conocer la fragmentación de los índices de la base de datos

A continuación muestro una consulta  que lista las tablas del sistema, la cantidad de registros por tabla, sus índices y porcentaje de fragmentación. La consulta se ordena por orden de registros descendente.

--Consulta para extraer tablas y registros

Select Tablas.Tabla

       ,Tablas.Registros

       ,Indices.Indice

       ,Indices.Fragmentacion

       ,Round((Registros / 1000000),2) * 100 As Orden

From

(select Distinct SCS.name + '.' + SO.Name as Tabla

      ,si.rows as 'Registros'

from sysobjects as SO

    inner join sysindexes as SI on SO.Id = SI.id

    inner join sysfilegroups as SFG on SI.GroupId = SFG.GroupId

    inner join sys.objects SOS On SO.id=SOS.object_id

    inner join sys.schemas SCS On SOS.schema_id = SCS.schema_id) As Tablas

    Inner Join (SELECT d.name + '.' + c.name as Tabla

                      ,b.name As Indice

                      ,a.avg_fragmentation_in_percent 'Fragmentacion'

                FROM sys.dm_db_index_physical_stats (DB_ID(N'Cuentame'), NULL, NULL, NULL, NULL) AS a

                     JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

                     Join sys.objects AS c on b.object_id = C.object_id

                     Join sys.schemas AS d ON c.schema_id = d.schema_id

WHERE b.name is Not NUll) As indices

      On Tablas.Tabla=indices.Tabla

Order By Registros Desc

Una vez ejecutado el listado, puedes seleccionar la tabla e índice que veas más fragmentado y reorganizar o reconstruir mediante la siguiente sentencia.

ALTER INDEX [NombreIndice] on [Esquema].[Tabla] REORGANIZE

O

ALTER INDEX [NombreIndice] on [Esquema].[Tabla] REBUILD

SQL Server es marca registrada de Microsoft.