Rendimiento

Feb

2025

Niveles de compatibilidad de las bases de datos

Hoy me he encontrado con un caso que es más habitual del que me gustaría y quería compartirlo con vosotros y ver qué podemos aprender de él. Estaba revisando el servidor de un cliente que nunca ha tenido un DBA, era un SQL Server 2022 Standard que había montado su equipo de IT. El caso es que, aun siendo un SQL Server 2022, todas las bases de datos estaban en nivel de compatibilidad 100, es decir en el modo de funcionamiento propio de SQL Server 2008. Al preguntar, me han contado que desde su primer SQL Server 2008 su informático siempre había ido aplicando las actualizaciones y subidas de versiones cuando era necesario pero claro, no sabían que había que cambiar nada en las bases de datos.

Este caso que os cuento no es excepcional, y muchos administradores de bases de datos han pasado por situaciones similares. Tener bases de datos en un SQL Server moderno pero con un nivel de compatibilidad antiguo es más común de lo que parece. Pero, ¿qué implica esto realmente? ¿Por qué es importante gestionar correctamente los niveles de compatibilidad? Vamos a profundizar en este tema y en las consideraciones clave antes de realizar cualquier cambio.

¿Qué es el Nivel de Compatibilidad en SQL Server?

El nivel de compatibilidad de una base de datos en SQL Server define qué conjunto de características y comportamientos del motor de base de datos están habilitados para esa base de datos en particular. Microsoft SQL Server introduce constantemente nuevas funcionalidades y mejoras en el motor de consulta con cada versión. Sin embargo, para evitar problemas de compatibilidad con aplicaciones antiguas, permite mantener una base de datos funcionando con un conjunto de reglas de versiones anteriores.

Por ejemplo, una base de datos con nivel de compatibilidad 100 (SQL Server 2008) ejecutada en un SQL Server 2022 seguirá comportándose en muchos aspectos como si estuviera en SQL Server 2008. Esto puede parecer una buena idea para garantizar que las consultas y procedimientos almacenados antiguos sigan funcionando sin problemas, pero también significa renunciar a muchas mejoras de rendimiento y nuevas funcionalidades del motor de base de datos.

¿Por qué es importante el Nivel de Compatibilidad?

El nivel de compatibilidad afecta aspectos fundamentales del comportamiento de SQL Server, incluyendo el optimizador de consultas, nuevas funciones y sintaxis de T-SQL y mejoras en la seguridad y el rendimiento general.

Como sabéis, SQL Server introduce mejoras constantes en el optimizador de consultas en cada nueva versión del producto. Mantener un nivel de compatibilidad antiguo significa que las consultas podrían no beneficiarse de los nuevos algoritmos y estrategias de ejecución de consultas. Además, algunas funciones y tipos de datos más modernos pueden no estar disponibles en niveles de compatibilidad antiguos.

En cuanto a seguridad, Microsoft mejora continuamente la seguridad en SQL Server, y algunas de estas mejoras solo están disponibles en niveles de compatibilidad más recientes. Lo mismo pasa con el rendimiento, características como Batch Mode on Rowstore, Adaptive Query Processing, Memory Grant Feedback, entre muchas otras, solo están habilitadas en niveles de compatibilidad más recientes.

El cambio clave con el Nivel de Compatibilidad 120 (SQL Server 2014)

Uno de los cambios más significativos en la historia de los niveles de compatibilidad de SQL Server ocurrió con SQL Server 2014 (Nivel 120). Microsoft rediseñó por completo el comportamiento del optimizador de consultas con la introducción del nuevo estimador de cardinalidad.

¿Qué es el estimador de cardinalidad y por qué es importante?

El estimador de cardinalidad es el componente del optimizador de consultas que predice cuántas filas se van a procesar en cada paso de una consulta. Estas predicciones, basadas entre otras cosas en las estadísticas, influyen directamente en la selección de planes de ejecución eficientes.

Con SQL Server 2014, Microsoft cambió la forma en que se estiman las filas, lo que en muchos casos mejoró el rendimiento, pero en otros, los menos, generó degradaciones inesperadas. Por eso, al cambiar el nivel de compatibilidad de 110 (SQL Server 2012) a 120 (SQL Server 2014), algunos planes de ejecución cambiaron drásticamente. Si tu base de datos aún se encuentra en un nivel de compatibilidad antiguo (110 o inferior), al actualizar a 120 o superior, es crucial revisar los planes de ejecución antes de aplicar el cambio en producción.

Impacto del Nivel de Compatibilidad 120 en los planes de mantenimiento

Otro cambio relevante con SQL Server 2014 y su nivel de compatibilidad 120 fue cómo se comportaban los planes de mantenimiento. Antes de SQL Server 2014, muchas bases de datos dependían de planes de mantenimiento que ejecutaban reconstrucción y reorganización de índices, estadísticas de actualización y otras tareas rutinarias de mantenimiento. Sin embargo, al actualizar a SQL Server 2014 con nivel de compatibilidad 120, muchos de estos procesos cambiaron significativamente debido a un nuevo comportamiento en la actualización de estadísticas

En versiones anteriores, las estadísticas se actualizaban con una heurística más básica basada en el número de cambios en los índices. A partir de SQL Server 2014, Microsoft introdujo un algoritmo mejorado que ajusta automáticamente la frecuencia de actualización de estadísticas en función de la variabilidad de los datos. Esto significa que algunos planes de mantenimiento antiguos pueden volverse ineficientes, ya que las estadísticas pueden actualizarse con menor frecuencia de lo esperado.

Cambios en la fragmentación de índices

También, antes de SQL Server 2014, los DBA solíamos programar reconstrucción y reorganización de índices en intervalos fijos. Sin embargo, con el nuevo estimador de cardinalidad, algunas consultas que antes se beneficiaban de la reconstrucción de índices ya no requieren mantenimiento tan frecuente, mientras que otras pueden necesitar ajustes más específicos.

Nuevo comportamiento en las esperas de consultas y la concesión de memoria

SQL Server 2014 introdujo Memory Grant Feedback, que ajusta dinámicamente la memoria asignada a las consultas. Este cambio afectó la manera en que los planes de mantenimiento deben ejecutarse en bases de datos de gran tamaño, ya que ahora SQL Server aprende con el tiempo y puede mejorar la asignación de memoria en ejecuciones repetitivas. Sin embargo, si por una actualización de estadísticas o mantenimiento de índices el plan se recompila todos esos cálculos ya no valen.

