Recientemente estamos detectando cierta «obsesión» por parte de algunos clientes con reducir la fragmentación. Por ejemplo, en un caso concreto donde pasaron de 10 minutos a casi 2 horas de ejecución para una consulta, el problema no tenía nada que ver con la fragmentación sino con las estadísticas. Como muchos sabréis, para la calidad de un plan de ejecución es muy importante contar con estadísticas suficientemente precisas, algo que al desfragmentar, obtenían «de rebote» y por tanto solucionaban el problema desfragmentando cuando hubiese sido suficiente con actualizar las estadísticas.

¿Qué es la fragmentación?

La fragmentación es un artefacto indeseable, eso es un hecho, pero también lo es que el mantener la fragmentación en niveles muy bajos, cercanos a 0, puede ser algo muy costoso y pesado para el servidor y que llegue incluso a penalizar más que el mantener un cierto grado de fragmentación.

Cuando hablamos de fragmentación en realidad deberíamos aclarar a qué nos referimos exactamente, ya que puede afectarnos a muchos niveles. Por ejemplo, a nivel de SQL Server imaginemos que tenemos una base de datos con un fichero de datos en un único fragmento contiguo en disco y tengamos una única tabla con un único índice recién reconstruido al 100% de llenado y con un 0% de fragmentación como resultado.

Aunque estemos en esa situación «ideal» esto no nos protegerá del impacto de la fragmentación si el disco presentado es en realidad un volumen lógico donde el espacio se asigna mediante thin provisioning y situado sobre un pool de discos compartido con muchos otros servidores, etc. Al final, aunque desde el lado de SQL Server y desde el OS todo esté «perfectamente contiguo», podemos tener aleatoriedad en las operaciones a nivel de cabina de almacenamiento.

Por otra parte, tenemos que considerar que la mayor parte de sistemas de almacenamiento modernos están basados, al menos en parte, en discos SSD donde el impacto de un acceso aleatorio es mucho menor que en un disco magnético tradicional.

¿Cuándo debemos preocuparnos por la fragmentación?

¿Quiere decir esto que no debemos preocuparnos nunca más de la fragmentación? Tampoco es cierto, puesto que va a depender mucho de los patrones de acceso que tengamos a nuestros datos el que dicha fragmentación, interna o externa, nos afecte más o menos. También en muchas ocasiones el porcentaje de llenado de las páginas, que determina el uso efectivo del espacio, puede tener más impacto que el hecho que las páginas estén contiguas o no. Esto es especialmente notorio cuando tenemos operaciones de scan sobre índices. Cuando realizamos una operación aleatoria para localizar 1 registro realmente es bastante irrelevante donde esté dicha página (salvo que esté ya en memoria) y si el factor de llenado es mayor o menor.

Sin embargo, en un caso de un scan de bastantes páginas, en un caso extremo, con un factor de llenado efectivo de un 50% implicaría que para acceder a un mismo volumen de información acabaremos duplicando el número de lecturas de páginas. Esto tiene un impacto a nivel de disco pero también a nivel de CPU ya que tendremos que gestionar un mayor número de operaciones de extracción de filas desde las páginas al contener menos filas cada página.

Por tanto la realidad lo que nos dice es que lo más inteligente pasa por medir el impacto de la fragmentación en nuestros escenarios habituales. Con estos datos en la mano, decidir cómo de agresiva o laxa debe ser nuestra política de desfragmentación.

Para hacer algunas pruebas vamos a crear una base de datos con un par de tablas, una que utilizará un GUID aleatorio y otra que utilizará un GUID secuencial como clave primaria de su índice cluster.

create database fragmentation
go
use fragmentation
go
create table t1 (id uniqueidentifier primary key clustered DEFAULT NEWSEQUENTIALID(), payload varchar(1000))
go
create table t2 (id uniqueidentifier primary key clustered DEFAULT NEWID(), payload varchar(1000))

Una vez tenemos las tablas, insertaremos algunos registros (10 millones) en batches de 100000 en 100000:

insert into t1 (payload)
select TOP 100000 REPLICATE('a',1000) from sys.all_columns c1,sys.all_columns c2,sys.all_columns c3,sys.all_columns c4
go 100
exec sp_spaceused t1
-- name	rows		reserved
-- t1	10000000    11513752 KB
insert into t2 (payload)
select TOP 100000 REPLICATE('a',1000) from sys.all_columns c1,sys.all_columns c2,sys.all_columns c3,sys.all_columns c4
go 100
exec sp_spaceused t2
-- name	rows		reserved
-- t2	10000000    17319088 KB
go

Lo primero que podemos ver es que el espacio ocupado por ambas tablas/índices es muy distinto pasando de 11 GB aproximadamente a 17 GB en el caso fragmentado.

Si comprobamos la fragmentación veremos que en la tabla t1 es prácticamente del 0% mientras que en la tabla t2 es casi del 100% debido a la aleatoriedad de los GUID:

-- Check the fragmentation
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED')

 -- Check the fragmentation select * from sys.dm_db_index_physical_stats(db_id(),object_id('t2'),1,0,'DETAILED')

Si lanzamos una consulta que realice un scan completo de ambas tablas podremos comprobar que existe una diferencia importante tanto en el consumo de CPU como en la duración total:

-- Measure scan impact of this extreme case
set statistics io on
set statistics time on

select COUNT(*) from t1
-- Table 't1'. Scan count 9, logical reads 1441216, physical reads 0, read-ahead reads 1428059, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- CPU time = 5079 ms,  elapsed time = 6045 ms.

select COUNT(*) from t2
-- Table 't2'. Scan count 9, logical reads 2165975, physical reads 0, read-ahead reads 2042691, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- CPU time = 16656 ms,  elapsed time = 17490 ms.

Como ya observamos previamente, el tamaño de la tabla/índice era bastante mayor en el caso de t2 debido a la fragmentación, con lo que haciendo algunos cálculos podemos intentar aproximar el número de lecturas si reducimos a un 65% el total de filas a leer:

-- Reduce the volume of pages to read to be approximately the same than t1 reduces the CPU a lot but not so much the elapsed time
select COUNT(*) from (select top (floor(10000000*0.65)) * from t2) t2
-- Table 't2'. Scan count 1, logical reads 1406592, physical reads 0, read-ahead reads 1413648, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- CPU time = 11750 ms,  elapsed time = 13982 ms.

Podemos ver que, aunque el volumen de páginas a leer es similar al caso de t1, el consumo de CPU y la duración no decrece en igual medida. En este escenario adverso (un scan de tabla completo o casi completo) tenemos una componente de CPU y de duración a la que afecta la fragmentación «nivel extremo» como la que tenemos en la tabla t2.

A continuación, lo que vamos a hacer es realizar operaciones de modificación sobre t1, modificando la columna ID con valores GUID aleatorios, para ir aumentando su nivel de fragmentación. Por ejemplo, si modificamos aproximadamente un 10% de las filas conseguimos una fragmentación de aproximadamente un 11%:

-- What about "normal" fragmentation levels, 10%, 20%, 30%...

-- Run some updates on the non fragmented table
update top (1000000) t1
set id=NEWID()
-- Check the fragmentation
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED')
-- ~11% fragmented

Para conseguir mayores niveles de fragmentación debemos ir realizando ajustes, ya que al encontrarse ya la tabla parcialmente fragmentada, cuando introducimos nuevos valores aleatorios, es posible que encuentren ya «su lugar» dentro de páginas previamente fragmentadas. Por ejemplo, para pasar de un 11 a aproximadamente un 20% necesitamos realizar 3 veces más updates:

update t1
set id=NEWID()
from 
(Select top 100000 * from t1 order by id) t1
go 3
select * from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),1,0,'DETAILED')
-- ~20% fragmented

Para cada «grado» de fragmentación entre un 10% y un 90% realizaremos una nueva ejecución de la consulta de conteo anteriormente mostrada y almacenaremos sus resultados. Con dichos datos podemos cargarlos en una hoja de cálculo y representar gráficamente los resultados:

Podemos ver cómo a medida que aumenta la fragmentación tenemos un aumento tanto de la duración como del uso de CPU. Existe también una correlación con el tamaño del índice que aumenta con la fragmentación, por lo que tenemos que ambos factores contribuyen negativamente.

Cuánto impacto tenga en el tiempo total, que es al final lo que nuestros usuarios van a percibir, dependerá de cómo de costoso sea realizar la entrada/salida y su impacto en la CPU y duración. Si el uso de CPU aumenta y nuestro sistema tiene que competir para obtener dicha CPU, la duración también se resentirá en mayor medida. Por tanto, el impacto, aunque claramente en este escenario de scan es notorio, debemos analizarlo en cada caso.

A su vez, tenemos que ser conscientes de la «proporción» en la que puede impactar el «problema de fragmentación» ya que si supone multiplicar por 10 el tiempo de ejecución de una consulta… nuestra experiencia debería indicarnos que «algo más hay» detrás de esa ralentización más allá de un mero aumento en la fragmentación de un índice.

En este post hemos visto un caso donde la duración aumentaba desde 6 segundos hasta 17 segundos, casi 3 veces más pero esto no es lo habitual, normalmente la penalización es incluso menor. En casos donde las operaciones habituales sean lookups, búsquedas de 1 fila, el impacto de la fragmentación es mínimo o inexistente. Incluso para búsquedas de varios miles de registros, realizando un scan parcial del índice, el coste puede ser casi insignificante comparado con lo mostrado en este post. Os recomiendo el siguiente artículo de Tibor Karaszi donde se aborda ese escenario mucho más habitual: http://sqlblog.karaszi.com/fragmentation-the-final-installment/

En conclusión, la clave del impacto percibido está principalmente en el uso que hagamos de dicho índice y especialmente de cómo de llenas estén las páginas fragmentadas, lo cual dependerá de cómo llegáramos a dicho nivel de fragmentación y al factor de llenado inicial que tuviéramos en uso. Aunque niveles muy elevados de fragmentación en general tienen siempre algo de impacto no debemos olvidar que en función de los casos de uso puede que una tabla fragmentada un 50% no represente el más mínimo problema. También es importante tener en cuenta durante el diseño de las tablas y los índices conocer las operaciones que vamos a realizar sobre dichas tablas e índices. De esta forma podremos diseñar pruebas de rendimiento realistas y predecir cual será el comportamiento esperable (y no el que tendríamos en un entorno «sintético» con 0% de fragmentación).

Como puedes ver, la desfragmentación no es imprescindible y en muchas ocasiones bastará con tener optimizados nuestros planes de ejecución. En las próximas semanas estará disponible nuestro curso de Planes de Ejecución, impartido por Enrique Catalá, con el que podrás aprender cuando quieras y desde casa mediante lecciones en vídeo, a optimizar tus planes de Ejecución. Suscríbete a nuestro blog y sé el primero en enterarte de la promoción especial de lanzamiento.