SQL Server

Aquí encontraras todos nuestros post relacionados con SQL Server desde cero hasta un nivel avanzado. Desde infraestructura hasta modelado de datos.

09
Oct
2024

Buffer Pool Extension

En nuestras instalaciones de SQL Server, la gestión eficiente de los recursos es uno de los factores clave para obtener un rendimiento óptimo. Sobre este tema, sabemos que el subsistema de memoria juega un papel crucial al permitir que SQL Server procese consultas y acceda a los datos de la forma más rápida posible. En concreto el Buffer Pool, que almacena páginas de datos en memoria para evitar accesos frecuentes al disco, es una de las estructuras más importantes en este aspecto. Sin embargo, cuando la cantidad de datos que estamos manejando supera la capacidad de la memoria física disponible, es cuando entra en juego una funcionalidad que muchos conocen, pero no siempre aprovechan al máximo: el Buffer Pool Extension (BPE).

¿Qué es el Buffer Pool?

Aunque ya hablamos de esto en profundidad en el artículo sobre el uso de la RAM, el Buffer Pool en SQL Server es donde se cargan las páginas de datos desde disco para ser procesadas por el motor de base de datos. Si la base de datos que estamos gestionando es lo suficientemente pequeña como para caber completamente en memoria, el rendimiento será óptimo porque las operaciones de lectura y escritura ocurren en la memoria, que es significativamente más rápida que el almacenamiento en disco. Sin embargo, cuando el tamaño de la base de datos excede la memoria disponible, SQL Server comienza a intercambiar páginas entre la memoria y el disco, lo que genera un impacto en el rendimiento.

¿Qué es exactamente el Buffer Pool Extension?

Aquí es donde el Buffer Pool Extension entra en juego. SQL Server puede utilizar una unidad SSD configurada como extensión de la memoria. Este mecanismo permite que las páginas de datos menos utilizadas se almacenen temporalmente en la SSD en lugar de ser expulsadas completamente del Buffer Pool, lo que reduce significativamente la latencia en comparación con el almacenamiento tradicional en disco mecánico.

Pero, empecemos por el principio, el Buffer Pool Extension es una característica introducida en la versión 2014 en las ediciones Standard y Enterprise de SQL Server que nos permite extender la memoria física utilizando almacenamiento en disco de estado sólido (SSD). Aunque a primera vista podría parecer que añadir más memoria física sería una mejor solución, el Buffer Pool Extension ofrece una alternativa interesante, especialmente cuando los costes de expansión de memoria RAM son prohibitivos. A lo largo de este artículo exploraremos cómo funciona esta extensión, cuándo es útil y cómo configurarla para sacar el máximo partido a nuestros sistemas SQL Server.

Aunque no sustituye a la memoria física, el Buffer Pool Extension es una solución que puede proporcionar una mejora sustancial en rendimiento cuando las bases de datos no pueden ser completamente almacenadas en la memoria RAM. Es importante tener en cuenta que las SSD utilizadas para el Buffer Pool Extension deben ser de alta calidad, ya que su durabilidad y velocidad de acceso son factores críticos para el éxito de esta estrategia.

Ventajas y desventajas del uso de Buffer Pool Extension

La principal ventaja del Buffer Pool Extension es que permite mejorar el rendimiento de SQL Server en escenarios donde el acceso a la memoria es un cuello de botella. Al utilizar almacenamiento en SSD para extender el Buffer Pool, SQL Server puede mantener más datos «cerca» del procesador, reduciendo la necesidad de acceder a discos más lentos.

Sin embargo, esta funcionalidad también presenta algunas limitaciones. El Buffer Pool Extension no es una solución mágica que elimine la necesidad de tener suficiente memoria RAM. Sigue siendo fundamental que tengamos una cantidad adecuada de memoria física disponible, ya que el Buffer Pool Extension solo es efectivo para gestionar picos de carga o situaciones donde la demanda de memoria excede temporalmente la capacidad instalada. Además, las unidades SSD, aunque rápidas, no tienen la misma velocidad que la RAM, por lo que su uso implica una pequeña penalización en el rendimiento en comparación con la memoria física.

Otro punto muy importante a considerar es el desgaste de las unidades SSD. Este tipo de almacenamiento, aunque eficiente, tiene un número limitado de ciclos de escritura, lo que puede provocar su deterioro con el tiempo, especialmente en sistemas que generan una alta cantidad de escrituras en el Buffer Pool. Es por ello que, si bien los SSD pueden mejorar el rendimiento, debemos monitorizar cuidadosamente su uso para evitar sorpresas desagradables en cuanto a su durabilidad.

Casos de uso para el Buffer Pool Extension

El Buffer Pool Extension es particularmente útil en escenarios donde la base de datos es mucho más grande que la memoria disponible y además no podemos incrementar la RAM fácilmente debido a restricciones presupuestarias o técnicas. Un ejemplo típico es cuando gestionamos bases de datos que contienen grandes volúmenes de datos históricos que no se consultan con frecuencia. En este tipo de situaciones, las páginas menos accedidas pueden ser almacenadas en el Buffer Pool Extension, dejando el espacio en memoria para los datos que son más críticos para las consultas activas.

Otro escenario en el que el Buffer Pool Extension puede resultarnos muy útil es cuando trabajamos con aplicaciones con picos de carga impredecibles. En lugar de dimensionar un sistema con más RAM de la que se necesitaría el 90% del tiempo, podemos dimensionarlo adecuadamente y permitir que el Buffer Pool Extension absorba los picos temporales de demanda de memoria.

No obstante, el Buffer Pool Extension no es adecuado para todas las situaciones. Si nuestra base de datos está en constante cambio y todo el conjunto de datos es consultado de manera uniforme, es probable que el Buffer Pool Extension no aporte beneficios significativos. En estos casos el intercambio entre memoria y SSD será constante, y no veremos mejoras notables en el rendimiento. Por no hablar además delriesgo de “romper” el disco SSD antes de tiempo.

Configuración y buenas prácticas

Configurar el Buffer Pool Extension en SQL Server es un proceso relativamente sencillo, pero debemos seguir algunas buenas prácticas para asegurarnos de que funcione correctamente. La primera recomendación es seleccionar un dispositivo SSD de alta calidad, con una baja latencia y alta durabilidad. Es preferible utilizar SSD dedicadas exclusivamente para el Buffer Pool Extension en lugar de compartir el mismo almacenamiento con otras cargas de trabajo. Compartir disco podría impactar en el rendimiento de ambas funciones y, en caso de degradación del disco, afectar a otros datos.

Una vez seleccionada la unidad adecuada, la configuración del Buffer Pool Extension se realiza a través de T-SQL, utilizando el comando ALTER SERVER CONFIGURATION. Es importante asegurarse de que el tamaño asignado al Buffer Pool Extension sea suficiente para cubrir las necesidades de las cargas de trabajo más intensivas, pero sin exceder el tamaño total del almacenamiento SSD disponible. La clave es encontrar un equilibrio que permita optimizar el uso de la unidad SSD sin sobrecargarla ni saturarla de datos que, en realidad, deberían estar en la memoria RAM.

Es recomendable monitorizar el uso del Buffer Pool Extension mediante las vistas de gestión dinámica (DMVs), como sys.dm_os_buffer_descriptors, para comprobar que las páginas almacenadas en la extensión realmente están siendo aprovechadas. Además, debemos estar atentos a los posibles problemas de rendimiento en el SSD y estar preparados para ajustar la configuración en caso de que notemos que la extensión no está ofreciendo las mejoras esperadas.

Conclusión

El Buffer Pool Extension de SQL Server es una herramienta valiosa para mejorar el rendimiento en sistemas con restricciones de memoria física, especialmente cuando se dispone de almacenamiento en SSD de alta calidad. Aunque no sustituye a la RAM, ofrece una solución intermedia eficaz en situaciones donde aumentar la memoria física no es viable. Sin embargo, debemos ser cuidadosos al implementarlo, asegurándonos de que nuestras aplicaciones realmente se beneficien de su uso y evitando su abuso en sistemas donde podría generar más desgaste que beneficios.

