¿Por qué aparece la fragmentación?
En la mayoría de las aplicaciones, las operaciones de modificación de datos que sufren nuestras tablas no son ni mucho menos repartidas de forma equitativa por la misma. Esto quiere decir que se darán situaciones en las que realicemos varias inserciones sobre las mismas páginas, dejando otras prácticamente sin tocar. Además, las inserciones producirán page-splits con el paso del tiempo, dando al final una situación en la que el relleno de las páginas de nuestros índices ha variado respecto a su estado inicial. Con el tiempo las páginas no serán correlativas porque al buscar espacio en disco donde almacenarlas, este no será asignado de forma secuencial (fragmentación externa). Además, se darán situaciones en las que existan páginas con espacio libre suficiente para albergar datos, que se encuentran repartidos en otras páginas con también espacio libre suficiente, produciendo una situación en la que por ejemplo 2 páginas puedan unirse en una sola (fragmentación interna)
¿Qué problemas conlleva la fragmentación?
A grandes rasgos, si existe fragmentación, lo que ocurre es que datos que deberían estar de forma contigua y compacta no lo estén. Esto implica que en ocasiones se necesiten mayor numero de lecturas E/S para recuperar un dato, ya que si este debería encontrarse en una sola página y se encuentra en dos, ya estamos necesitando una lectura extra que no debería existir. Pero piensa siempre a lo grande y no pienses en una lectura sola, piensa en ese problema reproducido con un mayor porcentaje y te darás cuenta que al final estamos hablando de ciclos de CPU y lecturas de disco que pueden hacer que consultas que duran milisegundos pasen al orden de los segundos.
¿Cómo evitamos la fragmentación?
Existen 3 formas de eliminar la fragmentación:
- Borrar y recrear el índice fragmentado
Uso de DROP INDEX … y luego CREATE INDEX …
Beneficios: Se puede especificar que el nuevo índice cumpla con la especificación de relleno dada por el FILLFACTOR. La recreación del índice es la mejor puesto que se rellena conforme le hayamos especificado y además todo será correlativo y compacto.
Desventajas: La operación es offline tanto en el borrado como en recreación del mismo (no se podrá consultar). Además la operación es atómica, lo cual quiere decir que si paramos la creación del índice, este último no se crea y por tanto nos quedaremos sin el (hasta volver a recrearlo). Otra desventaja importante es que debemos tener presente que puede que tengamos claves ajenas apuntando al índice, las cuales tendremos que desactivar o borrar previo a realizar esta operación.
- Reordenar las páginas de los niveles hoja del índice
Uso de ALTER INDEX … REORGANIZE
Beneficios: Reorganiza los datos del nivel hoja del índice en un orden lógico. La ordenación se realiza de forma online por lo que podemos consultar el índice mientras se realiza la operación, y además si la interrumpimos, permanecerá en un estado consistente
Desventajas: Este método no obtiene resultados de desfragmentación tan eficientes como un borrado y recreación de índice clustered
- Recrear el índice
Uso de ALTER INDEX … REBUILD
La recreación del índice consiste en realizar un borrado y recreación del mismo. La diferencia con la primera forma de eliminar fragmentación que hemos visto, es que esta operación está pensada para realizarse como tarea de mantenimiento al estilo de reorganización. Es decir, que no es necesario tener en cuenta entre otras cosas las claves ajenas que están apuntándole, por ejemplo.
De forma predeterminada, la operación es offline, lo cual quiere decir que durante el tiempo de recreación del índice no se puede consultar, pero es posible especificar un flag en su definición, que posibilite su recreación ONLINE (solo para ediciones Enterprise edition). Además, es por supuesto mas eficiente que la mera reorganización por lo que en según que casos es mas óptimo realizarlo.
De los tres métodos, solo nos interesa a priori el 2º y 3º debido al gran número de desventajas que se presentan en el primero.
¿Cómo detectamos la fragmentación?
De entre todas las columnas devueltas por la DMF sys.dm_db_index_physical_stats, nos centraremos en las columnas siguientes
|
Columna |
Descripción |
| avg_fragmentation_in_percent | El porcentaje de fragmentación externa, es decir páginas que están fuera de orden y no consecutivas. |
| fragment_count | Numero de fragmentos en el índice (número de páginas físicamente consecutivas en un nivel hoja) |
| avg_fragment_size_in_pages | Media de número de páginas en un fragmento en un índice |
| avg_page_space_used_in_percent | Promedio de espacio de almacenamiento de datos disponible usado en todas las páginas |
Gracias a la información que podemos consultar en dichas columnas, podremos realizar filtros en función del nivel de fragmentación:
- Si hay más de un 30% de fragmentación externa (avg_fragmentation_in_percent), o menos de un 60% de espacio usado medio por página en un índice (avg_page_space_used_in_percent), es recomendable realizar un REBUILD.
- Si hay entre un 10% y un 15% de fragmentación externa o entre un 60% y un 75% de espacio usado medio por página en un índice es recomendable realizar un REORGANIZE.
Además, no nos va a interesar analizar todos los índices porque quizás tengamos miles de ellos y no todos tengan tantos datos y/o fragmentación como para que nos resulte óptimo desfragmentarlos. Es por ello que de todos los índices solo nos quedaremos con los que satisfagan el siguiente filtro:
WHERE (avg_fragmentation_in_percent > 10
or avg_page_space_used_in_percent < 75)
and page_count > 8
La parte de page_count > 8 la pongo porque me parece interesante que si un índice es tan pequeño que no tiene ni 8 páginas, no merece a priori nuestra atención. Esto obviamente depende mucho del entorno en el que estemos pero en la mayoría de situaciones no ganamos nada desfragmentando un índice tan pequeño y el coste de su desfragmentación no está justificado (pese a que sea tan bajo que parezca insignificante, si hablamos de miles de índices la cosa no es tan simple).
Mediante el script que voy a indicar a continuación, se devolverá entre otras cosas la sentencia recomendada para desfragmentar el índice según las premisas definidas anteriormente.
| declare @tempFragmentation as table(bbdd sysname,
[table] varchar(128),
index_name varchar(128), avg_fragmentation_in_percent decimal (28,2), avg_page_space_used_in_percent decimal (28,2), page_count bigint, record_count bigint, fragment_count bigint, internal_fragmentation bit, external_fragmentation bit, recommendation nvarchar(1024)) declare @nombreBBDD nvarchar(128) declare @nombreBBDD_sin_espacios nvarchar(128) declare @sentenciaSQL nvarchar(max) DECLARE cursorBBDD CURSOR read_only fast_forward forward_only FOR SELECT [name] FROM sys.databases where state_desc = ‘ONLINE’ OPEN cursorBBDD — Perform the first fetch. FETCH NEXT FROM cursorBBDD into @nombreBBDD WHILE @@FETCH_STATUS = 0 BEGIN set @nombreBBDD_sin_espacios = @nombreBBDD set @nombreBBDD = QUOTENAME(@nombreBBDD) set @sentenciaSQL = N’ with dt as ( SELECT index_id,object_id,database_id,avg_fragmentation_in_percent, avg_page_space_used_in_percent,page_count,record_count,fragment_count from sys.dm_db_index_physical_stats (DB_ID(”’+ @nombreBBDD_sin_espacios +”’), NULL, NULL, NULL, NULL) where ( avg_fragmentation_in_percent > 10 or avg_page_space_used_in_percent < 75) and page_count > 8 ) SELECT db_name(database_id) BBDD,ss.name+”.”+st.name [table] ,si.name index_name, dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent,page_count,record_count,fragment_count, case when avg_page_space_used_in_percent < 75 then 1 else 0 end Fragmentacion_interna, case when avg_fragmentation_in_percent > 10 then 1 else 0 end Fragmentacion_externa, case when ( avg_page_space_used_in_percent < 60 or avg_fragmentation_in_percent > 15 ) then ”ALTER INDEX ” + si.name+ ” ON ”+ SCHEMA_NAME(st.SCHEMA_ID)+”.”+st.name +” REBUILD;” else ”ALTER INDEX ” + si.name+ ” ON ”+ SCHEMA_NAME(st.SCHEMA_ID)+”.”+st.name +” REORGANIZE;” end SENTENCIA_RECOMENDADA from dt INNER JOIN ‘+ @nombreBBDD+ ‘.sys.indexes si ON si.object_id = dt.object_id and dt.index_id <> 0 AND si.index_id = dt.index_id INNER JOIN ‘+ @nombreBBDD+ ‘.sys.tables st on st.object_id = dt.object_id INNER JOIN ‘+ @nombreBBDD+ ‘.sys.schemas ss on ss.schema_id = st.schema_id order by BBDD,dt.avg_fragmentation_in_percent desc, dt.avg_page_space_used_in_percent asc,page_count desc,record_count desc,fragment_count desc ‘ insert into @tempFragmentation ( bbdd,[table],index_name,avg_fragmentation_in_percent, avg_page_space_used_in_percent,page_count, record_count,fragment_count,internal_fragmentation, external_fragmentation,recommendation) execute(@sentenciaSQL) — This is executed as long as the previous fetch succeeds. FETCH NEXT FROM cursorBBDD into @nombreBBDD END CLOSE cursorBBDD DEALLOCATE cursorBBDD select * from @tempFragmentation |
Quedaría por tanto simplemente que modificarais este script para que las consultas recomendadas sean planificadas para ejecución en horas a las que vuestro modelo de negocio le vengan mejor ¿tal vez a las 2am? ¿tal vez las recreaciones deben ser ONLINE? Digamos que con este esqueleto no os resultará difícil definiros vuestro modelo de reindexación específico.
1 comment
excelente contenido, gracias por compartirlo a quienes estamos entendiendo un poco mas todo el mundo del SQL Server.