¿Cómo adaptar los planes de mantenimiento?

Si decides actualizar el nivel de compatibilidad a 120 o superior, es recomendable revisar y adaptar los planes de mantenimiento de la base de datos para evitar ineficiencias. Lo primero que tienes que hacer es revisar la estrategia de actualización de estadísticas.

En lugar de una actualización forzada en cada ciclo, es mejor (generalmente) dejar que SQL Server maneje esto dinámicamente. Por supuesto, siempre evalúa el impacto real en las consultas antes de aplicar actualizaciones manuales con UPDATE STATISTICS.

En cuanto a la reconstrucción de índices podemos utilizar la DMV sys.dm_db_index_physical_stats para analizar si realmente es necesario reconstruir índices. Y, como hemos dicho siempre que hablamos de mantenimiento de índices, ciertas tablas pueden beneficiarse más de una reorganización en lugar de una reconstrucción completa. Tendremos que valorar el nivel de mantenimiento en función de la fragmentación del índice.

En cualquier caso, Query Store puede ayudarnos a detectar cambios drásticos en planes de ejecución antes y después de cambiar el nivel de compatibilidad. Podemos incluso usar la opción de force plan si una consulta se ve afectada negativamente tras el cambio.

Consideraciones antes de cambiar el Nivel de Compatibilidad

A modo resumen de lo que llevamos hasta aquí, antes de modificar el nivel de compatibilidad de una base de datos, debemos hacer una serie de verificaciones y pruebas para asegurarnos de que el cambio no afectará negativamente a la operativa.

Analizar dependencias y compatibilidad con las aplicaciones
Revisar consultas y procedimientos almacenados
Ejecutar pruebas de rendimiento en un entorno de pruebas
Usar Query Store para comparar planes de ejecución
Ajustar planes de mantenimiento según el nuevo comportamiento de SQL Server

Cómo cambiar el Nivel de Compatibilidad

Para cambiar el nivel de compatibilidad de una base de datos en SQL Server, podemos hacerlo desde las propiedades de la base de datos en SSMS en la pestaña “Opciones”. También lo puedes hacer utilizando la siguiente instrucción T-SQL:

ALTER DATABASE NombreBaseDatos SET COMPATIBILITY_LEVEL = 150;

En este ejemplo 150 es para SQL Server 2019, los niveles de compatibilidad son los siguientes:

También podemos verificar el nivel de compatibilidad actual para todas las bases de datos con esta consulta:

SELECT name, compatibility_level FROM sys.databases

Conclusión

El nivel de compatibilidad en SQL Server no es solo una configuración más; afecta directamente al rendimiento, comportamiento del optimizador de consultas y eficiencia de los planes de mantenimiento. Con el cambio introducido en SQL Server 2014, muchas bases de datos experimentaron transformaciones en sus planes de ejecución y rutinas de mantenimiento. Antes de hacer cualquier modificación, es clave realizar pruebas exhaustivas y ajustar estrategias de optimización. Si vamos a actualizar, hagámoslo bien y con un plan claro para minimizar riesgos y aprovechar al máximo las mejoras de SQL Server.

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!

Feb

2025

Asignación AVANZADA de páginas en SQL Server

En nuestro pasado artículo vimos como SQL Server organizaba el almacenamiento de los datos en páginas de datos y como lo administraba gracias a páginas de metadatos. En ese artículo, os prometí que hablaríamos más de estas páginas de asignación. Es muy importante entender estos conceptos pues son imprescindibles para poder hacer una optimización avanzada y enfrentar con solvencia problemas complejos de almacenamiento.

Como ya vimos en el pasado artículo, SQL Server administra el almacenamiento mediante estructuras internas diseñadas para optimizar la asignación y reutilización del espacio. Dentro de estas estructuras, las páginas de asignación juegan un papel clave en la gestión de las páginas y extensiones dentro de los archivos de datos (.mdf o .ndf). Estas páginas permiten un acceso eficiente a los datos y minimizan la fragmentación, asegurando el mejor uso posible del espacio disponible.

En este artículo, profundizaremos en la función y el comportamiento de las páginas de asignación en SQL Server, complementando la información que vimos y ajustando los detalles donde sea necesario para proporcionarnos una visión más completa.

Estructuras de asignación de espacio en SQL Server

Resumamos un poco lo que vimos en el pasado artículo. SQL Server organiza el almacenamiento en páginas de 8 KB, que se agrupan en extents de 64 KB (8 páginas). Para administrar la asignación de estas páginas y extensiones, SQL Server utiliza las siguientes estructuras especializadas:

PFS (Page Free Space) → Rastreo del uso del espacio en páginas individuales.
GAM (Global Allocation Map) → Rastreo de extensiones asignadas y libres.
SGAM (Shared Global Allocation Map) → Rastreo de extensiones mixtas con espacio libre.
IAM (Index Allocation Map) → Mapeo de asignaciones de páginas y extensiones a objetos específicos.

Ahora que ya hemos refrescado ideas vamos a ver en detalle cada una de estas estructuras. Pero antes una pequeña aclaración. Todos los archivos de datos (mdf y ndf) empiezan siempre con la misma estructura, una primera página (página 0) con cabeceras, y a continuación una página PFS, una GAM y una SGAM (página 1, 2 y 3 respectivamente)

Páginas PFS (Page Free Space)

Para gestionar correctamente la asignación de almacenamiento es imprescindible tener controlado en todo momento cuánto espacio tienes libre. Las PFS controlan cuánto espacio libre hay dentro de cada página individual en un archivo de base de datos. Son fundamentales para la inserción eficiente de datos, ya que SQL Server puede identificar rápidamente qué página tiene espacio disponible para nuevas filas sin necesidad de asignar nuevas páginas.

Características de las páginas PFS

Cada página PFS rastrea el estado de 8,088 de datos (~64 MB de datos). Es decir, si la página PFS es la página 1 del archivo de datos nos vamos a encontrar con una de estas cada 8088 páginas de datos,

Estas PFS se actualizan cada vez que una página de datos cambia de estado, por ejemplo, cuando se insertan o eliminan filas.

La información se almacena en bits dentro de la página PFS, con los siguientes valores:

