A diferencia de otros objetos en SQL Server, los índices son los únicos objetos que pierden su efectividad con el paso del tiempo si no se les da un mantenimiento adecuado.

Es muy importante que creemos un plan de mantenimiento para esto, pues el grado de fragmentación de un índice, determina si este va a ser usado o no por el optimizador de consultas de SQL Server. En algunos casos el optimizador puede usar un índice que se encuentra muy fragmentado y que puede afectar el performance de la base de datos.

Generalmente donde se nota más la degradación del desempeño de una aplicación, es cuando corren sobre bases de datos muy grandes, es decir tablas de varios millones de registros. En estos casos puede pasar la aplicación de tener un tiempo de respuesta adecuado a un tiempo de respuesta inaceptable, y peor aun si la aplicación comienza a generar timeouts. Y todo esto puede suceder en cualquier momento cuando no se les da un mantenimiento adecuado a los índices. En este caso lo que sucede es que el grado de fragmentación del índice llega a un punto tal, que cambia el plan de ejecución y como consecuencia el tiempo de respuesta del sistema. Todo esto se puede prevenir y evitar si tenemos un adecuado plan de mantenimiento de los índices.

¿Como es que se genera la fragmentación?

La fragmentación de un índice se genera cuando los datos de una tabla son modificados y estas modificaciones afectan una página de índice. Es decir, cuando una aplicación, inserta, borra o actualiza información de una tabla, el índices clustered y los nonclustered también son modificados.

Cuando se lleva a cabo una operación de borrado, se libera espacio en las páginas de índice, lo que causa que solo cierta parte de la página de índice se encuentre ocupada, a diferencia de lo que normalmente contiene cuando no existe fragmentación, a esta condición se le conoce como fragmentación interna.

Cuando existe fragmentación interna, las páginas de índices no utilizan el espacio en disco de una manera eficiente y se incrementan el número de páginas, es decir ocupamos más páginas de índices de las necesarias, causando que SQL Server tenga que leer más páginas para satisfacer un query. Ahora, cuando estas páginas son leídas del disco para pasarlos a la memoria, se le conoce como paging y debido a que el acceso a los discos es lo más lento de un sistema, cualquier tipo de acción que hagamos para evitar acceder al disco se va a ver reflejado en el rendimiento, es decir entre menos paging llevemos acabo, mejor va a ser el rendimiento de nuestro SQL Server.

Las inserciones y borrados de las tablas, provoca que se generen más páginas de índices. Cuando llevamos a cabo un insert y la página no puede almacenar ese registro de índices se genera un page split, en este caso es cuando se crea una nueva página conservando orden lógico de las llaves de índices pero no el orden físico. Generalmente esta nueva página no se crea junto a la original, sino en otro lado del disco y cuando estas páginas no se encuentras físicamente ordenadas se le conoce como fragmentación externa. Cuando tenemos fragmentación externa se incrementa el número de IO's al disco al igual que lecturas lógicas.

Algunas veces es aceptable tener cierto grado fragmentación interna en sistemas altamente transaccionales, para evitar los page splits, pero la fragmentación externa siempre debe ser evitada, pues también evita que SQL Server lleve a cabo lecturas adelantadas.

¿Como podemos ver la fragmentación?

A diferencia de SQL Server 2000 y versiones anteriores, donde ejecutábamos un DBCC SHOWCONTIG, SQL Server 2005 provee la función sys.dm_db_index_physical_stats para poder ver la fragmentación mediante un simple select. Las columnas a revisar son avg_fragmentation_in_percent y avg_page_space_used_in_percent de donde podemos ver el grado de fragmentación de los índices.

Con el siguiente query podemos ver la fragmentación de todas las tablas de la base de datos AdventureWorks

Debe ser ejecutado desde la base de datos que se quiere revisar.

SELECT OBJECT_NAME(dt.object_id),si.name, dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM (SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks'), NULL, NULL, NULL, 'DETAILED')
WHERE index_id = 0) as dt --Con index_id 0 evitamos traer información de tablas que no tienen índices (Heaps)
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id

Debemos de observar el valor avg_fragmentation_in_percent para determinar si el índice contiene fragmentación externa, cuando este es mayor a 10 debemos considerar desfragmentar el índice. En la columna avg_page_space_used_in_percent podemos ver la fragmentación interna y esta se presenta cuando el valor es menor a 75.

Fragmentación    Valor
 Interna   < 75
 Externa  > 10

Si determinamos que nuestra base de datos esta presentando fragmentación, ya sea interna o externa, debemos de ejecutar ALTER INDEX...REORGANIZE o ALTER INDEX...REBUILD para eliminarla. Debemos recordar que cada sistema es diferente y este valor es un aproximado, pero lo podemos tomar como base y realizar pruebas para tomar un valor más adecuado.

¿Como manejar la fragmentación?

ALTER INDEX...REORGANIZE
Esta operación nos va a desfragmentar el leaf level de un índice clustered y nonclustered en tablas y vistas ordenando físicamente las páginas leaf-level del índice para hacer match con las lógicas. En nivel de llenado de las páginas, va a depender del fill factor definido. Para poder ver el valor de fill factor en nuestras tablas podermos ver la vista sys.indexes.