En definitiva, la clave para sacar el máximo partido del Buffer Pool Extension es entender las características de nuestras cargas de trabajo y aplicar esta funcionalidad solo cuando sea necesario. Con una correcta planificación y configuración, el Buffer Pool Extension puede marcar una gran diferencia en el rendimiento de nuestros sistemas 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!

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 0 comentarios
07
Oct
2024

¿Cómo usa SQL la RAM?

La memoria RAM es uno de los recursos más críticos para cualquier sistema informático, ya que impacta directamente en el rendimiento. En SQL Server también es un tema clave ya que afecta a cómo de rápido se van a completar las operaciones de consulta y escritura. Aunque a menudo nos enfocamos en cómo SQL Server utiliza la RAM para cargar y procesar las páginas de datos, la realidad es que hay muchos otros elementos que también consumen memoria. En este artículo, profundizaremos en cómo SQL Server gestiona la memoria RAM.

Uso general de la memoria RAM en SQL Server

SQL Server utiliza la memoria RAM para una amplia variedad de funciones operativas, no sólo para las páginas de datos. La RAM se reparte entre tareas que van desde la gestión de conexiones y sesiones hasta el procesamiento de consultas y la ejecución de procedimientos almacenados. Cada vez que un usuario se conecta a SQL Server, se asigna una porción de memoria para gestionar la sesión y almacenar detalles sobre los planes de ejecución de las consultas.

Además, las operaciones de consulta más complejas, como las ordenaciones, agrupaciones y uniones, requieren memoria temporal para gestionar los datos durante el proceso. Esto significa que, incluso en escenarios donde el acceso a los datos no es intensivo, SQL Server utiliza la RAM para agilizar las operaciones que no necesariamente dependen de las páginas de datos.

Otro gran consumidor de memoria es el caché de planes de ejecución, también conocido como Plan Cache. Este área almacena los planes de ejecución de consultas reutilizables, lo que mejora el rendimiento al evitar la recompilación constante de planes consultas similares.

Si bien estas áreas son importantes, el mayor consumidor de memoria dentro de SQL Server es, sin duda, el Buffer Pool.

El Buffer Pool de la RAM

El Buffer Pool es el área de la memoria en la que SQL Server carga las páginas de datos desde los discos. Estas páginas tienen un tamaño estándar de 8 KB y contienen los datos que las consultas necesitan para ejecutarse. Siempre que una consulta requiere acceder a una tabla, SQL Server primero intenta cargar la página correspondiente en el Buffer Pool. Si la página ya está en memoria, el motor puede procesarla rápidamente. Si no está, se debe leer del disco, lo que es significativamente más lento.

En el mejor de los casos, el Buffer Pool es lo suficientemente grande como para almacenar todas las páginas de datos activas. Sin embargo, esto es un ideal y lo normal es encontrarnos con bases de datos que exceden la capacidad de la memoria física disponible. En estos casos, SQL Server debe gestionar el intercambio de páginas entre el Buffer Pool y el almacenamiento en disco. El motor utiliza un algoritmo de reemplazo de páginas conocido como LRU (Least Recently Used), que expulsa las páginas menos utilizadas de la memoria para hacer espacio a nuevas páginas que necesitan ser cargadas.

Este proceso es crítico para el rendimiento general del sistema, ya que el acceso a datos almacenados en disco es mucho más lento que el acceso a datos en memoria. Cuantas más páginas de datos puedan permanecer en el Buffer Pool, más rápido será el rendimiento del servidor. Ten en cuenta que el motor de SQL Server jamás va a leer datos del disco directamente, siempre carga en memoria las páginas de datos necesarias y luego accede a los datos contenidos en ellas. Para los procesos de escritura, en resumidas cuentas, es lo mismo: se carga la página en memoria, se modifica lo que sea necesario y después se vuelca al disco.

Otras áreas de uso de la memoria RAM en SQL Server

Además del Buffer Pool, SQL Server utiliza la RAM para una variedad de funciones adicionales, todas ellas orientadas a mejorar el rendimiento del sistema:

Como ya hemos visto antes, la caché de planes de consulta es una de las áreas más importantes fuera del Buffer Pool. Aquí se almacenan los planes de ejecución de las consultas para evitar tener que recompilar consultas idénticas. La caché de planes mejora significativamente el rendimiento de consultas recurrentes al permitir que el servidor reutilice los planes de ejecución almacenados.

También hemos visto que otro de los procesos en los que SQL Server emplea la memoria RAM es en las operaciones internas como ordenaciones y uniones requieren memoria adicional para gestionar los datos intermedios antes de que los resultados finales puedan ser escritos o enviados al cliente. SQL Server asigna memoria temporal para estas operaciones, que luego es liberada una vez que se completan.Además, es probable que el uso de RAM sea mayor al volumen de los datos por lo que hay que tener cuidado con este tipo de operaciones.

Otra funcionalidad que consume gran cantidad de memoria son las operaciones In-Memory OLTP. Esta característica permite que ciertas tablas se mantengan completamente en memoria, lo que acelera enormemente las transacciones de alta concurrencia. Sin embargo, este enfoque consume una cantidad significativa de RAM.

En resumen, SQL Server no solo utiliza la memoria para las páginas de datos, sino que gestiona dinámicamente la memoria entre varias áreas clave para maximizar el rendimiento del sistema. El reto que tenemos los DBAs es equilibrar estas áreas de uso para garantizar que el Buffer Pool tenga suficiente espacio para almacenar las páginas de datos más utilizadas sin comprometer otras operaciones importantes del sistema. Sin embargo, el reto es que no podemos actuar sobre este reparto, al menos directamente. 

Mecanismos de ajuste dinámico de memoria RAM

Como acabamos de ver, no podemos actuar sobre como SQL Server gestiona la memoria. El motor de base de datos cuenta con mecanismos internos para gestionar y ajustar el uso de la memoria de manera dinámica. Esto es crucial en entornos donde la demanda de memoria puede fluctuar con frecuencia debido a cambios en las cargas de trabajo o la actividad de usuarios externos. Uno de los mecanismos más importantes es el ajuste dinámico de memoria, que permite a SQL Server ajustar automáticamente la cantidad de RAM que utiliza dentro de los límites establecidos en la configuración del servidor.

Cuando otros procesos en el sistema operativo necesitan memoria, SQL Server puede liberar parte de su memoria para evitar sobrecargar el sistema. A este proceso se le conoce como «presión de memoria externa». Aunque SQL Server es bastante eficiente en este ajuste, es importante para nosotros monitorizar el uso de la memoria para asegurarnos de que el servidor no esté liberando demasiada memoria en detrimento del rendimiento. Esto podemos asegurarlo configurando un mínimo de RAM para la instancia.

En situaciones donde la memoria es insuficiente, SQL Server también puede aplicar «esperas de memoria», lo que significa que las consultas se ponen en cola hasta que haya suficiente memoria disponible para su ejecución. Este escenario puede generar cuellos de botella en el rendimiento si no se gestiona adecuadamente, por lo que es fundamental tener configurados límites de memoria adecuados y suficientes recursos físicos disponibles.

Optimización de la gestión de memoria en SQL Server

Para optimizar el uso de la memoria RAM en SQL Server, ya que no podemos actuar sobre la gestión interna de la memoria, es vital que comprendamos cómo maneja nuestro sistema las cargas de trabajo. Si nuestras bases de datos están activamente consultadas y el tamaño de los datos excede la capacidad de la memoria física, priorizar el uso del Buffer Pool es fundamental. Aquí es donde técnicas como la extensión de memoria mediante Buffer Pool Extension pueden ser útiles, pero siempre como complemento a una buena planificación de memoria RAM física.

Un punto importante es mantener un equilibrio adecuado entre las diferentes áreas que consumen memoria en SQL Server. No podemos permitir que áreas como el Plan Cache o la memoria para operaciones internas consuman una cantidad excesiva de memoria a expensas del Buffer Pool. Si bien SQL Server ajusta la memoria de forma dinámica, un buen DBA debe estar atento a las señales de advertencia, como esperas prolongadas de memoria o un uso excesivo de intercambio de páginas.

Conclusión

La gestión eficiente de la memoria en SQL Server es clave para garantizar un rendimiento óptimo, especialmente en sistemas con grandes volúmenes de datos. Comprender cómo SQL Server utiliza la RAM y ajustar la configuración en función de las necesidades de cada implementación es el primer paso para asegurar un equilibrio entre el uso de la memoria y la eficiencia del sistema.

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!

 

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 1 comentario
04
Oct
2024

Deadlock Priority

Uno de los problemas más temidos por usuarios y administradores de bases de datos son los deadlocks. Cuando varias transacciones acceden a los mismos recursos al mismo tiempo, el riesgo de bloqueos, y en particular de deadlocks, aumenta considerablemente. En estos casos, la prioridad de deadlock, o Deadlock Priority, se convierte en una herramienta esencial para evitar que procesos críticos se vean interrumpidos cuando SQL Server elige qué transacción finalizar. Antes de profundizar en este concepto, es importante entender qué es un deadlock y cómo afecta al rendimiento en SQL Server.

Qué es un deadlock en SQL Server

Deadlock PriorityUn deadlock ocurre cuando dos o más transacciones quedan atrapadas en un ciclo de espera mutua porque cada una está bloqueando recursos que la otra necesita. Ninguna puede continuar hasta que la otra libere el recurso, lo que genera un bloqueo indefinido. En estos casos, SQL Server interviene para romper el ciclo seleccionando una transacción como «víctima». SQL entonces finalizará la transacción victima para liberar los recursos y permitir que la otra continúe.

Para entender este fenómeno de manera más clara, es útil recurrir a un ejemplo clásico de la teoría de la concurrencia: la paradoja de la cena de los filósofos.

La paradoja de la cena de los filósofos y la concurrencia

La paradoja de la cena de los filósofos es un experimento mental creado para ilustrar los problemas que surgen cuando varios procesos (o personas, en este caso) intentan acceder simultáneamente a recursos compartidos de manera descoordinada. Imaginemos a cinco filósofos sentados alrededor de una mesa redonda. En la mesa, hay un plato de comida frente a cada uno, pero solo hay cinco tenedores disponibles, uno entre cada dos filósofos. Para comer, cada filósofo necesita dos tenedores: uno en la mano izquierda y otro en la mano derecha. Lo sé no tiene sentido necesitar dos tenedores pero es que esta es una adaptación de una teoría original de china donde hablan de palillo, para el caso da igual. 

Ahora, supongamos que cada filósofo sigue la misma estrategia: primero toma el tenedor de su izquierda y luego el de su derecha. El problema surge si todos los filósofos deciden tomar el tenedor de su izquierda al mismo tiempo. Cada uno tomará un tenedor, pero quedarán esperando indefinidamente a que el filósofo de su derecha libere el otro tenedor. Nadie podrá comer y todos estarán bloqueados.

Este es un ejemplo claro de deadlock. Cada filósofo (o transacción, en el contexto de SQL Server) tiene un recurso y está esperando por otro que está siendo utilizado por otro filósofo. Esto es básicamente, un ciclo de espera sin fin.

¿Cómo esta paradoja refleja los problemas de deadlock en SQL Server?

En SQL Server, la situación es muy similar. Las transacciones actúan como los filósofos de la historia, y los recursos, como los tenedores, son elementos que las transacciones necesitan para completarse. Cuando dos transacciones intentan acceder a los mismos recursos de manera descoordinada, pueden quedar bloqueadas de forma indefinida. En estos casos, el servidor debe intervenir para resolver el conflicto.

La solución en la paradoja de los filósofos sería introducir un mecanismo de coordinación, asegurando que no todos los filósofos intenten tomar el mismo tenedor al mismo tiempo. De manera similar, en SQL Server, es fundamental implementar estrategias que eviten los deadlocks, como el uso de la configuración de Deadlock Priority.

Configurando Deadlock Priority para gestionar los conflictos

SQL Server nos permite influir en la decisión de qué transacción finalizar cuando ocurre un deadlock mediante la instrucción SET DEADLOCK_PRIORITY. De este modo, asignaremos una prioridad a cada sesión gracias a esta configuración, con lo que, podremos influir sobre su probabilidad de ser finalizada en caso de un deadlock. Las prioridades van desde -10 (más susceptible a ser finalizada) hasta 10 (menos susceptible) o, también, con valores predefinidos como LOW, NORMAL y HIGH.

Por ejemplo, si estamos ejecutando una transacción crítica que no debe interrumpirse, podemos asignarle una prioridad alta:

SET DEADLOCK_PRIORITY HIGH;

De este modo, le estamos indicando a SQL Server que esta transacción debe ser protegida en caso de conflicto. Por otro lado, si tenemos una transacción menos importante, podemos asignarle una prioridad baja:

SET DEADLOCK_PRIORITY LOW;

Esto asegura que, si se produce un deadlock, SQL Server finalizará primero la transacción con prioridad baja.

SQL Server y la toma de decisiones automáticas ante deadlocks

Aunque la prioridad de deadlock nos ofrece mayor control sobre qué transacciones sobrevivirán en caso de conflicto, no garantiza que una transacción nunca será finalizada. SQL Server sigue considerando otros factores, como el costo de finalizar una transacción, antes de decidir cuál terminar. 

A lo largo de mi experiencia como DBA he escuchado teorías de lo más locas sobre esta elección de víctima en caso de deadlock. Desde que las transacciones ejecutadas por job tienen una prioridad más baja hasta que existe un sistema interno de reputación de usuarios. Nada de esto es cierto, por lo menos atendiendo a la documentación oficial. Cualquier transacción que no tenga especificada una prioridad distinta es tratada como de prioridad normal. En caso de colisión, si ambas transacciones tienen la misma prioridad, el servidor seleccionará aquella que sea más barata de finalizar. Es decir, en la que haya realizado menos cambios y por tanto tenga menos que revertir o que tenga menor impacto en el sistema. 

Por esta razón, la prioridad de deadlock debe usarse como parte de una estrategia más amplia para gestionar la concurrencia y los bloqueos en SQL Server.

Optimización del rendimiento mediante el control de deadlocks

Aunque la configuración de Deadlock Priority es una herramienta útil, no debe ser nuestra única estrategia para evitar deadlocks. Una parte crucial de la prevención de bloqueos mutuos es optimizar las consultas para reducir el tiempo durante el cual mantienen bloqueados los recursos. Las transacciones que se completan rápidamente tienen menos probabilidades de verse envueltas en deadlocks, ya que liberan los recursos antes de que otras transacciones los necesiten.

Además, es importante diseñar los flujos de acceso a los recursos de manera coherente. Una técnica efectiva es garantizar que todas las transacciones sigan un orden predefinido al acceder a los recursos. Esto reduce significativamente la probabilidad de un ciclo de espera, similar a cómo los filósofos podrían ponerse de acuerdo en quién toma primero los cubiertos.

Monitorización y detección de deadlocks en SQL Server

Es fundamental que monitoricemos activamente los deadlocks en SQL Server para detectar patrones repetidos de bloqueo y ajustar nuestras configuraciones de prioridad de manera adecuada. Las herramientas de SQL Server, como los eventos extendidos (Extended Events) y las vistas de administración dinámica (DMVs), nos permiten identificar qué transacciones están causando los bloqueos y analizar sus causas.

Si notamos que una transacción crítica está siendo seleccionada frecuentemente como víctima de deadlocks, es posible que debamos ajustar su prioridad o revisar cómo accede a los recursos. Una monitorización continua nos ayuda a mantener un sistema fluido y a identificar posibles problemas antes de que afecten de manera significativa al rendimiento.