0x00 → Página vacía.
0x40 → Página 1-50% ocupada.
0x80 → Página 51-100% ocupada.
0xC0 → Página completamente llena.
0xE0 → Página reservada para IAM u otros propósitos.

Ubicación de las páginas PFS en el archivo de datos

SQL Server es capaz de registrar con un Byte de información la página y su estado por lo que en una página PFS que es de 8KB podremos almacenar información de 8 * 1024 páginas de datos. Esto significa que las PFS aparecen cada 8,088 páginas en un archivo de datos. Por ejemplo, si la primera página PFS sabemos que está en la página 1, la siguiente estará en la página 8,089, la siguiente en la página 16,177, y así sucesivamente.

Consulta para inspeccionar páginas PFS

Podemos analizar las PFS con DBCC PAGE pero para ello vamos a tener que activar primero la traza 3604 para ver el resultado en SSMS y que no vaya al log de errores de SQL Server. Al comando DBCC PAGE le vamos a pasar como parámetros primero el ID de la base de datos, seguido del ID del fichero de datos, el número de página que queremos ver y un 3.

DBCC TRACEON(3604);
DBCC PAGE(6, 1, 1, 3);

Páginas GAM (Global Allocation Map)

Las GAM rastrean la asignación de extensiones dentro del archivo de base de datos. Permiten a SQL Server identificar rápidamente qué extensiones están libres y cuáles han sido asignadas a objetos.

Características de las páginas GAM

Cada página GAM cubre 511,232 de datos (unos 3 GB de datos).

Utiliza 1 bit por extensión:
1 → Extensión libre.
0 → Extensión asignada.

Como sabéis, SQL Server no libera el espacio cuando los datos se borran sino que se lo queda y lo marca como disponible. Pues bien, GAM se usa cuando SQL Server necesita encontrar espacio disponible y no asignado para nuevos objetos.

Cada página GAM es una página de 8 KB (como todas) que contiene información de 63,904 extents (ya que cada bit representa un extent). Como cada extent tiene 8 páginas, esto significa que una página GAM puede rastrear 511,232 páginas, lo que equivale a aproximadamente 3 GB de almacenamiento. Pero no tienes por qué creer ciegamente en lo que yo te diga, vamos a hacer los números.

1 GAM = 8 KB = 8 * 1024 bytes = 8192 bytes
De estos 8192 bytes, 204 bytes están reservados por lo que quedan 7988 en una página GAM. ¿Cuántas páginas de datos pueden cubrir 7988 bytes?
1 GAM = 7988 bytes = 7988 * 8 bits = 63904 bits
Entonces si una página GAM tiene 63904 bits y necesita 1 bit para registrar 1 extent eso significa que puede almacenar información de 63904 extents. Convirtamos eso en tamaño.
1 GAM = 63904 extents = 63904 * 8 páginas = 511232 páginas
11232 páginas * 8 KB = 4,089,856 KB
4,089,856 KB / 1024 = 3994 MB / 1024 = 3,3144 GB

Ubicación de las páginas GAM en el archivo de datos

Si ya sabemos que las GAM aparecen cada 511,232 páginas y que la primera GAM está en la página 2, podemos saber que la siguiente en la página 511,234, etc.

Consulta para inspeccionar GAM

Igual que hemos hecho antes podemos usar DBCC PAGE para ver las GAM.

DBCC TRACEON(3604);
DBCC PAGE(6, 1, 2, 3);

Páginas SGAM (Shared Global Allocation Map)

Las SGAM funcionan de manera complementaria a las GAM y se utilizan para rastrear extents mixtos que aún tienen espacio disponible para nuevos datos.

Características de SGAM

Cada página SGAM cubre 511,232 páginas ( unos 3 GB de datos), igual que GAM. También utiliza 1 bit por extensión:
1 → Extensión mixta con espacio libre.
0 → Extensión completamente ocupada o no mixta.

SGAM se usa cuando SQL Server necesita encontrar espacio en extensiones mixtas para insertar nuevos datos sin asignar nuevas páginas.

Ubicación de las SGAM en el archivo de datos

Las SGAM aparecen cada 511,232 páginas, comenzando en la página 3, luego en la página 511,235, y así sucesivamente.

Consulta para inspeccionar SGAM

DBCC TRACEON(3604);
DBCC PAGE(6,1,3.,3);

Páginas IAM (Index Allocation Map)

Las IAM rastrean qué páginas y extensiones pertenecen a un objeto específico, como una tabla o un índice. Cada página IAM cubre un solo objeto (una tabla o un índice) en un solo archivo. Incluso puede abarcar múltiples páginas y archivos si el objeto está distribuido.

Las IAM almacenan la relación entre los objetos y sus páginas de datos distribuidas en el archivo de datos. A diferencia de las GAM y SGAM, las IAM no tienen una ubicación fija dentro del archivo, ya que se crean dinámicamente para cada objeto.

Consulta para inspeccionar IAM

En este caso es mucho más complicado porque las IAM no tienen ubicación fija pero si supiesemos el número de página podremos consultarlo igual con DBCC PAGE y en el resultado podríamos ver que páginas y extents pertenecen a ese objeto.

DBCC TRACEON(3604); 
DBCC PAGE(6, 1, NúmeroDePáginaIAM, 3);

Conclusión

SQL Server administra el almacenamiento de manera eficiente utilizando páginas de asignación especializadas como PFS, GAM, SGAM e IAM. Estas estructuras permiten al motor de base de datos optimizar el uso del espacio, minimizar la fragmentación y mejorar el rendimiento. Comprender su funcionamiento es clave para administrar bases de datos de manera óptima y solucionar problemas de asignación de espacio en SQL Server.

Feb

2025

Gestión de almacenamiento en SQL Server

¿Alguna vez te has preguntado cómo hace SQL Server para gestionar los datos en disco? Y no sólo SQL Server, la administración eficiente del almacenamiento es fundamental para garantizar un rendimiento óptimo y una gestión eficaz de los datos en cualquier sistema gestor de bases de datos. El motor de base de datos tiene que utilizar estructuras bien definidas, como páginas y extensiones, para organizar y controlar el espacio en disco. Para nosotros, como DBAs o usuarios avanzados, comprender en profundidad estas estructuras y sus mecanismos asociados es esencial para diseñar, implementar y mantener bases de datos de alto rendimiento.

Páginas, la unidad atómica de almacenamiento en SQL Server

