martes, 23 de abril de 2013

SQL Server: ¿Qué es la fragmentación de índices?

En el artículo SQL Server: FILL FACTOR os comentaba que si para un determinado

índice

seleccionamos un fill factor demasiado elevado y hay numerosos INSERT / DELETE / UPDATE que afectan al

índice

se producen divisiones de página (para proporcionar espacio al

índice

) lo que genera la

fragmentación

. ¿En qué consiste la

fragmentación

del

índice

? Pues simplemente que los datos del

índice

no se almacenan de forma continua en disco lo que hace que sean necesarias más operaciones de E/S para su lectura.

Nos encontramos con 2 tipos de

fragmentación de índices

:

  • Fragmentación interna

    es el espacio disponible que hay entre los registros dentro de una misma página (recordad que en

    SQL Server

    el tamaño es 8Kb). Se mide en % de bytes de la página llenos, el promedio de llenado de todas las páginas del

    índice

    se conoce como

    densidad del índice

    . Este espacio (que se va generando a medida que se producen operaciones de INSERT/DELETE/UPDATE) que no es utilizado afecta al correcto aprovechamiento de la caché y requiere más operaciones de E/S lo que genera un peor rendimiento en las consultas. Otros efectos que genera este tipo de

    fragmentación

    podrían ser que el escaneo del

    índice

    es más lento, aumenta el tamaño de la base de datos, se ve afectado el rendimiento de backups y restores,...

  • Fragmentación externa

    (también denominada

    fragmentación de extensión

    ) se produce cuando el orden lógico de las páginas no coincide con el orden en el que se almacenan en disco (orden físico). Se mide en % de páginas fuera de orden (aquellas cuya siguiente página lógica no coincide con la página física). Este tipo de

    fragmentación

    penaliza el escaneo ordenado del

    índice

    , requiere de mayores operaciones de E/S,...

El siguiente gráfico nos muestra los 2 tipos posibles de

fragmentación

que podemos encontrar en un

índice

.


¿Y cómo detectamos la

fragmentación de índices

en

SQL Server

? Muy sencillo, mediante la función sys.dm_db_index_physical_stats podemos obtener información tanto de la

fragmentación interna

como de la

fragmentación externa

de nuestros

índices

.

-- Obtenemos ID de la base de datos a analizar, por ejemplo, 123
SELECT DB_ID(N'AdventureWorks')

-- Obtenemos fragmentación para la base de datos con ID 123
SELECT OBJECT_NAME(istats.OBJECT_ID) AS TableName, 
       idxs.name AS IndexName,
       index_type_desc,       
       avg_fragmentation_in_percent,
       avg_page_space_used_in_percent, 
       page_count      
FROM sys.dm_db_index_physical_stats(123,NULL,NULL,NULL,'SAMPLED') istats
INNER JOIN sys.indexes idxs 
ON idxs.OBJECT_ID = istats.OBJECT_ID and idxs.index_id = istats.index_id
ORDER BY  page_count DESC

Sobre las columnas que devuelve la consulta anterior:

  • TableName corresponde al nombre de la tabla que contiene el

    índice

    .

  • IndexName corresponde al nombre de del

    índice

    sobre el que se muestran datos de

    fragmentación

    .

  • avg_fragmentation_in_percent representa el % de

    fragmentación externa

    , cuanto menor sea este valor mejor, en valores por encima de un 10% sería recomendable aplicar medidas correctivas.

  • avg_page_space_used_in_percent representa la

    fragmentación interna

    , en concreto, la

    densidad del índice

    , cuanto mayor sea este valor mejor, en valores por debajo del 75% sería recomendable aplicar medidas correctivas.

  • page_count es el número de páginas que forman el

    índice

    , como norma general, deberíamos centrar nuestros esfuerzos en la corregir

    fragmentación de índices

    de más de 100 páginas.

En este punto deberíamos tener claro qué es la

fragmentación de índices

, cómo afecta a los procesos que se ejecutan en nuestro servidor

SQL Server

, cómo saber la

fragmentación

de nuestros

índices

y conocer los

índices