Conclusión

La configuración de Deadlock Priority en SQL Server es una herramienta eficaz para gestionar la concurrencia y evitar que procesos críticos sean interrumpidos en caso de deadlocks. Sin embargo, no debemos confiar únicamente en esta configuración. Los deadlocks suelen ser un síntoma de una planificación ineficiente de las transacciones o de un acceso mal gestionado a los recursos. Para minimizar su ocurrencia, es necesario combinar la configuración de prioridad con una estrategia de optimización de consultas y un monitoreo constante.

De este modo, no solo reducimos la probabilidad de deadlocks, sino que también garantizamos que las transacciones críticas se ejecuten sin interrupciones, asegurando un rendimiento óptimo en nuestro sistema 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!

 

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios
02
Oct
2024

Particionado en ReFS vs NTFS para SQL Server

Hace unos días surgió en nuestra comunidad de telegram un debate interesante sobre cual es el mejor particionamiento para SQL Server si NTFS o ReFS. Lo cierto es que al gestionar bases de datos SQL Server, la elección del sistema de archivos adecuado puede tener un impacto significativo en el rendimiento y la fiabilidad. Tradicionalmente, NTFS ha sido la opción por defecto en sistemas Windows, pero la llegada de ReFS (allá por 2012) ha planteado una alternativa interesante. En este artículo, voy a tratar de analizar las diferencias entre ReFS y NTFS para SQL Server, evaluando sus implicaciones en términos de rendimiento, integridad de datos y consumo de recursos.

NTFS: Un sistema probado y compatible

NTFS es el sistema de archivos más utilizado en Windows Server desde hace décadas. Ofrece estabilidad y un amplio soporte para características como la compresión de archivos, encriptación y la gestión eficiente de grandes volúmenes de datos. En el contexto de SQL Server, NTFS ha demostrado ser un sistema fiable y compatible, especialmente en entornos de producción donde se utilizan herramientas y utilidades estándar para la administración de discos y almacenamiento.

A nivel de rendimiento, NTFS puede enfrentar problemas cuando se manejan grandes volúmenes de datos o cuando las operaciones de entrada/salida (E/S) son intensivas. Su estructura jerárquica y los metadatos asociados pueden llegar a ser un cuello de botella en consultas complejas o bases de datos con altas transacciones. Además, NTFS es susceptible a la fragmentación de archivos, un problema que afecta al rendimiento a medida que las bases de datos crecen y se modifican. La desfragmentación, aunque útil, requiere tiempo y recursos adicionales.

ReFS: Ventajas y limitaciones para SQL Server

ReFS (Resilient File System) fue introducido por Microsoft como una alternativa más robusta a NTFS, con la promesa de mejorar la resiliencia ante la corrupción de datos y ofrecer un mejor rendimiento en operaciones de E/S intensivas. En teoría, ReFS presenta varias ventajas sobre NTFS, como la reducción de la fragmentación y una mayor tolerancia a errores, lo que lo convierte en una opción atractiva para gestionar volúmenes de datos grandes y sistemas de almacenamiento avanzado, como Storage Spaces.

Uno de los puntos fuertes de ReFS es su capacidad para gestionar grandes volúmenes de datos y evitar la fragmentación, algo que puede beneficiar significativamente a las bases de datos SQL Server que requieren muchas operaciones de lectura y escritura. Sin embargo, cuando hablamos de bases de datos, no es recomendable aprovechar la característica de integridad de datos automática de ReFS, ya que Microsoft sugiere desactivarla en estos entornos. El motivo es que SQL Server ya gestiona su propia integridad de datos mediante transacciones ACID y checksums, y permitir que ReFS realice correcciones automáticas podría interferir con estos mecanismos. Por tanto, la función de integridad de ReFS, que es una de sus principales fortalezas, no debe utilizarse en bases de datos SQL Server, limitando su aplicación en estos escenarios.

Consumo de recursos en ReFS

Otra consideración importante al usar ReFS es su mayor consumo de recursos en comparación con NTFS. Debido a las funcionalidades avanzadas de corrección de errores y gestión de volúmenes, ReFS tiende a utilizar más CPU y memoria. En entornos de bases de datos con cargas de trabajo intensivas, como es común en SQL Server, este mayor consumo puede reducir el rendimiento general del sistema, especialmente en servidores que ya están bajo una alta carga de procesamiento.

Si bien ReFS ofrece una mejor gestión de volúmenes grandes, debemos sopesar si el mayor uso de recursos del sistema compensa las mejoras en la resistencia y la fragmentación. En muchos casos, NTFS sigue siendo más eficiente, especialmente en servidores donde el hardware no es de última generación o donde el coste de la memoria y CPU es una preocupación.

¿Afecta a SQL Server la deduplicación de datos de ReFS?

La deduplicación de datos es una técnica de almacenamiento que consiste en identificar y eliminar bloques de datos duplicados para reducir el espacio necesario. En lugar de almacenar múltiples copias de la misma información, la deduplicación guarda una única versión de los bloques repetidos y crea referencias a estos desde otros archivos o ubicaciones.

En cuanto a la deduplicación de datos para SQL Server, es importante aclarar que SQL Server no se beneficia directamente de esta funcionalidad. Esto es porque las bases de datos de SQL almacenan información en bloques (páginas) con identificadores únicos, lo que implica que no hay bloques redundantes que puedan ser eliminados sin afectar la integridad de los datos. Por tanto, la deduplicación no se aplica a los archivos de bases de datos.

Sin embargo, es posible que en algunos escenarios se intente utilizar la deduplicación de ReFS para las copias de seguridad de bases de datos. En este contexto, la deduplicación podría ser útil para reducir el espacio de almacenamiento utilizado por las copias de seguridad, pero también introduce riesgos. Un entorno con deduplicación puede generar un único punto de fallo (SPoF), ya que si el mecanismo de deduplicación falla o se corrompe, podríamos perder acceso a múltiples copias de seguridad que dependen de los mismos bloques deduplicados. Por esta razón, no se recomienda utilizar deduplicación en entornos de SQL Server para los archivos de base de datos, y su uso en archivos de copia de seguridad debe evaluarse con cautela.

Comparativa final: NTFS vs ReFS

NTFS sigue siendo una opción sólida para la mayoría de entornos SQL Server, especialmente en servidores que manejan bases de datos de tamaño medio o pequeño. Su menor consumo de recursos, la compatibilidad con un amplio abanico de herramientas de terceros y su fiabilidad hacen que sea preferido en muchos casos. Aunque la fragmentación y la necesidad de mantenimiento son factores a tener en cuenta, NTFS ofrece un equilibrio robusto entre rendimiento y estabilidad, sin requerir un hardware excepcionalmente avanzado.

ReFS, por otro lado, tiene claras ventajas cuando gestionamos grandes volúmenes de datos en sistemas avanzados de almacenamiento, como en configuraciones con Storage Spaces. Su diseño reduce la fragmentación y permite una gestión más eficiente de los datos, lo que es útil en entornos con grandes archivos o volúmenes masivos de información. No obstante, la necesidad de desactivar su función de integridad de datos en bases de datos SQL Server y su mayor consumo de recursos limitan su aplicabilidad en escenarios de bases de datos tradicionales.

Para bases de datos críticas o transaccionales, donde la carga de trabajo y los recursos del servidor ya están optimizados para SQL Server, NTFS sigue siendo la opción preferida. Si bien ReFS puede mejorar el rendimiento en algunos aspectos, su configuración más compleja y mayor uso de CPU y memoria lo convierten en una opción menos atractiva a menos que se trate de un entorno con necesidades específicas de almacenamiento masivo.

Conclusión

La elección entre NTFS y ReFS para SQL Server no tiene una respuesta única, ya que depende de las necesidades de cada entorno. NTFS sigue siendo la opción más estable y eficiente en términos de recursos, y es especialmente recomendable cuando se prioriza la compatibilidad y la menor sobrecarga en el servidor. ReFS, aunque potente en la gestión de grandes volúmenes de datos, requiere una configuración más cuidadosa, especialmente al desactivar sus funciones de integridad de datos, lo que puede reducir algunas de sus ventajas teóricas.

El consumo de recursos de ReFS y su necesidad de ajustes adicionales hacen que, en la mayoría de los escenarios, NTFS siga siendo una opción más práctica para bases de datos de producción en SQL Server. La clave está en evaluar los requisitos de almacenamiento y recursos de cada proyecto y elegir el sistema de archivos que mejor se adapte a nuestras necesidades.

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!

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 0 comentarios
30
Sep
2024

DBCC LOG

Hoy os quiero hablar de uno de esos “secretos a voces” que tiene SQL Server. En SQL Server, existen herramientas y comandos que, aunque nadie te va a decir oficialmente que están disponibles, son bien conocidos por los DBAs y pueden ser extremadamente útiles. Uno de estos comandos es DBCC LOG, una funcionalidad que nos permite acceder al contenido del log de transacciones de SQL Server. A lo largo de este artículo, veremos cómo funciona DBCC LOG, sus aplicaciones prácticas y algunas consideraciones que debemos tener en cuenta para utilizarlo eficazmente.

Introducción a DBCC LOG

En SQL Server el log de transacciones es una de las piezas fundamentales del sistema. Este log almacena una secuencia de todas las modificaciones realizadas en la base de datos, lo que nos permite, además, la recuperación de datos en caso de fallos y la replicación en entornos distribuidos. A través del comando DBCC LOG, podemos acceder directamente a este registro, algo que puede ser especialmente valioso en escenarios de depuración o análisis forense de la base de datos.

Sin embargo, a diferencia de otros comandos de la familia DBCC (Database Console Commands), DBCC LOG no está documentado oficialmente por Microsoft, lo que significa que su uso conlleva ciertos riesgos y limitaciones. No obstante, para aquellos de nosotros que manejamos SQL Server en profundidad, esta herramienta puede ofrecer una visión muy valiosa de lo que ocurre “bajo el capó” de nuestra base de datos.

¿Cómo funciona DBCC LOG?

El comando DBCC LOG nos permite visualizar el contenido del log de transacciones de una base de datos específica. Así, al ejecutar este comando, obtendremos una salida que incluye cada una de las entradas de este registro, desde operaciones de inserción, eliminación y actualización hasta transacciones de nivel más bajo como asignaciones de páginas o cambios en la estructura de las tablas.

La sintaxis básica para ejecutar DBCC LOG es la siguiente:

DBCC LOG ([NombreBaseDeDatos], [TipoDeSalida])

Como veis, el comando necesita de los parametros que yo he llamado NombreBaseDeDatos que es el nombre de la base de datos cuyo log queremos inspeccionar, y, del parametro TipoDeSalida que tiene que ser un número entre 0 y 4 que determina el nivel de detalle de la información que recibimos. Este segundo parámetro es crucial, ya que permite ajustar la cantidad y el tipo de datos que se devuelven, desde un resumen básico hasta una vista muy detallada de las operaciones.

Es importante entender que la salida generada por DBCC LOG puede ser extensa y, en muchos casos, poco intuitiva. No es un comando que se utilice habitualmente para tareas diarias, sino más bien en situaciones específicas donde necesitamos conocer el estado exacto de las transacciones en un momento dado.

¿Qué información nos devuelve?

La salida de DBCC LOG varía en función del parametro de tipo de salida que elijamos. Así, tendremos distintos niveles de detalle de más a menos entre 0 y 3 y todo el detalle con el parámetro 4 (igual que el 3) pero en este caso recogido en un volcado hexadecimal.

 

DBCC LOG(‘basededatos’,0) 

  • Current LSN
  • Operation
  • Context
  • TransactionID
  • LogBlockGeneration

DBCC LOG(‘basededatos’,1) 

  • Current LSN
  • Operation
  • Context
  • TransactionID
  • LogBlockGeneration
  • TagBits
  • Log Record Fixed Length
  • Log Record Length
  • Previous LSN
  • Flag Bits
  • Log Reserve
  • Description

DBCC LOG(‘basededatos’,2) 

  • Current LSN
  • Operation
  • Context
  • Transaction ID
  • LogBlockGeneration
  • Tag Bits
  • Log Record 
  • Fixed Length
  • Log Record Length
  • Previous LSN
  • Flag Bits
  • Log Reserve
  • AllocUnitId
  • AllocUnitName
  • Page ID
  • Slot ID
  • Previous Page LSN
  • Number of Locks
  • Lock Information
  • Description

DBCC LOG(‘basededatos’,3) 

  • Current LSN
  • Operation
  • Context
  • Transaction ID
  • LogBlockGeneration
  • Tag Bits
  • Log Record Fixed Length
  • Log Record Length
  • Previous LSN
  • Flag Bits
  • Log Reserve
  • AllocUnitId
  • AllocUnitName
  • Page ID
  • Slot ID
  • Previous Page LSN
  • PartitionId
  • RowFlags
  • Num Elements
  • Offset in Row
  • Modify Size
  • Checkpoint Begin
  • CHKPT Begin DB Version
  • Max XDESID
  • Num Transactions
  • Checkpoint End
  • CHKPT End DB Version
  • Minimum LSN Dirty Pages
  • Oldest Replicated Begin LSN
  • Next Replicated End LSN
  • Last Distributed Backup End LSN
  • Last Distributed End LSN
  • Repl Min Hold LSN
  • Server UID
  • SPID
  • Beginlog Status
  • Xact Type
  • Begin Time
  • Transaction Name
  • Transaction SID
  • Parent Transaction ID
  • Oldest Active Transaction ID
  • Xact ID
  • Xact Node ID
  • Xact Node Local ID
  • End AGE
  • End Time
  • Transaction Begin
  • Replicated Records
  • Oldest Active LSN
  • Server Name
  • Database Name
  • Mark Name
  • Master XDESID
  • Master DBID
  • Preplog Begin LSN
  • Prepare Time Virtual Clock
  • Previous Savepoint
  • Savepoint Name
  • Rowbits First Bit
  • Rowbits Bit Count
  • Rowbits Bit Value
  • Number of Locks
  • Lock Information
  • LSN before writes
  • Pages Written Command Type
  • Publication ID
  • Article ID
  • Partial Status
  • Command
  • Byte Offset
  • New Value
  • Old Value
  • New Split Page
  • Rows Deleted Bytes Freed
  • CI Table Id
  • CI Index Id
  • NewAllocUnitId
  • FileGroup ID
  • Meta Status
  • File Status
  • File ID
  • Physical Name
  • Logical Name Format LSN
  • RowsetId
  • TextPtr Column Offset Flags
  • Text Size
  • Offset Old Size
  • New Size
  • Description
  • Bulk allocated extent count
  • Bulk RowsetId Bulk AllocUnitId
  • Bulk allocation first IAM Page ID
  • Bulk allocated extent ids
  • VLFs added
  • InvalidateCache Id
  • InvalidateCache keys
  • CopyVerionInfo Source Page Id
  • CopyVerionInfo Source Page LSN
  • CopyVerionInfo Source Slot Id
  • CopyVerionInfo Source Slot Count
  • RowLog Contents 0
  • RowLog Contents 1
  • RowLog Contents 2
  • RowLog Contents 3
  • RowLog Contents 4
  • RowLog Contents 5
  • Compression Log Type
  • Compression Info
  • PageFormat PageType
  • PageFormat PageFlags
  • PageFormat PageLevel
  • PageFormat PageStat
  • PageFormat FormatOption

DBCC LOG(‘basededatos’,4) 

  • Current LSN
  • Operation
  • Context
  • Transaction ID
  • LogBlockGeneration
  • Tag Bits
  • Log Record Fixed Length
  • Log Record Length
  • Previous LSN Flag Bits
  • Log Reserve
  • Description
  • Log Record