Una página es la unidad básica de almacenamiento en SQL Server, en estas páginas se guardan nuestros datos y tienen un tamaño fijo de 8 KB, siempre tienen el mismo tamaño.

Cada página comienza con un encabezado de 96 bytes que contiene información crucial, como el número de página, el tipo de página, la cantidad de espacio libre y el ID de la unidad de asignación del objeto propietario. Las filas de datos se almacenan secuencialmente después del encabezado, y al final de la página se encuentra una tabla de desplazamiento de filas que facilita el acceso rápido a cada fila.

Tipos de Páginas en función su almacenamiento

SQL Server utiliza diversos tipos de páginas para almacenar diferentes tipos de información.

Aunque el usuario medio podría pensar que todo lo que hay son páginas de datos lo cierto es que este es solo un tipo de página. En concreto, son las páginas que almacenan las filas de datos de las tablas pero, excluyendo los datos de tipos como text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml cuando la opción «text in row» está activada. Esos datos veremos que van a otro tipo especial de página.

En concreto a páginas de texto o imagen que son las encargadas de almacenar datos de objetos grandes (LOB), incluyendo text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml.

Para cerrar con las páginas que almacenan datos tenemos que nombrar las páginas de índice que contienen esas entradas de índices que facilitan el acceso rápido a los datos.

Pero esto no es todo, no sólo hay páginas con nuestros datos, SQL Server necesita una serie de metadatos para poder gestionar todo esto y los almacena en las páginas de asignación. En este tipo de páginas incluimos las páginas GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), IAM (Index Allocation Map) y PFS (Page Free Space), que gestionan la asignación y el seguimiento del espacio en la base de datos.

Extensiones, como realmente SQL Server gestiona el almacenamiento

Una extensión (lo encontrarás también como extent) es un conjunto de ocho páginas físicamente contiguas, totalizando 64 KB. Las extensiones son la unidad básica mediante la cual SQL Server gestiona el espacio de almacenamiento.

Permitidme hacer un parón en este punto para interiorizar esto, vuelve a leer el párrafo anterior y grábatelo a fuego así, la próxima vez que tengas que preparar un nuevo disco para almacenar datos de SQL Server entenderás por qué es una buena práctica formatear los discos con un tamaño de asignación de bloques de 64 KB.

Ahora sé, volvemos con los extent y, nuevamente, nos encontramos con que existen varios tipos. En este caso, no dependen del tipo de los datos que estamos almacenando sino de si son o no dedicados para un objeto.

De esta manera, podemos encontrar extensiones uniformes donde las ocho páginas son propiedad de un único objeto o extensiones mixtas donde las ocho páginas pueden ser compartidas por hasta ocho objetos diferentes, permitiendo una asignación eficiente para objetos pequeños. Es importante destacar que aunque el extent puede ser mixto, las páginas siempre son dedicadas al 100% para el mismo objeto.

A partir de SQL Server 2016, veremos que el comportamiento predeterminado es asignar extensiones uniformes para la mayoría de las operaciones. En principio para optimizar el rendimiento pero bueno, eso es un tema discutible.

Gestión del almacenamiento

Como hemos comentado antes, SQL Server emplea varias estructuras internas (páginas de asignación) para gestionar la asignación y el uso del almacenamiento. Aquí va un pequeño resumen aunque ya os adelanto que a este tema le vamos a dedicar un artículo completo porque tiene mucho de lo que hablar.

Páginas GAM: mapa global de asignación de almacenamiento

Las páginas GAM (Global Allocation Map) registran qué extents han sido asignadas en el archivo de datos. Cada bit en una página GAM representa una extensión: un bit en 1 indica que la extensión está libre, mientras que un bit en 0 señala que está asignada. Cada página GAM puede rastrear aproximadamente 4 GB de datos, cubriendo 64,000 extents.

Páginas SGAM: mapa global de almacenamiento compartido

Las páginas SGAM (Shared Global Allocation Map) indican qué extents mixtos tienen al menos una página disponible para su uso. Un bit en 1 significa que la extensión mixta tiene espacio libre, mientras que un bit en 0 indica que todas sus páginas están ocupadas o que no es una extensión mixta. Al igual que las páginas GAM, cada página SGAM cubre cerca de 4 GB de datos.

Páginas PFS: almacenamiento disponible

Las páginas PFS (Page Free Space) rastrean el estado de cada página individual en términos de asignación y espacio libre. Cada bit en una página PFS representa una página de datos y señala si está libre, parcialmente llena o completamente ocupada. Cada página PFS puede rastrear 8,088 páginas, lo que equivale aproximadamente a 64 MB de datos.

Páginas IAM: Mapa de asignación de índices

Las páginas IAM (Index Allocation Map) mapean las extensiones utilizadas por un objeto específico, como una tabla o un índice, en una unidad de asignación particular. Estas páginas permiten a SQL Server identificar rápidamente qué páginas y extensiones pertenecen a cada objeto, facilitando operaciones eficientes de lectura y escritura.

Consideraciones Avanzadas

Antes de cerrar este artículo quería remarcar unos conceptos sobre los que he podido pasar de manera tangencial antes.

De cara a entender la compatibilidad con filas largas, aunque una fila de datos no puede dividirse entre múltiples páginas, SQL Server maneja columnas de longitud variable que pueden exceder los 8 KB moviendo parte de los datos a páginas especiales denominadas ROW_OVERFLOW_DATA. Esto permite almacenar filas que, en conjunto, superan el tamaño de una página estándar.

Otra cosa que hemos comentado pero que quería matizar es que en versiones anteriores a SQL Server 2016, las tablas e índices pequeños comenzaban utilizando extensiones mixtas y, al crecer, se les asignaban extensiones uniformes. Desde SQL Server 2016, el comportamiento predeterminado es asignar extensiones uniformes desde el inicio para la mayoría de las operaciones, mejorando el rendimiento en la gestión del almacenamiento. Sin embargo, ahora mismo, con los discos SSD modernos, las lecturas secuenciales ya no representan tanta ventaja como cuando los discos eran mecánicos y esta supuesta ventaja en rendimiento puede ser un problema de desaprovechamiento del almacenamiento. Tampoco es que importe mucho, los discos duros, a la vez que se hacían más rápidos, también han visto crecer su capacidad total y el coste del almacenamiento se ha reducido mucho.

Conclusión

Una comprensión profunda de las estructuras de almacenamiento en SQL Server, incluyendo páginas, extensiones y mapas de asignación, es esencial para administradores y desarrolladores que buscan optimizar el rendimiento y la eficiencia de sus bases de datos. Al dominar estos conceptos, es posible diseñar estrategias de almacenamiento que maximicen el uso de los recursos y aseguren operaciones de base de datos robustas y escalables.

Ene

2025

Reduce el tiempo de tus BACKUPS a la mitad o más

La semana pasada publiqué un post sobre las configuraciones avanzadas de backups BLOCKSIZE, MAXTRANSFERSIZE y BUFFERCOUNT y cómo impactan en el rendimiento de nuestras copias de seguridad. A raíz de este artículo, muchos comentasteis una optimización mucho más sencilla y que mejora sustancialmente los tiempos de backup, separar las copias en varios ficheros. Y es cierto, incluso cuando no estás escribiendo estos múltiples ficheros en discos separados puedes ganar tiempo con esta configuración. Pero, ¿por qué? Veámoslo en profundidad. Además veremos otro truco que nos puede ahorrar gran cantidad de tiempo en estos procesos.

¿Por qué optimizar los backups?

A menudo no pensamos en que la optimización de rendimiento sea algo que afecte a las copias de seguridad y sin embargo es un campo donde podemos ganar mucho.

A ver, un momento, si trabajas con una base de datos pequeña o mediana (hasta 300Gb aprox) y tu empresa solo tiene necesidad de acceso a la base de datos en horas de oficina esto no es un problema, no te compliques más. Seguramente tengas tiempo durante la noche y los fines de semana para hacer todas las tareas de mantenimiento completas sin mucho más miramiento. Pero, a medida que los datos crecen o crecen la demanda de los datos acercándose al tan temido 24×7 te va a tocar hacer malabares con los tiempos de los planes de mantenimiento y ajustarlo todo de manera que impacte lo menos posible en el resto de actividad.

Vale, en estos casos lo mejor sería una aplicación externa de backup por snapshot para reducir el tiempo a casi 0 pero no seamos tan extremos, veamos qué podemos hacer con herramientas nativas.

Separar los backups en varios ficheros

Incluso cuando escribimos los distintos ficheros de copias de seguridad en el mismo disco duro físico o por la misma interfaz de red suele ser más rápido separar las copias de seguridad en varios ficheros. Esto pasa porque SQL Server tiene “cuellos de botella internos” (limitaciones) en la escritura a ficheros de copias de seguridad y al generar varios vamos a poder salvar en gran medida esas limitaciones.

Es cierto que a cambio vamos a tener que complicar un poco tanto el script de recuperación de la copia de seguridad como el de la restauración pero, ¿de verdad eso es importante? ¿Sigues escribiendo a mano los scripts de backup y restore en 2025? Amigo para eso existen soluciones como los script de Ola Hallengren o el maravilloso sp_DatabaseRestore de Brent Ozar.

Demostración práctica BACKUPS

Veamos cómo impacta en los tiempos el hecho de dividir las copias de seguridad. Para la prueba estoy usando la base de datos StackOverflow2013 de demo de 52 Gb que tiene 4 ficheros de datos y uno de log. Sobre el hardware de mi máquina de pruebas es una máquina con 8 cores (16 vCores), 32Gb de RAM y un disco SSD M2.Tanto los ficheros de datos como el de log y el backup están en la misma unidad, no es lo ideal pero es lo que tengo para esto.

En un primer intento he hecho un backup full sencillo, a un solo fichero y ha tardado 1:52 minutos, la misma prueba de backup pero con 2 ficheros ha tardado 0:49 minutos y sin embargo, en cuanto he puesto 4 ficheros la prueba se ha ido a 3:42 minutos. ¿Por qué estos resultados? En teoría os había dicho que SQL Server limita la cantidad de datos que escribe a un único fichero por lo que podríamos entender que a más ficheros menos tiempo y sin embargo aunque con 2 ficheros hemos bajado los tiempos con 4 se han disparado.

Esto es porque también tenemos que tener en cuenta las limitaciones de velocidad del disco, en mi caso donde además todos los ficheros de datos y log están en la misma unidad esto cobra más sentido. Durante la prueba con un fichero el uso de disco ha rondado el 30%, durante la segunda prueba entre el 65 y 70% y, en la prueba con 4 ficheros el consumo ha sido del 100% del disco. Por tanto, con 4 ficheros mi hardware ha sobrepasado su límite generando tiempos de espera por cuellos de botella en la E/S de disco.

Demostración práctica RESTORE

Ahora os comparto los resultados que he tenido en la restauración de estas copias que acabo de hacer. Para esta prueba todos los backups siguen en la misma unidad que los datos y los logs y la base de datos existente va a ser sobrescrita, es decir no hay que generar de nuevo los ficheros. ¿Qué pasará?

Aquí los tiempos se disparan, la restauración de la copia con un solo fichero ha tardado 7:18 minutos. La restauración de la copia con dos ficheros, por su parte, ha demorado 11:07. Por último la copia de 4 ficheros ha tomado 10:19 minutos para restaurarse.

Cabe destacar que todas las pruebas de restauración han tenido el uso de disco al 100% en todo momento por lo que no puedo dar por 100% fiables los datos al haber encontrado tán pronto el límite del hardware. Ya os había dicho que la configuración de todo en el mismo disco no es una buena idea.

Bonus extra: Verificar los backups

Otra de las cosas que seguro que estás haciendo es verificar los backups a la hora de hacerlos. Verificar los backups no es que sea una buena práctica es que es imprescindible si queremos estar seguros y cumplir con la normativa vigente para muchas empresas. Como se suele decir, un backup sin probar no es un backup, es como el gato de Schrödinger (claro que he tenido que buscar en google como se escribe).

Sin embargo, que tengamos que comprobar nuestras copias de seguridad no significa que debamos hacerlo al momento de hacer la copia, ni siquiera significa que debamos hacerlo en el mismo servidor.

Si nuestros backups están en una unidad de red vamos a poder probarlas de manera independiente y en una máquina distinta al servidor de producción (por ejemplo el servidor de DR o el de pruebas) vamos a poder ganar un 30% o más del total de tiempo de la tarea de copias de seguridad. Incluso, podemos hacer uso del procedimiento sp_DatabaseRestore que he mencionado antes y hacer un CheckDB a la base de datos en este proceso separado de verificación. ¿Te das cuenta de lo que te estoy diciendo, verdad? Más seguridad y mejor rendimiento sin apenas complicarte.