ALTER INDEX Indice1 on tabla1
REORGANIZE;

ALTER INDEX...REBUILD
Esta operación va a eliminar la fragmentación interna y externa mediante la eliminación y recreación del índice. Este Proceso remueve la fragmentación externa ordenando los registros del índice en páginas continuas y remueve la fragmentación interna eliminando páginas al reacomodar los registros en ella, donde el llenado de las páginas va a depender del fill factor.

ALTER INDEX Indice1 on tabla1
REBUILD;

Opción ALL

Si especificamos la opción ALL en la operación ALTER INDEX...REBUILD, le estamos diciendo que todos los índices de la tabla van a ser eliminados y regenerados en una sola transacción.

Otra de las cosas nuevas de la versión 2005 es que podemos regenerar un índice sin bloquear las tablas y los índices mediante la opción ONLINE,  Y mejor aun, podemos ejecutar operaciones sobre los índices en una misma tabla al mismo tiempo, siempre y cuando se realicen las siguientes operaciones:

  • Crear múltiples índices nonclustered
  • Reorganizar diferentes índices en la misma tabla

¿Como saber que tipo de sentencia debo ejecutar?

Cuando los índices no están demasiado fragmentados podemos reorganizar(REORGANIZE) los índices ya que usa menos recursos y corre automáticamente en línea, para índices muy fragmentados necesitamos regenerarlos(REBUILD).

También podemos ejecutar el query sobre sys.dm_db_index_physical_stats y utilizar los siguientes parámetros.

   avg_fragmentation_in_percent  avg_page_space_used_in_percent
 ALTER INDEX...REORGANIZE

 > 10 y

  60

 ALTER INDEX...REBUILD

 > 15  

 < 60


Conclusiones

  • Los índices se fragmentan durante las operaciones de INSERT, DELETE y UPDATE y van degradando el performance.
  • La fragmentación interna ocurre cuando las páginas de índice no están llenas a su máxima capacidad indicada por el fill factor.
  • La fragmentación externa ocurre cuando el orden físico de las páginas de índices no son iguales al orden lógico.
  • Los niveles de fragmentación pueden revisarse mediante un select a la función sys.dm_db_index_physical_stats.
  • La fragmentación se puede corregir ejecutando ALTER INDEX...REORGANIZE o ALTER INDEX...REBUILD.
You are not authorised to post comments.

Comments powered by CComment

DBASupport - Foro

Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...

Estadisticas en la base de datos
Buenos días, estoy teniendo problemas de performance en mis queries y quisiera saber como puedo validar que mis tablas tienen las estadisticas...
URGE AYUDA
  • Topic started by
  • in General
  • 15 years ago.
  • 15 years ago.
  • 30 replies
HOLA ESPERO ME PUEDAN AYUDAR. TEN GO UNA TABLA CON una estructura similar a la siguiente: id_mov cargo subtotal total...

Duada de Memoria de SQL Server 2005
  • Topic started by Erzengel
  • in General
  • 11 years ago.
  • 11 years ago.
  • 30 replies
Buen Día Recurro nuevamente a su sabiduría. Quiero saber el porque tiene un tope SQL Serever de 2GB en Windows server 2003 de 32 bit y si alguien...

SqlDateTime overflow
  • Topic started by
  • in General
  • 15 years ago.
  • 15 years ago.
  • 11 replies
Alguno de ustedes ha tenido este problema: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.' Les...

problemas para iniciar el agente sql
  • Topic started by acgch
  • in General
  • 14 years ago.
  • 14 years ago.
  • 11 replies
Recientemente para asegurar el sql server 2005, cree una cuenta sql con derechos de sysadmin y borre el grupo BUILTIN\Administradores para evitar...

Problemas para levantar mirror
  • Topic started by acgch
  • in General
  • 14 years ago.
  • 14 years ago.
  • 16 replies
Hola amigos, tengan un buen día, les comento que estoy configurando mirror siguiendo los pasos a través del wizard del managment studio y también...
Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...

Estadisticas en la base de datos
Buenos días, estoy teniendo problemas de performance en mis queries y quisiera saber como puedo validar que mis tablas tienen las estadisticas...
Deshabilitar todos los jobs
  • Topic started by agamez
  • in Jobs
  • 3 years ago.
  • 3 years ago.
  • 1 reply
Buenos días, alguien sabe como puedo deshabilitar rapidamente todos los jobs de SQL Server, gracias.

Mirroring con estados disconnected
Hola, el día de hoy después de reiniciar mi servidor de destino a donde realizo el mirroring ya no pude replicar más, el error que muestra es que...

Se me duplica el mensaje en /etc/motd
  • Topic started by agamez
  • in General
  • 3 years ago.
  • 3 years ago.
  • 2 replies
Buen día, alguien tiene idea porque después de actualizar a fedora 28 se me esta duplicando la salida de mi /etc/motd?

Cómo puedo filtrar un mensaje en el log messages
Buenos días, estoy recibiendo en mi log de errores muchos mensajes de cron, ya que tengo varios crones configurados en mi server. Existe alguna...

Cómo validar la hora de ejecución de un proceso
Hola, estoy tratando de validar que un proceso sólo pueda ser ejecutado dentro de un periodo de tiempo, en este caso entre las 7:00 AM y las 7:00...