Aplicaciones Prácticas de DBCC LOG

Una de las aplicaciones más directas de DBCC LOG es la identificación de transacciones específicas que han tenido lugar en la base de datos. Por ejemplo, en un escenario donde necesitamos auditar cambios realizados por un usuario o aplicación, podemos utilizar este comando para rastrear esas modificaciones en el log de transacciones.

Supongamos que necesitamos investigar un problema de corrupción de datos o pérdida de información. Al acceder al log de transacciones con DBCC LOG, podemos reconstruir los pasos previos al incidente y entender qué ocurrió exactamente. Este nivel de análisis es particularmente útil en entornos donde la estabilidad y consistencia de la base de datos son críticas.

Otra aplicación interesante es la optimización del rendimiento. Aunque DBCC LOG no se usa directamente para este fin, entender el log de transacciones puede ayudarnos a identificar patrones de uso que afecten al rendimiento. Por ejemplo, podríamos descubrir que ciertas transacciones están generando un número excesivo de registros, lo que a su vez podría estar ralentizando las operaciones de escritura en la base de datos.

Limitaciones y Consideraciones

Aunque DBCC LOG es una herramienta poderosa, su uso no está exento de limitaciones. La primera de ellas es la falta de documentación oficial, lo que significa que estamos trabajando en un terreno relativamente inexplorado. Es crucial tener cuidado al interpretar los resultados y no hacer suposiciones precipitadas basadas en la salida del comando.

Además, debido a la cantidad de información que puede devolver, es recomendable utilizar DBCC LOG en entornos controlados y con un propósito claro. No es un comando para ejecutarse indiscriminadamente en un entorno de producción sin un motivo justificado, ya que podría generar una gran carga en el sistema y dificultar la interpretación de los datos.

Otro aspecto a considerar es la compatibilidad. A lo largo de las diferentes versiones de SQL Server, la estructura interna del log de transacciones puede cambiar, lo que significa que los resultados de DBCC LOG pueden variar entre versiones. Esto puede complicar la utilización del comando en sistemas donde se utilizan múltiples versiones de SQL Server o donde se planea una migración.

Por último, el uso de procedimientos indocumentados, aunque no supone en sí mismo la pérdida del soporte por parte de Microsoft, este si puede no hacerse cargo si ve que el problema está relacionado con alguno de estos procedimientos.

 Dificultad de interpretación

Además de todo lo anteriormente mencionado, cabe destacar la dificultad para interpretar la salida de estos comandos, no solo por la complejidad de su contenido sino, además por la cantidad de registros generados. Fijaos en la siguiente imagen en la que, creo una base de datos nueva, en esa base de datos simplemente creo una tabla, sin ningún registro, no hago nada más y, sin embargo, la salida de DBCC LOG es de 461 filas

dbcc log

Conclusión

El comando DBCC LOG en SQL Server es una herramienta avanzada que, si bien no está documentada oficialmente, puede proporcionar información muy valiosa sobre el estado y las operaciones de una base de datos. Desde la auditoría de transacciones hasta la resolución de problemas complejos, este comando nos permite acceder a detalles intrincados del funcionamiento interno de SQL Server.

Es importante recordar que, debido a su naturaleza no documentada, debemos usar DBCC LOG con precaución y siempre con un propósito claro. En manos de un experto, puede ser la llave para desentrañar problemas difíciles o mejorar la comprensión de cómo se comporta una base de datos bajo determinadas condiciones.

En resumen, aunque DBCC LOG no es una herramienta para el uso cotidiano, su potencial en situaciones específicas lo convierte en un recurso valioso para quienes manejamos SQL Server a un nivel profundo. Si bien su interpretación puede ser compleja, el conocimiento que podemos extraer de este comando es difícilmente igualable, especialmente en contextos donde cada detalle cuenta.

 

Publicado por Roberto Carrancio en SQL Server, 0 comentarios
27
Sep
2024

Columnstore vs VertiPaq

Cuando gestionamos grandes volúmenes de datos, hay dos tecnologías de almacenamiento que suelen ser las principales protagonistas: el Columnstore de SQL Server y VertiPaq, el motor de almacenamiento de Power BI. Ambas tecnologías están diseñadas para optimizar el procesamiento de datos en entornos de análisis, pero lo hacen utilizando enfoques y arquitecturas diferentes. En este artículo, veremos en profundidad las similitudes y diferencias entre estas dos tecnologías, considerando aspectos como el rendimiento, la eficiencia en la compresión de datos y las características de uso que determinan su idoneidad para diferentes escenarios.

Antes de iniciar, es de justicia reconocer los méritos y es que, este artículo no habría sido posible sin el whitepaper “Vertipaq vs Columnstore” escrito por Alberto Ferrari de sqlbi que podéis descargar completo desde aquí. Es un documento con más de 12 años de antigüedad y casi 30 páginas dedicado a comparar el rendimiento entre ambas tecnologías del motor xVelocity introducido en  SQL Server 2012 para SQL Server y SSAS.

Columnstore de SQL Server: Desempeño y optimización

Los índices Columnstore en SQL Server son una solución avanzada que almacena datos en columnas en lugar de filas. Esta disposición mejora la compresión y reduce la cantidad de E/S necesaria para ejecutar consultas analíticas, especialmente en entornos de data warehousing. Sin embargo, el rendimiento del Columnstore no es uniforme en todos los escenarios. Por ejemplo, en consultas simples de agregación, SQL Server puede no aprovechar automáticamente los beneficios del índice Columnstore, requiriendo ajustes en las consultas para forzar el uso de este índice y lograr un rendimiento óptimo​.

En términos de tiempo de procesamiento, la reconstrucción completa de un índice Columnstore es significativamente más rápida que el procesamiento de una base de datos en Analysis Services con VertiPaq, lo que puede ser un factor decisivo en entornos donde la velocidad de procesamiento es crítica​.

VertiPaq en Power BI: Un motor de almacenamiento revolucionario

VertiPaq, utilizado por Power BI y SQL Server Analysis Services (SSAS) en su modalidad Tabular, está optimizado para el uso en memoria, ofreciendo una capacidad de respuesta excepcional al ejecutar análisis complejos en tiempo real. Su modelo de compresión en memoria permite cargar grandes volúmenes de datos y mantener una alta eficiencia en la ejecución de consultas. Además, VertiPaq maneja cálculos a nivel de hoja de manera extremadamente eficiente, superando en muchos casos al Columnstore en operaciones como conteos distintos y cálculos ponderados​.

No obstante, VertiPaq requiere que todo el modelo de datos esté en memoria, lo que puede ser una limitación si se trabaja con conjuntos de datos que superan la capacidad de la RAM disponible. En estos casos, SQL Server con Columnstore podría ser más adecuado, ya que SQL puede manejar de manera dinámica los datos en memoria, cargando y descargando información según sea necesario​.

Almacenamiento en columnas vs. almacenamiento en filas

Según acabamos de ver, el almacenamiento en columnas (ya sea en memoria como en VertiPaq o en disco como Columnstore) mejora el rendimiento de las consultas analíticas pero, seguro que os estáis preguntando por qué.

Sin entrar en detalle de bajo nivel que complicarían este artículo más de lo necesario, esta mejora es debida a la manera en que los datos se organizan y se acceden en este tipo de almacenamiento. 

En un sistema de almacenamiento tradicional basado en filas, como el que se utiliza en muchas bases de datos relacionales, los datos de todas las columnas de una fila se almacenan juntos en disco. Esto significa que cuando se realiza una consulta que necesita acceder a una o dos columnas específicas, el sistema tiene que leer la fila completa desde el disco, incluso si solo se necesita un subconjunto de las columnas.

Por el contrario, en un sistema de almacenamiento en columnas, los datos de cada columna se almacenan por separado. Es decir, todas las entradas de una columna se almacenan juntas. Esta estructura permite que las consultas que solo necesitan acceder a ciertas columnas puedan hacerlo de manera más eficiente, leyendo sólo los datos relevantes desde el disco.

Similitudes entre el Columnstore de SQL y VertiPaq de Power BI

Ambas tecnologías comparten un enfoque basado en columnas, lo que permite una compresión eficiente y un uso optimizado del almacenamiento. Además, tanto Columnstore como VertiPaq están diseñados para maximizar el rendimiento en consultas analíticas, lo que los hace ideales para entornos donde se requiere procesar grandes volúmenes de datos rápidamente. En ambos casos, la compresión de datos no solo reduce el espacio de almacenamiento, sino que también mejora la velocidad de las consultas, ya que se reduce la cantidad de datos a procesar​, como ya hemos visto en el apartado anterior.

Diferencias clave entre Columnstore y VertiPaq

A pesar de las similitudes, las diferencias entre Columnstore y VertiPaq son notables en varios aspectos. Por ejemplo, Columnstore se desempeña mejor en escenarios donde se aplican filtros a los datos, lo que le permite superar a VertiPaq en términos de velocidad cuando se trata de consultas que no requieren un escaneo completo de la tabla​.

Por otro lado, VertiPaq sobresale en operaciones que involucran cálculos complejos y conteos distintos, ofreciendo un rendimiento superior en estos casos debido a las optimizaciones inherentes a su motor de cálculo. Además, VertiPaq ofrece una rica capa de metadatos que facilita la creación de modelos de datos complejos y la implementación de medidas calculadas, lo que puede ser un punto decisivo en proyectos donde la facilidad de uso y la integración con herramientas de usuario final son importantes​.

Otra diferencia significativa es cómo cada tecnología maneja las relaciones muchos-a-muchos. VertiPaq maneja estas relaciones de manera extremadamente eficiente, lo que lo convierte en una opción superior en escenarios donde este tipo de relaciones son comunes. Columnstore, aunque también es competente en este aspecto, puede no igualar la velocidad de VertiPaq en todos los casos​.

Consideraciones adicionales

Más allá del rendimiento en consultas, es importante considerar otros factores como el tiempo de procesamiento y el uso de memoria. Como os he mencionado antes, Columnstore ofrece un tiempo de procesamiento significativamente más rápido al reconstruir índices, mientras que VertiPaq requiere que todo el modelo de datos esté en memoria, lo que puede ser una limitación en entornos con recursos de memoria limitados​.

Además, el uso de la caché en VertiPaq mejora significativamente el rendimiento en escenarios donde las mismas consultas se ejecutan repetidamente, ya que los resultados se almacenan en caché y se pueden recuperar rápidamente sin necesidad de volver a ejecutar la consulta completa​. En contraste, SQL Server no almacena en caché los resultados, lo que puede llevar a tiempos de respuesta más largos en consultas repetitivas.

Columnstore o VertiPaq, ¿cuál es mejor?

La elección entre el Columnstore de SQL Server y VertiPaq de Power BI depende en gran medida del entorno y las necesidades específicas de cada proyecto. VertiPaq, con su motor de almacenamiento en columnas altamente optimizado para el análisis en memoria, es ideal para escenarios donde necesitemos un rendimiento elevado en cálculos complejos y agregaciones, y donde los datos puedan ser cargados completamente en memoria. Su capacidad para manejar eficientemente consultas analíticas y ofrecer una rica capa de metadatos lo hace especialmente adecuado para modelos de análisis interactivos y ágiles en Power BI.

Por otro lado, el índice Columnstore de SQL Server brilla en entornos donde los datos no pueden ser completamente cargados en memoria, o donde necesitamos actualizaciones y escrituras frecuentes en grandes volúmenes de datos. Si bien el Columnstore también nos ofrece un almacenamiento basado en columnas, su integración con SQL Server permite un manejo más dinámico de la memoria, lo que es ventajoso en escenarios donde el tamaño del conjunto de datos excede la capacidad de la memoria disponible. Además, su capacidad para filtrar y procesar datos de manera eficiente en consultas específicas lo convierte en una opción poderosa para mejorar el rendimiento en bases de datos relacionales que manejan grandes volúmenes de datos.

En el contexto de Power BI, si bien no podemos usar directamente los índices Columnstore de SQL Server, podemos optar por usar DirectQuery para trabajar con datos en SQL Server y aprovechar esos índices. Sin embargo, esto puede implicar un compromiso en términos de rendimiento, debido a la latencia de la red, y funcionalidad (no todas las funciones DAX están disponibles en DirectQuery) en comparación con un modelo de datos totalmente importado y gestionado por VertiPaq.

Conclusión

En resumen, VertiPaq es la opción preferida cuando se necesita un rendimiento extremo en análisis interactivo y la memoria es suficiente para manejar los datos. El Columnstore de SQL Server, por su parte, es más adecuado en escenarios donde la gestión eficiente de grandes volúmenes de datos en disco es crítica, y se requiere flexibilidad en las operaciones de escritura y actualización. Debemos comprender las fortalezas y limitaciones de cada tecnología es fundamental para que podamos tomar las mejores decisiones informadas y, así, optimizar el rendimiento de nuestras soluciones analíticas en función de los requisitos específicos del proyecto.

 

Publicado por Roberto Carrancio en Índices, Power BI, Rendimiento, SQL Server, 0 comentarios
25
Sep
2024

Migrar SQL 2019 a 2022 con el menor tiempo de inactividad

El fin de soporte de SQL Server 2019 está a la vuelta de la esquina, eso ya lo sabemos. También hemos hablado en esta casa de cómo planificar una migración y, cuando lo hicimos, comentamos la posibilidad de valorar métodos para una migración Online, sin tiempo de inactividad o con el mínimo posible. Y, eso es justo de lo que os quiero contar hoy, esos métodos de migración casi transparentes para el usuario por su nula o baja inactividad.

Preparar una migración

Antes de entrar en las opciones de migración online, es fundamental preparar adecuadamente el entorno. La planificación es clave. Sin entrar mucho en detalle, que ya le dedicamos un artículo entero a este tema, debemos asegurarnos de que todas las aplicaciones que interactúan con la base de datos sean compatibles con SQL Server 2022. Una buena práctica es clonar el entorno de producción en un entorno de pruebas donde podamos simular la migración. Este paso extra nos permitirá identificar posibles inconvenientes y corregirlos antes de afectar el entorno productivo.

También es crucial realizar una evaluación de los requisitos de hardware y software, ya que SQL Server 2022 puede requerir actualizaciones en los sistemas operativos o en el hardware para aprovechar todas sus nuevas características. Una vez que hemos cubierto estos aspectos, estamos listos para explorar las opciones de migración online que nos ayudarán a reducir el tiempo de inactividad.

Migraciones Online (sin inactividad)

Una migración en caliente, también conocida como migración online, es el proceso en el que la base de datos se migra a un nuevo servidor o versión sin interrumpir el servicio o con una interrupción mínima. En lugar de detener las operaciones para realizar la migración, la base de datos permanece activa, y los usuarios pueden seguir accediendo a los datos mientras se lleva a cabo el proceso. Esto es especialmente crítico en entornos donde la disponibilidad continua es esencial, como en sistemas de comercio electrónico, fábricas que no pueden parar la producción o cualquier otro sistema que opere en tiempo real.

Un concepto clave asociado a las migraciones en caliente es el RTO, o «Recovery Time Objective» (Objetivo de Tiempo de Recuperación). El RTO se refiere al tiempo máximo tolerable durante el cual un sistema, aplicación o proceso puede estar inactivo antes de que se produzca un impacto significativo en la organización. En el contexto de una migración, el RTO define cuánto tiempo podemos permitir que la base de datos esté inaccesible durante el cambio, y es un factor crucial para determinar la estrategia de migración.

Cuando hablamos de minimizar el tiempo de inactividad, estamos trabajando directamente para cumplir con el RTO definido. Un RTO corto, como por ejemplo de unos pocos minutos, implica que necesitamos utilizar métodos avanzados de migración en caliente, como Always On, Database Mirroring o Log Shipping, que permiten que el tiempo de transición sea prácticamente imperceptible para los usuarios. De esta manera, podemos asegurar que la migración no solo sea efectiva, sino que también cumpla con las expectativas y requisitos de disponibilidad de la organización.

Migración con Always On: Alto coste, cero inactividad

Always On Availability Groups es, sin duda, una de las tecnologías más potentes y flexibles para lograr una migración con tiempo de inactividad cero o cercano a cero. La idea detrás de Always On es crear un grupo de disponibilidad que replique las bases de datos seleccionadas en uno o más nodos secundarios. Esto no solo ofrece alta disponibilidad y recuperación ante desastres, sino que también facilita las migraciones.

Always On existente

En el mejor de los casos, ya dispondremos de un Always On con dos o más servidores SQL Server 2019. En este caso, para aprovechar Always On en la migración a SQL Server 2022, lo primero que haremos será agregar un nuevo nodo (o varios) con SQL Server 2022 a nuestro grupo de disponibilidad actual. Es importante que el nodo con SQL Server 2022 tenga configurada una replicación síncrona para garantizar que no tendremos pérdida de datos. 

Una vez que el nodo 2022 esté sincronizado y funcionando correctamente, podemos proceder a realizar un failover manual al nuevo nodo. Durante este proceso, la mayoría de las conexiones se mantendrán activas y la interrupción será mínima (de pocos milisegundos).

Con el servicio balanceado al nodo SQL Server 2022, los nodos con SQL Server 2019 perderán la conexión, ya que no podrán alojar bases de datos de un servidor con una versión superior, por lo que tendremos que sacarlos del grupo de disponibilidad y actualizarlos a SQL 2022 o desmontarlos. En este punto, es el momento de añadir el resto de nodos con SQL Server 2022, si aún no lo habíamos hecho, para mantener la alta disponibilidad.

Si nuestro anterior Always On disponía de uno o varios Listener no tendremos que hacer nada más y no habrá ninguna pérdida de servicio. En caso contrario, las aplicaciones y usuarios deberán apuntar al nuevo servidor, con el tiempo de inactividad que conlleve el cambio por su lado.

Sin Always On anteriores

Si no disponemos de un grupo de alta disponibilidad Always On anterior la cosa se complica. Primero tenemos que asegurarnos de cumplir los requisitos para montar el Always On como disponer de una licencia SQL Server Enterprise o una sola base de datos (es la limitación de los Always On básicos con licencia estándar). Si cumplimos con estos requisitos, estamos listos para el siguiente paso, la configuración del Always On. Es importante destacar que este paso necesita de la instalación y configuración del servicio de clúster de Windows (WSFC) lo que nos puede requerir de uno o varios reinicios, con las consecuentes pérdidas de inactividad del servicio de base de datos.

Con el Always On ya configurado los pasos son los descritos en el apartado anterior: asegurarnos de que la replicación es correcta, balancear el servicio, modificar las conexiones de las aplicaciones para que apunten al nuevo servidor y desmontar el AG y apagar el servidor SQL Server 2019.

Migración con DB Mirroring: menor coste, baja inactividad

Database Mirroring es una antigua solución de alta disponibilidad anterior a la implementación de Always On pero que, hasta el día de hoy, se mantiene por compatibilidad. Además, no requiere de WSFC ni de licencia enterprise para funcionar lo que lo hace una solución ideal para nuestra migración con baja inactividad. 

Estos son los pasos a seguir para una migración de baja inactividad con DB Mirroring:

En el servidor SQL Server 2019:

  1. Hacer una copia completa de la base de datos.
  2. Hacer una copia de log de la base de datos.

En el servidor SQL Server 2022:

  1. Restaurar con No Recovery la copia completa de la base de datos
  2. Restaurar con No Recovery la copia de log de la base de datos.

Configurar el DB Mirroring:

  1. En el SQL Server 2019:
ALTER DATABASE TuBaseDeDatos SET PARTNER = 'TCP://SQL2022ServerName:5022';
  1.  En el SQL Server 2012:
ALTER DATABASE TuBaseDeDatos  SET PARTNER = 'TCP://SQL2019ServerName:5022';
  1. Verifica que la configuración es correcta y que se está replicando en tiempo real. Puedes usar este script:
SELECT 
     db_name(database_id) AS DatabaseName, 
     mirroring_state_desc, 
     mirroring_role_desc, 
     mirroring_partner_name, 
     mirroring_partner_instance 
   FROM 
      sys.database_mirroring; 

Migración:

  1. Detén las conexiones de los usuarios y balancea el DB Mirroring para convertir el servidor SQL Server 2022 en principal. Ejecuta este comando en el SQL Server 2022:
ALTER DATABASE TuBaseDeDatos SET PARTNER FAILOVER;
  1. Modifica las conexiones de tus aplicaciones y usuarios para que apunten al servidor SQL Server 2022 y restablece la conexión
  2. Elimina el DB Mirroring
-- En el Principal (ahora SQL Server 2022) 
ALTER DATABASE TuBaseDeDatos SET PARTNER OFF; 
-- En el secundario (SQL Server 2019) 
ALTER DATABASE TuBaseDeDatos SET PARTNER OFF; 

 

Como puedes ver, gracias a este método de migración la pérdida de servicio ha sido mínima, sin embargo, sí que llega a existir. Además cuantas más bases de datos tengas más se va a alargar el proceso que se tiene que repetir manualmente por cada una de ellas.

Migración con Log Shipping: bajo coste, inactividad moderada

El Log Shipping es otra alternativa para realizar la migración de SQL Server 2019 a 2022, especialmente en entornos donde el presupuesto es limitado o donde necesitemos una solución más sencilla. Aunque el Log Shipping no nos ofrezca las mismas ventajas que Always On o Database Mirroring en términos de disponibilidad continua, sigue siendo una herramienta efectiva para minimizar el tiempo de inactividad.

Para el proceso de migración con Log Shipping, configuraremos la copia y restauración periódica de los archivos de registro de transacciones desde el servidor principal con SQL Server 2019 a un servidor secundario con SQL Server 2022. A diferencia de los otros métodos, el Log Shipping requiere un breve tiempo de inactividad al momento de realizar el balanceo final al nuevo servidor.

Para llevar a cabo este balanceo, debemos asegurarnos de que todos los logs pendientes se hayan restaurado en el servidor con SQL Server 2022. Una vez hecho esto, deshabilitaremos el servidor principal y configuraremos el nuevo servidor como el principal. Aunque este método requiere un breve tiempo de inactividad, sigue siendo una opción viable para entornos donde la alta disponibilidad no es crítica o donde la simplicidad y el bajo coste son prioritarios.

Conclusión

Migrar de SQL Server 2019 a 2022 sin inactividad es un proceso que puede parecer difícil, pero con las herramientas y estrategias adecuadas, podemos minimizar significativamente el tiempo de inactividad necesario. Always On, Database Mirroring y Log Shipping ofrecen soluciones adaptadas a diferentes necesidades y presupuestos. Always On es la opción más completa para entornos que no pueden permitirse ningún tipo de interrupción. Database Mirroring ofrece una buena combinación de seguridad y simplicidad, mientras que Log Shipping es ideal para aquellos que buscáis una solución económica y efectiva.

Cada uno de estos métodos tiene sus particularidades y ventajas, y la elección entre ellos dependerá de las características y requisitos específicos de nuestro entorno. Lo que es innegable es que, con la planificación adecuada y el uso de estas tecnologías, podemos llevar a cabo una migración exitosa a SQL Server 2022, garantizando así la continuidad de nuestras operaciones y aprovechando las ventajas de la nueva versión con el mínimo impacto en nuestros usuarios.

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!

Publicado por Roberto Carrancio en Alta Disponibilidad, SQL Server, 0 comentarios