Detectando fragmentación de índices en SQL Server y Azure

Volvemos a la carga con un artículo sobre índices de esos que tanto nos gustan. Esta vez vamos a hablar de un tema muy importante y es detectar qué índices están más fragmentados y cómo solucionarlo. A menudo vemos que una mala gestión de los planes de mantenimiento provocan una degradación del rendimiento de las consultas y eso, gran parte de las veces es debido a un problema de fragmentación de índices o falta de mantenimiento de las estadísticas. Hoy vamos a centrarnos en el primero de estos aspectos.

¿Cómo detectar fragmentación en los índices?

Para ver la fragmentación de un índice en concreto podemos hacerlo desde el entorno gráfico de nuestro SSMS, haciendo click derecho sobre el objeto y mirando sus propiedades. Sin embargo, esto no es práctico cuando tenemos cientos de índices en nuestra base de datos y queremos saber de un vistazo cuales son los más fragmentados y cuanto. Para ello, usaremos una consulta sobre la función de sistema sys.dm_db_index_physical_stats. 

Otra de las cosas que debemos tener en cuenta es el tamaño de nuestra tabla, con menos de 1000 páginas, el motor de base de datos directamente ignorará los índices nonclustered y, en el caso de los clustered, tampoco vamos a notar diferencia.

Con esto en mente vamos a preparar el script. 

SELECT SCHEMA_NAME(ob.[schema_id]) Esquema,
       ob.[name] AS Objeto,
       i.[name] AS Indice,
       ob.type_desc AS TipoObjeto,
       i.type_desc AS TipoIndice,
       stats.page_count AS Paginas,
       stats.avg_fragmentation_in_percent AS Fragmentacion
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) stats
INNER JOIN sys.indexes i ON stats.[object_id] = i.[object_id] 
 AND stats.index_id = i.index_id
INNER JOIN sys.objects ob ON i.[object_id] = ob.[object_id]
WHERE ob.[type] IN('U','V')
AND ob.is_ms_shipped = 0
AND i.[type] IN(1,2,3,4)
AND i.is_disabled = 0
AND i.is_hypothetical = 0
AND stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND stats.index_level = 0
AND stats.page_count >= 1000 
AND stats.avg_fragmentation_in_percent > 5
ORDER BY stats.avg_fragmentation_in_percent desc

Revisemos el script, por un lado podemos ver que a la función para ver las estadísticas de los índices le estamos pasando el id de la base de datos actual para que se ejecute en ese contexto. Esto es para evitar que se ejecute por todas las bases de datos y podamos tener un problema de rendimiento con esta consulta. Por otro lado vemos que solo afecta a tablas y vistas de usuario que tengan un índice clustered, el tipo de índice 0 está excluido de los filtros. Las tablas HEAP (sin índice clustered) necesitan otro tipo de tratamiento. Podemos ver también el filtro para solo mostrar índices con más de 1000 páginas y el de fragmentación superior al 5%, que suele considerarse el umbral de fragmentación aceptable.

Solucionar fragmentación de índices

Ahora que sabemos cuales son los índices más fragmentados debemos actuar y solucionar el problema. Sabemos que tenemos a nuestra disposición dos alternativas: reorganizar o reconstruir. Para elegir entre una opción u otra tenemos varios factores a tener en cuenta.

Por un lado tenemos el modo de operación de estas instrucciones, reorganizar siempre es una operación online lo que significa que solo generará sobre nuestro índice un intento de bloqueo compartido. El índice se podrá seguir leyendo durante la reordenación sin causar bloqueos. En cuanto a la reconstrucción, solo es online si se lo especificamos manualmente y eso solo es posible en ediciones Enterprise de SQL Server o en las bases de datos o instancias gestionadas de Azure. Si la reconstrucción es offline se generará un bloqueo exclusivo sobre el índice.

Por otro lado, la reconstrucción es más eficiente que la reorganización para porcentajes elevados de fragmentación y eso deberemos tenerlo también muy en cuenta.

¿Debería reorganizar o reconstruir mis índices con mucha fragmentación?

Esto no es una ciencia exacta y es un tema sobre el que hay muchas opiniones discordantes. Normalmente se habla de reorganizar los índices con una fragmentación superior al 5 o 10% y menor al 15 o 30%. Como veis es una horquilla muy amplia y para atinar tenemos que pensar en las las implicaciones de estas operaciones que ya hemos visto antes. Yo os voy a contar cómo lo hago yo pero esto es totalmente personal y deberás adaptarlo a cada caso.

Escenario 1: Mantenimiento programado

En este primer escenario estamos hablando de un mantenimiento programado dentro de una ventana de mantenimiento en la que no hay interferencia con otros procesos. Este caso es el más sencillo porque no tenemos que pensar en no entorpecer a nadie. En estos casos yo pongo el umbral para empezar a actuar en un 5%. Si estamos hablando de una edición Standard de SQL Server reorganizaré los índices con una fragmentación entre un 5 y un 20% y reconstruiré los de mayor fragmentación. Para ediciones Enterprise o Azure reduciré esa horquilla para reorganizar entre un 5 y un 15% y haré reorganizaciones online a partir del 15%.

Escenario 2: Problema puntual de rendimiento

En este escenario estamos hablando de un momento de carga de trabajo elevada en el que hemos recibido o detectado una incidencia por problemas de rendimiento. Tenemos que actuar rápido para solventar la situación pero entorpeciendo lo menos posible a los procesos de negocio que ya de partida tienen un rendimiento mermado. En estos casos pongo el umbral para empezar a actuar en fragmentaciones por encima del 10% en vez del 5. A partir de ahí, si tenemos la suerte de contar con una edición Enterprise, o estamos en Azure, no hay más problema, reconstruiremos con las mismas condiciones que en el escenario anterior, a partir del 15%. Para una edición Standard, donde si vamos a generar bloqueos si reconstruimos, intentaremos reorganizar hasta el 30% de fragmentación.

Solucionar estadísticas desactualizadas

Las estadísticas son clave para SQL Server. Como ya hemos comentado en este blog muchas veces, unas estadísticas desfasadas pueden tener el mismo impacto negativo o peor que un índice fragmentado. Por este motivo, es importante tenerlas en cuenta a la hora de realizar nuestros mantenimientos o enfrentar una incidencia por degradación de rendimiento. Una reconstrucción de índices siempre actualizará las estadísticas asociadas a ese índice pero en el caso de las reorganizaciones deberemos hacerlo manualmente. Tenemos que contar también con que una actualización de estadísticas es más ligera y rápida que un mantenimiento de índices por lo que, en caso de una degradación de rendimiento de una consulta puntual, yo siempre actualizo las estadísticas de las tablas involucradas como primera medida.

Conclusión

Ante un problema de rendimiento, tenemos que verificar el estado de nuestros índices y estadísticas. Además, consultar su nivel de fragmentación será clave a la hora de decidir si vamos a reorganizarlo o reconstruirlo y, todo esto, siempre sin dejar de lado las estadísticas. Tened en cuenta que por mucho que tengamos implementada una solución de mantenimiento de índices y estadísticas nunca vamos a estar 100% seguros de que no va a haber una variación tal de datos que nos va a generar fragmentación o a dejar desfasadas nuestras estadísticas. Es importante que mantengamos una monitorización y vigilancia continua para garantizar el mejor desempeño de nuestros SQL Server.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!