Cuando diseñamos nuestra estrategia de copias de seguridad en SQL Server, es esencial considerar no solo la integridad de los datos, sino también la eficiencia de los procesos. Quiero decir, además de la retención de los backups y factores como RPO y RTO que siempre tenemos en cuenta tenemos que pensar también en el rendimiento. En este sentido, ya hicimos un video sobre cómo afectaba la compresión de los backups a los tiempos de copia y restauración, ¿lo recuerdas? Lo tienes aquí por si quieres revisarlo.
Hoy, sin embargo, vamos a ir un paso más allá con esto del rendimiento de las copias de seguridad y vamos a ver tres opciones avanzadas que pueden marcar la diferencia en los tiempos y la utilización de recursos; estoy hablando de BLOCKSIZE, MAXTRANSFERSIZE y BUFFERCOUNT. Vamos a ver cómo funcionan y cómo optimizarlas.
Introducción al funcionamiento de las opciones de backups
Para entender estos complejos conceptos lo más simple posible tenemos que empezar pensando en el proceso de backup en SQL Server como en la transferencia de datos desde la base de datos hacia un destino de almacenamiento. Durante este flujo, como en cualquier transferencia de información informática, el tamaño del bloque, la cantidad de buffers y la cantidad de datos transferidos por operación son factores clave que pueden afectar considerablemente el rendimiento.
Entonces, tenemos por un lado el parámetro BLOCKSIZE que define el tamaño del bloque de datos utilizado en la operación de backup, MAXTRANSFERSIZE que determina el tamaño máximo de los datos que se transfieren en una única operación de I/O y BUFFERCOUNT que especifica cuántos buffers se asignan para la operación.
BLOCKSIZE
Como acabamos de comentar el parámetro BLOCKSIZE define el tamaño, en bytes, de los bloques que se utilizan para escribir datos en el medio de almacenamiento durante el backup.
De manera predeterminada, y si no modificamos nada tendrá un tamaño de 64 KB. Sin embargo, podemos modificarlo, admitiendo valores que pueden oscilar entre 512 bytes y un máximo de 4 MB.
Un BLOCKSIZE mayor puede resultar en un uso más eficiente del disco, especialmente en sistemas con discos de alta velocidad y controladores optimizados.
Sin embargo, no todos los dispositivos admiten tamaños de bloque personalizados. Es vital verificar la compatibilidad con el hardware subyacente.
Ejemplo de uso:
BACKUP DATABASE MiBaseDatos
TO DISK = 'C:\Backups\MiBaseDatos.bak'
WITH BLOCKSIZE = 65536; -- Tamaño del bloque especificado en bytesMAXTRANSFERSIZE
Este parámetro controla la cantidad máxima de datos transferidos entre SQL Server y el medio de almacenamiento en una sola operación de I/O. Tiene un rango de valores posibles desde el mínimo 64 KB hasta un máximo de 4 MB (desde SQL Server 2012).
Un MAXTRANSFERSIZE mayor puede reducir la cantidad de operaciones de I/O, mejorando la velocidad del backup. Aumentar este valor puede ser beneficioso sobre todo en dispositivos con alto rendimiento de escritura secuencial como los actuales discos SSD. Pero cuidado, configurar valores altos puede requerir más memoria en el servidor, lo que podría ser contraproducente en sistemas con recursos limitados.
Ejemplo de uso:
BACKUP DATABASE MiBaseDatos
TO DISK = 'C:\Backups\MiBaseDatos.bak'
WITH MAXTRANSFERSIZE = 1048576; -- 1 MB BUFFERCOUNT
Acabamos de hablar de la memoria y para optimizar este recurso y no tener problemas tenemos este último parámetro. BUFFERCOUNT define el número de buffers de memoria que se utilizarán durante la operación de backup. Es importante definirlo correctamente, sobre todo si hemos modificado los parámetros anteriores.
Una mala configuración de BUFFERCOUNT nos puede dar muchos dolores de cabeza, por ejemplo valores bajos nos pueden provocar cuellos de botella si el flujo de datos excede la capacidad de los buffers disponibles y, sin embargo, unos valores altos aunque aprovechan al máximo la memoria disponible, deben equilibrarse con otros procesos en ejecución o usurparán sus recursos. Por suerte, tenemos una fórmula básica para calcular BUFFERCOUNT:
BUFFERCOUNT = (MAXTRANSFERSIZE / BLOCKSIZE) * número de hilos.Ejemplo de uso:
BACKUP DATABASE MiBaseDatos
TO DISK = 'C:\Backups\MiBaseDatos.bak'
WITH BUFFERCOUNT = 16;Cómo optimizar tu backups
Ahora que ya hemos visto las tres configuraciones por sepradao vamos a ver como aplicarlas juntas. Esta es la clave ya que el rendimiento de los backups depende de cómo se ajustan estas tres opciones en conjunto.
Lo primero que debemos hacer es analizar nuestro hardware.Si el sistema tiene discos rápidos y suficiente memoria, aumentar BLOCKSIZE y MAXTRANSFERSIZE puede sernos ventajoso. En sistemas con I/O limitado, priorizar un BUFFERCOUNT ajustado puede equilibrar la carga y ayudarnos a no impactar en otras operaciones.
En cualquier caso, es fundamental probar diferentes combinaciones en un entorno de prueba, lo más parecido al real posible, para determinar qué configuración ofrece el mejor rendimiento.
Lo cierto es que aunque SQL Server utiliza valores predeterminados razonables, ajustar estas opciones para nuestro escenario concreto puede ser crucial, sobre todo en bases de datos grandes o sistemas críticos.
Ejemplo completo:
BACKUP DATABASE MiBaseDatos
TO DISK = 'C:\Backups\MiBaseDatos_Optimizado.bak'
WITH
BLOCKSIZE = 65536, -- 64 KB
MAXTRANSFERSIZE = 1048576, -- 1 MB
BUFFERCOUNT = 32;En este ejemplo el BLOCKSIZE de 64 KB se combina con el MAXTRANSFERSIZE de 1 MB.
El BLOCKSIZE de 64 KB es el adecuado si hacemos nuestros backups en un disco de los formateados según las buenas prácticas de SQL Server. Recordad que en estos discos definimos un tamaño de bloque de 64 KB que es justo lo que ocupa un EXTEND, es decir un bloque de 8 páginas cada una de 8 KB. El MAXTRANSFERSIZE se ajusta a 1 MB para permitir que cada operación de I/O mueva datos en bloques razonablemente grandes, optimizando las escrituras en disco.
Ahora, si para estas operaciones de backup queremos aplicar 2 hilos, es decir dos núcleos virtuales del procesador, aplicamos la fórmula que hemos visto antes y nos da ese resultado.
32 = ( 1048576 / 65536 ) * 2Conclusión
Las opciones BLOCKSIZE, MAXTRANSFERSIZE y BUFFERCOUNT nos ofrecen un control detallado sobre el rendimiento de las operaciones de backup en SQL Server. Aprovecharlas de manera efectiva requiere un análisis cuidadoso del entorno y pruebas específicas hasta dar con la mejor combinación. Pero merece la pena, en bases de datos críticas y de gran tamaño, estos ajustes pueden marcar una diferencia significativa, reduciendo los tiempos de los backups y optimizando el uso de recursos.
Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!
