Mantenimiento de Índices.

Creado por David Miralpeix, Modificado el Wed, 20 Mar 2024 a las 07:33 AM por Rosa Mª Martí

Los sucesivos borrados, inserciones y modificaciones de los registros de una tabla, provocan una dispersión de los datos conocida como fragmentación.

Cuando un dato no cabe en la página porque hay poco espacio libre, se crea una nueva página con parte de los datos de la página original, y con nuevos punteros que hacen que la nueva página se encuentren ordenadas de forma lógica, pero no de forma física (basándonos en la clave del índice).

Para ver el nivel de fragmentación que tenemos podemos hacer uso de la función sys.dm_db_index_physical_stats;

SELECT OBJECT_NAME(ddips.[object_id], DB_ID()) AS tabla, i.[name] AS [indice] , ddips.[index_type_desc],

CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%],

ddips.[page_count]

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips

INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id] AND ddips.[index_id] = i.[index_id]

WHERE ddips.[avg_fragmentation_in_percent] > 15 --AND ddips.[page_count] > 500

ORDER BY ddips.[avg_fragmentation_in_percent], OBJECT_NAME(ddips.[object_id], DB_ID()), i.[name]

Con un avg_fragmentation_in_percent entre 5 y el 30 podemos reorganizar el índice (solo el nivel de hoja) y con un porcentaje >30% deberíamos reconstruir el índice. La reconstrucción del índice también actualiza las estadísticas de las columnas.

Recordemos que las estadísticas nos dan la distribución de los datos en las columnas de la tabla, su selectividad y su densidad. El Optimizador de consultas de SQL se basa en las estadísticas de los índices y columnas para calcular el coste de E/S y CPU y buscar el mejor plan de ejecución de la consulta.

Las estadísticas se crean automáticamente para las columnas de índices y las columnas de los WHERE y de los JOIN

Es muy importante generar tareas de mantenimiento de índices y estadísticas, que reorganicen, regeneren índices y actualicen estadísticas.

ALTER INDEX ALL ON Pedidos_Cli_Lineas REORGANIZE;

ALTER INDEX ALL ON Pedidos_Cli_Lineas REBUILD;

UPDATE STATISTICS Pedidos_Cli_Cabecera

A continuación, se adjuntan un par de consultas de interés.

Índices que nunca se han usado:

SELECT OBJECT_NAME(i.[object_id]) AS [Table Name], i.name

FROM sys.indexes i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] AND o.[type] = 'U'

WHERE i.index_id NOT IN

(SELECT index_id FROM sys.dm_db_index_usage_stats WHERE [object_id] = i.[object_id] AND i.index_id = index_id AND database_id = DB_ID())

ORDER BY OBJECT_NAME(i.[object_id]) ASC

Índices que no se leen y si se escriben, recordemos que, ante cualquier modificación de una tabla, hay que actualizar también los datos de sus respectivos índices:

SELECT o.[name] AS [statement] ,

i.[name] AS [index_name] ,

ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS lecturas ,

ddius.[user_updates] AS escrituras ,

SUM(SP.rows) AS NumRegistrosDelIndice

FROM sys.dm_db_index_usage_stats ddius

INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id] AND i.[index_id] = ddius.[index_id]

INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id]

INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]

WHERE ddius.[database_id] = DB_ID() AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1

GROUP BY o.[name] , i.[name] , ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] , ddius.[user_updates]

HAVING ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0 and ddius.[user_updates] <>0

ORDER BY ddius.[user_updates] DESC , o.[name] , i.[name]

Si deseas saber más sobre índices, te recomendamos los siguientes artículos: