Obsesionados con la fragmentación

Obsesionados con la fragmentación

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.

 

Cuello de botella y SQL Server… ¿hora de modernizar la red?

Cuello de botella y SQL Server… ¿hora de modernizar la red?

El impacto de una red de comunicaciones obsoleta es cada vez mayor en nuestros SQL Server a medida que otros subsistemas que típicamente producían el fenómeno llamado cuello de botella, el subsistema de disco especialmente, van mejorándose.

También el aumento del volumen de datos o el uso de funcionalidades de replicación/extracción de datos pueden convertir la red en el cuello de botella principal. Esto como veremos parece bastante “incomprensible” en una infraestructura equilibrada, dada la gran evolución que hemos vivido estos últimos años tecnológicamente hablando.

(más…)

Sysadmin o no sysadmin: Impacto de las políticas de seguridad en entornos SQL Server

Sysadmin o no sysadmin: Impacto de las políticas de seguridad en entornos SQL Server

Más frecuentemente de lo que desearíamos nos encontramos con sistemas con problemas de rendimiento donde no se siguen unas buenas políticas de seguridad. Esto acaba generando que una parte muy significativa de la carga se ejecuta como sysadmin, excluida por tanto de la evaluación de permisos ya que, como sysadmin, toda operación está permitida.

(más…)

Chaos Monkey en SQL Server

Chaos Monkey en SQL Server

Corría el año 2011 cuando Netflix se encontraba migrando sus servicios a la nube cuando detectó que era difícil probar/testear el comportamiento de la plataforma ante situaciones inesperadas. Por ello diseñaron un sistema al que llamaron Chaos Monkey que se encargaba de introducir errores de forma aleatoria, buscando “perturbar” el buen funcionamiento de la plataforma ante fallos.

(más…)

Optimizaciones «boomerang»

Optimizaciones «boomerang»

En ocasiones nos encontramos situaciones bastante anómalas que requieren análisis “think out of the box” por llamarlos de alguna forma. En este caso vamos a tratar un escenario que hemos llamado optimizaciones «boomerang» donde podemos encontrarnos que tras optimizaciones (indexación, parametrización, reescritura de queries, etc.) el consumo global de CPU disminuye y contrariamente a lo previsto, los tiempos de ejecución de ciertos procesos aumentan.

(más…)

Azure SQL Database Serverless

Azure SQL Database Serverless

Una de las últimas novedades que Microsoft nos ha presentado en Azure es Azure SQL Database Serverless. Esta es una funcionalidad que muchos entornos, especialmente de desarrollo, testing, han estado demandando desde hace tiempo. También debemos tener en cuenta que esta funcionalidad existía en competidores como Amazon Aurora Serverless desde hace un tiempo por lo que era algo natural que MS ofreciera algo similar.

(más…)

Planes de ejecución mutables

Planes de ejecución mutables

En general casi todos los DBAs de SQL Server tienen grabadas a fuego las fases por las que pasamos desde que lanzamos una consulta hasta que comienza su ejecución. El proceso básicamente arranca con un paseo inicial, donde detectaremos por ejemplo los errores de sintaxis. A continuación, se validará que los objetos referenciados existen, que los tipos de datos encajan, etc. (metadata) y comenzaremos la fase de optimización. Esta fase de optimización puede ser eludida si previamente tenemos un plan ya cacheado, pero en todo caso tras la optimización obtendremos un plan de ejecución válido que podremos ejecutar:

(más…)

No, no todas las máquinas virtuales son iguales

No, no todas las máquinas virtuales son iguales

Es muy habitual que nos encontremos en nuestros clientes situaciones de lo más variopintas en cuanto al rendimiento de las máquinas virtuales con SQL Server. En muchos casos lo que encontramos son casos donde el rendimiento está lejos del ideal, pero en general la virtualización en sí, no tiene la culpa. Lo que suele ocurrir es que cuando virtualizamos un SQL Server que teníamos en máquina física pasamos a disponer de una cantidad máxima o puntual de recursos (CPU/memoria/IO) sustancialmente distinta a las que teníamos en la máquina física. (más…)

Latencias de red < 1ms, ¿es suficiente para un buen rendimiento de SQL Server?

Latencias de red < 1ms, ¿es suficiente para un buen rendimiento de SQL Server?

Poco a poco, a medida que el almacenamiento se vuelve más rápido, va popularizándose el almacenamiento SSD local, etc. los tiempos de acceso a disco van bajando sustancialmente. El máximo exponente en este sentido lo encontramos en sistemas con SSDs Optane que se caracterizan por tener unas latencias de lectura/escritura mucho más bajas que los SSD tradicionales y además ir directamente conectados al bus PCIe: (más…)

La latencia, el archienemigo de los entornos Hybrid Cloud

La latencia, el archienemigo de los entornos Hybrid Cloud

En los últimos años vamos viendo cada vez más entornos híbridos donde algunos SQL Server se desplazan a la nube. En estos escenarios otras aplicaciones, servicios, ERPs o incluso otras instancias SQL Server siguen viviendo OnPremise en el datacenter original. Esta situación implica que cuando existan conexiones entre ambos entornos, las conexiones tendrán, en comparación con las conexiones que no crucen entre entornos, restricciones de ancho de banda y latencias mayores. (más…)