sobre los que hemos de aplicar medidas correctivas; ahora la cuestión es, ¿qué medidas correctivas aplico? ¿cómo lo hago? A continuación os doy unas recomendaciones (cuidado, recomendaciones, no reglas, en bases de datos la respuesta a todo siempre es "depende"):

  • No apliquemos medidas correctivas a

    índices

    con menos de 100 páginas (columna page_count) y empecemos a analizar

    índices

    de mayor a menor número de páginas.

  • Para

    índices

    donde la columna avg_fragmentation_in_percent tiene un valor inferior al 10% no aplicaremos medidas correctivas.

  • Para

    índices

    donde la columna avg_fragmentation_in_percent tiene un valor entre 10% y 35% aplicaremos ALTER INDEX ... REORGANIZE.

  • Para

    índices

    donde la columna avg_fragmentation_in_percent tiene un valor superior al 30% aplicaremos ALTER INDEX ... REBUILD. ¿Qué diferencia hay entre REBUILD y REORGANIZE? Tienen características diferentes pero lamentablemente no hay ninguna regla (sí recomendaciones) que nos indique cuándo utilizar uno u otro. Las principales características de cada uno son:

    • REBUILD:
      • Requiere crear un nuevo

        índice

        antes de eliminar el anterior lo que supone que sea necesario más espacio disponible en base de datos para el nuevo

        índice

        ; si el

        índice

        es grande esto puede ser un problema importante.
      • Puede utilizar varias CPUs por lo que se ejecuta más rápido.
      • Puede requerir bloqueos.
      • Tiene impacto mínimo sobre el registro de transacciones.
      • Reconstruye las estadísticas del

        índice

        .

    • REORGANIZE:
      • Sólo requiere de 8Kb de espacio adicional en base de datos.
      • El proceso se ejecuta en una única CPU.
      • No requiere bloqueos.
      • Queda reflejado al completo en el registro de transacciones.
      • No actualiza las estadísticas del

        índice

        .

    En resumen, REBUILD siempre reconstruirá el

    índice

    por completo independientemente del grado de

    fragmentación

    ; para un

    índice

    ligeramente

    fragmentado

    no es lo adecuado. REORGANIZE soluciona la

    fragmentación

    existente lo que es adecuado para eliminar la

    fragmentación

    de un

    índice

    ligeramente

    fragmentado

    pero no para

    índices

    muy

    fragmentados

    .

  • En ningún caso es conveniente aplicar DROP INDEX + CREATE INDEX si se trata de un

    índice

    cluster, ¿por qué? Para acceder a los datos de una tabla los

    índices

    no cluster utilizan los

    índices

    cluster. Al hacer un DROP INDEX de un

    índice

    cluster todos los

    índices

    no cluster (relacionados) se tienen que reconstruir y posteriormente, cuando se hace el CREATE INDEX del

    índice

    cluster, todos los

    índices

    no cluster (relacionados) vuelven a reconstruirse... esto no es nada óptimo. Con ALTER INDEX ... REBUILD sucede lo mismo, debemos tener en cuenta las recomendaciones que os hacía anteriormente.

Para eliminar la

fragmentación de índices

lo normal sería crear un plan de mantenimiento con cierta periodicidad (yo acostumbro a que sea semanal) donde se haga una reorganización / reconstrucción de los

índices

; dentro de los procesos del plan las estadísticas deberían actualizarse antes que la reorganización / reconstrucción del

índice

ya que esta reorganización / reconstrucción utiliza las estadísticas y para que sea más efectivo el proceso es recomendable que sea haga con las estadísticas actualizadas.


En este punto es importante tener en cuenta que en este artículo he explicado diversos aspectos relacionados con la

fragmentación de índices

pero no he entrado en detalle de aspectos como los extents, matices distintos entre

índices

cluster / no cluster / heaps, distintas versiones de

SQL Server

(sys.dm_db_index_physical_stats sustituye a DBCC SHOWCONTIG, ALTER INDEX ... REORGANIZE a DBCC INDEXDEFRAG, ALTER INDEX ... REBUILD a DBCC DBREINDEX,...),... debéis tomar el artículo como base y a partir de ahí profundizar en los detalles que os interesen.

Y hasta aquí el artículo de hoy, ahora toca revisar la

fragmentación

de vuestros

índices

y tomar las medidas (preventivas / correctivas) adecuadas. Recordad también que dentro de

areaTIC

podéis encontrar otros artículos interesantes, no dudéis en consultar nuestro archivo, también podéis seguirnos por RSS o las principales redes sociales (twitter, facebook, linkedin...) 


LECTURAS RELACIONADAS RECOMENDADAS POR AREATIC.NET

5 comentarios:

Anónimo dijo...

Se debe tener en cuenta que el plan de mantenimiento que indicas para reconstruir los indices, los reconstruye todos independiente de si estan fragmentados o no, no creo que esto sea lo mas recomendado, lo mas recomendado seria identificar los indices que estan muy fragmentados y reconstuir estos,

David Berdié Escolano dijo...

Por supuesto estás en lo cierto, no tiene sentido reconstruir los índices no fragmentados ya que hay un consumo de recursos para no obtener ningún beneficio, una indicación muy interesante que había olvidado mencionar.

En cualquier caso, si se dispone de una ventana de tiempo suficiente en la que la reconstrucción de índices no afecta a los servicios en producción no hay ningún problema en reconstruirlos todos.

hj ruiz dijo...

Interesante. Pero si el primer valor es de 3 y el segundo de 90, pero las páginas son de 10mil. Que hacer.

David Berdié Escolano dijo...

Como diría un buen profesor que tuve, la respuesta sería "depende". En principio, según lo que indicas, si la fragmentación externa es del 3% y la interna del 90% y hay más de 10mil páginas de entrada no sería prioritario reconstruir/reorganizar el índice. Pero lo dicho, habría que analizar el caso en detalle, lo anterior es lo que dice la teoría =)

Anónimo dijo...

Hola, para los interesados en la reorganización/reconstrucción de indices este SCRIPT les puede ayudar a automatizar la tarea:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Publicar un comentario