Conclusión

Optimizar los procesos de backups no es solo cuestión de ahorrar tiempo, sino también de garantizar que nuestro entorno sea resiliente, eficiente y cumpla con los estándares de seguridad. A través de ajustes relativamente simples, como dividir los backups en múltiples ficheros o separar la verificación en un proceso independiente, podemos obtener grandes beneficios sin necesidad de recurrir a herramientas externas costosas.

Sin embargo, como hemos visto en las pruebas, no existe una configuración única que funcione para todos los escenarios. Cada entorno tiene sus propias limitaciones, ya sea por el hardware, la arquitectura de los datos o los requisitos operativos. Por eso, es fundamental medir, analizar y ajustar las configuraciones basándonos en pruebas reales. La clave está en encontrar el equilibrio entre el rendimiento y la fiabilidad, adaptando las estrategias a las características de nuestros sistemas.

En resumen, la optimización no siempre implica complejidad, y pequeños cambios pueden marcar una gran diferencia. Ahora te toca a ti: ¿qué ajustes has probado en tus backups? ¿Qué resultados has obtenido? Como siempre, la mejor forma de aprender es compartiendo experiencias.

Ene

2025

FILESTREAM en SQL Server

Aunque aquí somos de bases de datos relacionales y de datos estructurados, en ocasiones vamos a tener que lidiar con el manejo de datos no estructurados, como imágenes, videos y documentos. Desde el punto de vista relacional esto representa un desafío significativo en nuestras bases de datos. SQL Server aborda este problema con FILESTREAM, una característica que permite almacenar grandes objetos binarios (BLOBs) en el sistema de archivos NTFS mientras se mantienen gestionados a través de la base de datos. FILESTREAM combina la eficiencia del acceso directo al sistema de archivos con las capacidades transaccionales y de gestión de datos de SQL Server, proporcionando una solución perfecta (teóricamente) para escenarios que involucran datos no estructurados.

Arquitectura de FILESTREAM

Como os decía en la introducción, FILESTREAM integra el almacenamiento de datos binarios con la gestión relacional de SQL Server. Para conseguir esto, en lugar de guardar los datos binarios directamente en las páginas de datos de la base de datos, estos se almacenan en archivos físicos dentro de un directorio gestionado por SQL Server. Cada fila en la tabla que contiene datos FILESTREAM tiene un puntero que referencia el archivo correspondiente en el sistema de archivos. Este diseño garantiza que los datos binarios y relacionales estén sincronizados, respetando las propiedades ACID de las transacciones.

Internamente, el almacenamiento de FILESTREAM se organiza mediante filegroups especiales que se configuran para contener datos FILESTREAM. Estos filegroups actúan como un vínculo lógico entre la base de datos y el sistema de archivos, permitiendo a SQL Server gestionar la ubicación física de los datos binarios de forma transparente para el usuario.

Configuración de FILESTREAM

Habilitar FILESTREAM requiere configuraciones específicas tanto en el sistema como en SQL Server. En primer lugar, FILESTREAM debe activarse a nivel de instancia mediante SQL Server Configuration Manager. Para ello iremos a SQLServer Configuration Manager, abriremos las oropiedades de la instancia y en la pestaña FILESTREAM marcaremos el check “Activar FILESTREAM”. En esta configuración, es necesario habilitar el acceso desde Transact-SQL y, opcionalmente, el acceso de entrada/salida directa mediante la API de Win32 para escenarios que requieran un rendimiento optimizado.

Tras habilitar FILESTREAM en la instancia, se debe configurar un filegroup en la base de datos para almacenar los datos binarios. Este filegroup se asocia a un directorio del sistema de archivos que actuará como el almacenamiento físico de los archivos. Por ejemplo, mediante T-SQL se puede crear un filegroup FILESTREAM y asignarle una ruta específica:

ALTER DATABASE MiBaseDeDatos
ADD FILEGROUP FG_FileStream CONTAINS FILESTREAM;

ALTER DATABASE MiBaseDeDatos
ADD FILE (
    NAME = 'FSData',
    FILENAME = 'C:\SQLData\FileStream'
) TO FILEGROUP FG_FileStream;

Con el filegroup configurado, se pueden definir tablas con soporte FILESTREAM. Las tablas deben incluir una columna VARBINARY(MAX) declarada con el atributo FILESTREAM, lo que habilita el almacenamiento de datos binarios en el sistema de archivos. Un ejemplo de definición de tabla es el siguiente:

CREATE TABLE Documentos (Ç
    ID INT IDENTITY PRIMARY KEY,
    Nombre NVARCHAR(100),
    Archivo VARBINARY(MAX) FILESTREAM,
    FechaCreacion DATETIME DEFAULT GETDATE()
);

Acceso y Manipulación de Datos

SQL Server proporciona dos métodos principales para acceder y manipular datos FILESTREAM. El primero utiliza Transact-SQL, lo que permite realizar operaciones de inserción, actualización y recuperación de datos binarios como se haría con cualquier columna relacional. Por ejemplo, para insertar un archivo en una tabla FILESTREAM, se puede utilizar el siguiente comando:

INSERT INTO Documentos (Nombre, Archivo)
VALUES ('Archivo1', CAST('Contenido del archivo' AS VARBINARY(MAX)));

Para recuperar el archivo, se emplea una consulta estándar:

SELECT Nombre, Archivo FROM Documentos;

El segundo método de acceso emplea la API de Win32 que he mencionado antes. Esta API está diseñada para acceder directamente a los archivos almacenados en el sistema de archivos. Esta forma de trabajar es más compleja pero, particularmente útil en escenarios de alto rendimiento, ya que permite operaciones de lectura y escritura secuenciales más eficientes. Para usar este método, SQL Server proporciona la función GET_FILESTREAM_TRANSACTION_CONTEXT(), que genera un identificador de contexto transaccional necesario para acceder a los archivos.

Ventajas de FILESTREAM

Ya hemos visto la principal ventaja y es que FILESTREAM combina el rendimiento del sistema de archivos con la gestión transaccional de SQL Server. Al mover los datos binarios al sistema de archivos, reducimos la presión sobre el almacenamiento de páginas de datos y mejoramos la escalabilidad. Además, se mantiene la integridad transaccional, lo que garantiza que las operaciones de datos relacionales y binarios sean consistentes. Otro beneficio clave es la compatibilidad de FILESTREAM con las herramientas de copias de seguridad y recuperación de SQL Server, lo que simplifica la protección de datos en soluciones empresariales.

Limitaciones de FILESTREAM

A pesar de sus ventajas, FILESTREAM tiene limitaciones que deben considerarse antes de su implementación. Una de las principales restricciones es su dependencia del sistema de archivos NTFS, lo que limita su uso en otros sistemas operativos o configuraciones de almacenamiento. Es decir, olvídate de usarlo en SQL Server en linux o en docker. Además, no es compatible con todas las características avanzadas de SQL Server, como la replicación transaccional. Con Always On si que es compatible pero, requiere un cuidado especial y, en mi experiencia, es fuente de problemas de integridad de las bases de datos. Si lo activas, asegúrate de tener chequeos frecuentes de la base de datos y prepárate para reparar errores a menudo. La administración de permisos y seguridad también es más compleja, ya que los archivos están físicamente accesibles desde el sistema operativo.

Por último, la integración de FILESTREAM con las estrategias de copias de seguridad puede requerir configuraciones adicionales, ya que los datos relacionales y binarios deben mantenerse sincronizados. Esto puede aumentar la complejidad operativa, especialmente en entornos con grandes volúmenes de datos binarios.

Conclusión

FILESTREAM es una solución técnica avanzada para gestionar datos no estructurados en SQL Server. Su capacidad para combinar la eficiencia del acceso directo al sistema de archivos con la integridad transaccional lo convierte en una herramienta valiosa en escenarios donde el rendimiento y la consistencia son críticos. Sin embargo, su implementación requiere un conocimiento técnico sólido y una planificación cuidadosa para maximizar sus beneficios y evitar problemas operativos. Con una configuración adecuada y un enfoque técnico riguroso, FILESTREAM puede ser una solución escalable y robusta para aplicaciones que manejan grandes volúmenes de datos binarios.

Ene

2025

Optimización avanzada de Backups: BLOCKSIZE, MAXTRANSFERSIZE y BUFFERCOUNT

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 bytes

MAXTRANSFERSIZE

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 ) * 2

Conclusió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.

Ene

2025

Compresión en índices columnares: COLUMNSTORE_ARCHIVE

Llevamos un par de artículos ya hablando sobre compresión de datos y ya hemos visto cómo esta afecta tanto a las tablas como a los índices tanto en SQL Server como en Azure SQL. Sin embargo, hay un tipo de índice que no se ve afectado por la compresión y son los índices columnares. Lo cierto es que este tipo de índices ya tienen un nivel altísimo de compresión de datos al ser columnares pero, aún podemos comprimirlos más si es lo que queremos. Estoy hablando de una opción no tan conocida y usada que es el COLUMNSTORE_ARCHIVE.

Índices Columnares y su naturaleza comprimida

Como he comentado ya en la introducción, la propia naturaleza columnar de los índices columnstore ya de por si facilita la compresión. En concreto, por defecto y por definición se aplican técnicas de compresión avanzadas. A diferencia de los índices tradicionales basados en filas, los índices Columnstore almacenan los datos en formato columnar, lo que permite aplicar técnicas de compresión más avanzadas.

Cuando creamos un índice Columnstore, SQL Server automáticamente aplica compresión de diccionario, codificación por lotes y compresión de bits, lo que reduce significativamente el tamaño del almacenamiento y mejora la eficiencia en la lectura de datos.

La compresión de diccionario reduce el tamaño del almacenamiento eliminando valores repetitivos dentro de cada segmento de datos. Por su parte, la codificación por lotes (Run-Length Encoding) optimiza la compresión al almacenar secuencias de valores repetidos como una sola entrada. Por último, la compresión de bits (Bit-Packing) reduce el tamaño del almacenamiento al optimizar el número de bits utilizados para representar los valores almacenados.

¿Qué es Columnstore_Archive?

El índice Columnstore_Archive es una extensión del índice Columnstore comprimido estándar, diseñado para proporcionar una comprensión aún mayor aplicando algoritmos de compresión adicionales. Mientras que un índice Columnstore ya aplica técnicas avanzadas de reducción de datos como codificación de diccionario, codificación por lotes y compresión de bits, Columnstore_Archive utiliza una compresión más agresiva basada en el algoritmo Xpress Compression Algorithm (XCA).

Diferencias clave entre Columnstore y Columnstore_Archive

Característica	Índice Columnstore Normal	Columnstore Archive
Compresión aplicada	Codificación de diccionario, run-length, bit-packing	Todo lo anterior + compresión LZ77+Huffman
Impacto en almacenamiento	Reducción del 50-70%	Reducción del 70-90%
Impacto en CPU	Bajo	Alto (más procesamiento en consultas)
Velocidad de lectura	Alta	Reducida por el proceso de descompresión
Casos de uso ideales	Datos transaccionales y de consulta frecuente	Datos históricos, auditoría y repositorios de solo lectura

Cómo funciona la compresión en Columnstore_Archive

Como he comentado, el modo Columnstore_Archive añade una capa extra de compresión sobre los segmentos Columnstore existentes. Esto se logra mediante una combinación de técnicas de compresión basadas en LZ77 y Huffman, utilizadas en el algoritmo Xpress Compression Algorithm (XCA).

Fases del proceso de compresión de Columstore_Archive

Compresión LZ77: Reemplaza secuencias repetidas de bytes con referencias a posiciones anteriores en el flujo de datos. Esto reduce el tamaño al eliminar redundancias en los segmentos Columnstore.
Codificación Huffman: Utiliza un esquema de codificación basado en la frecuencia de los datos para minimizar aún más el tamaño. Los valores más comunes se almacenan con menos bits, mejorando la eficiencia de almacenamiento.

Cómo maneja SQL Server los datos comprimidos en Columstore_Archive

Cuando se escribe un índice Columnstore_Archive, SQL Server aplica la compresión LZ77 + Huffman a los segmentos Columnstore ya existentes. Al leer datos de un índice Columnstore Archive, SQL Server debe descomprimir estos segmentos antes de ejecutar la consulta, lo que implica un uso de CPU significativamente mayor.

Implementar Columstore_Archive

Si queremos habilitar Columnstore_Archive en una tabla o índice usaremos el comando ALTER TABLE o ALTER INDEX de la siguiente manera:

1. Habilitar Columnstore_Archive en una partición

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

2. Habilitar Columnstore_Archive en todas las particiones

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

3. Habilitar Columnstore en todas las particiones y Columnstore_Archive en alguna

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (4, 5));

3b. Otra forma de habilitar Columnstore en todas las particiones y Columnstore_Archive en alguna:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (1, 2, 3));

Impacto en el rendimiento de Columnstore_Archive

Columnstore_Archive permite una reducción extrema del tamaño de almacenamiento, lo que lo hace ideal para entornos donde el espacio en disco o las copias de seguridad representan un coste significativo. Al disminuir el tamaño de los datos almacenados, se reducen los costes operativos y se optimiza el uso del almacenamiento, especialmente en bases de datos alojadas en la nube.

Sin embargo, esta ventaja viene acompañada de un mayor consumo de CPU en las consultas, ya que los datos deben ser descomprimidos en tiempo de ejecución. En escenarios donde las consultas analíticas son frecuentes y de gran volumen, este aumento en el uso de CPU puede impactar el rendimiento general del sistema, por lo que es fundamental evaluar su aplicación caso por caso.

Casos de uso ideales para Columnstore_Archive

El uso de Columnstore_Archive está especialmente indicado en escenarios donde los datos almacenados son mayormente de solo lectura o tienen un acceso esporádico. Tablas con registros históricos, auditorías o grandes volúmenes de datos que rara vez se consultan pueden beneficiarse enormemente de la reducción de almacenamiento sin que el impacto en la CPU sea un problema. En entornos de Data Warehouse donde la retención de datos es fundamental, Columnstore_Archive puede ser clave para reducir los costes de almacenamiento sin comprometer la integridad de los datos.

También es una opción interesante en Azure SQL Managed Instance y otras bases de datos en la nube, donde los costes de almacenamiento suelen ser elevados. Reducir el tamaño de la base de datos mediante Columnstore_Archive puede generar ahorros significativos, especialmente en cargas de trabajo que dependen de replicaciones geográficas y copias de seguridad, donde el tamaño de los datos afecta directamente los costes de operación.

Buenas prácticas con Columnstore_Archive

Para aprovechar al máximo Columnstore_Archive, es fundamental evaluar cuidadosamente qué tablas o índices pueden beneficiarse de esta compresión. No es recomendable aplicarlo en datos de acceso frecuente, ya que el proceso de descompresión puede generar una sobrecarga en la CPU que afecte el rendimiento de las consultas. Monitorizar el impacto en el rendimiento con herramientas como Query Store y ejecutar pruebas antes de aplicar la compresión en entornos de producción son pasos esenciales para garantizar que los beneficios en almacenamiento no se vean opacados por problemas de latencia.

Conclusión

Columnstore_Archive es una solución avanzada para la compresión extrema de datos en SQL Server, útil en escenarios donde el almacenamiento es la principal preocupación. Sin embargo, su mayor consumo de CPU puede ser un factor limitante en bases de datos con consultas frecuentes. Si el objetivo es maximizar la eficiencia del almacenamiento sin comprometer demasiado el rendimiento, Columnstore Archive es una opción poderosa que debe aplicarse estratégicamente en los casos adecuados. Una planificación cuidadosa y una evaluación continua del impacto en rendimiento permitirán sacar el máximo provecho de esta tecnología sin afectar la operativa de la base de datos.

¿Qué es el Nivel de Compatibilidad en SQL Server?

¿Por qué es importante el Nivel de Compatibilidad?

El cambio clave con el Nivel de Compatibilidad 120 (SQL Server 2014)

¿Qué es el estimador de cardinalidad y por qué es importante?

Impacto del Nivel de Compatibilidad 120 en los planes de mantenimiento

Cambios en la fragmentación de índices

Nuevo comportamiento en las esperas de consultas y la concesión de memoria

¿Cómo adaptar los planes de mantenimiento?

Consideraciones antes de cambiar el Nivel de Compatibilidad

Cómo cambiar el Nivel de Compatibilidad

Conclusión

Estructuras de asignación de espacio en SQL Server

Páginas PFS (Page Free Space)

Características de las páginas PFS

Ubicación de las páginas PFS en el archivo de datos

Consulta para inspeccionar páginas PFS

Páginas GAM (Global Allocation Map)

Características de las páginas GAM

Ubicación de las páginas GAM en el archivo de datos

Consulta para inspeccionar GAM

Páginas SGAM (Shared Global Allocation Map)

Características de SGAM

Ubicación de las SGAM en el archivo de datos

Consulta para inspeccionar SGAM

Páginas IAM (Index Allocation Map)

Consulta para inspeccionar IAM

Conclusión

Páginas, la unidad atómica de almacenamiento en SQL Server

Tipos de Páginas en función su almacenamiento

Extensiones, como realmente SQL Server gestiona el almacenamiento

Gestión del almacenamiento

Páginas GAM: mapa global de asignación de almacenamiento

Páginas SGAM: mapa global de almacenamiento compartido

Páginas PFS: almacenamiento disponible

Páginas IAM: Mapa de asignación de índices

Consideraciones Avanzadas

Conclusión

¿Por qué optimizar los backups?

Separar los backups en varios ficheros

Demostración práctica BACKUPS

Demostración práctica RESTORE

Bonus extra: Verificar los backups

Conclusión

Arquitectura de FILESTREAM

Configuración de FILESTREAM

Acceso y Manipulación de Datos

Ventajas de FILESTREAM

Limitaciones de FILESTREAM

Conclusión

Introducción al funcionamiento de las opciones de backups

BLOCKSIZE

MAXTRANSFERSIZE

BUFFERCOUNT

Cómo optimizar tu backups

Conclusión

Índices Columnares y su naturaleza comprimida

¿Qué es Columnstore_Archive?

Diferencias clave entre Columnstore y Columnstore_Archive

Cómo funciona la compresión en Columnstore_Archive

Fases del proceso de compresión de Columstore_Archive

Cómo maneja SQL Server los datos comprimidos en Columstore_Archive

Implementar Columstore_Archive

Impacto en el rendimiento de Columnstore_Archive

Casos de uso ideales para Columnstore_Archive

Buenas prácticas con Columnstore_Archive

Conclusión

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes