Cloud

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!

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.

Sep

2024

Operadores Spool en planes de ejecución

Una de las primeras cosas que hacemos, o por lo menos que deberíamos hacer, cuando estamos analizando el rendimiento de una consulta es mirar su plan de ejecución. En los planes de ejecución vemos paso a paso y gráficamente lo que hace nuestra consulta. Cada uno de estos pasos de los que hablamos está representado gráficamente por un operador y la mayoría son intuitivos y fáciles de comprender pero hay un tipo en concreto que parece que cuesta un poco más. Me refiero como no podía ser de otra manera a los operadores Spool. Estos componentes pueden marcar la diferencia en términos de eficiencia y tiempo de respuesta de las consultas. En este artículo, profundizaremos en los diferentes tipos de Spool, su propósito, cómo funcionan y cuándo deberíamos prestarles atención.

¿Qué es un operador Spool?

Antes de entrar en detalles, es importante entender qué es un operador Spool. Básicamente, son operadores que almacenan temporalmente un conjunto de filas durante la ejecución de una consulta. Este almacenamiento permite que SQL Server reutilice estos datos en lugar de volverlos a calcular o volver a leerlos desde el disco, lo que puede resultar en una mejora significativa del rendimiento en determinadas situaciones. Existen varios tipos de operadores spool en SQL Server y, aunque todos comparten la definición que hemos mencionado, cada uno tiene sus particularidades.

Table Spool

El «Table Spool» es el tipo de Spool más común en SQL Server. Su objetivo principal es almacenar el resultado de una subconsulta o una parte del plan de ejecución que es probable que se reutilice. Este tipo de operador suele aparecer cuando tenemos consultas que requieren repetir una operación costosa varias veces. Por ejemplo, si una subconsulta se ejecuta en múltiples ocasiones dentro de una misma consulta, SQL Server puede decidir almacenar temporalmente el resultado de esa subconsulta en un Table Spool para evitar esas múltiples ejecuciones.

Un detalle importante a considerar es que, aunque el Table Spool puede reducir el tiempo de ejecución en general, también consume memoria temporal para almacenar los datos, lo que podría impactar en la eficiencia si el tamaño del Spool es considerablemente grande.

Index Spool

El «Index Spool» se utiliza cuando SQL Server anticipa que necesitará un índice temporal para mejorar la búsqueda de datos en una consulta específica. Este operador crea un índice en memoria, que puede ser utilizado para acelerar operaciones como JOINs o búsquedas basadas en condiciones de filtrado. Aunque esta operación añade un paso adicional al plan de ejecución, la creación de un índice temporal puede resultar en un rendimiento significativamente mejorado, especialmente en consultas que trabajan con grandes volúmenes de datos.

La clave para entender el impacto de un Index Spool está en el balance entre el coste de crearlo y los beneficios que aporta en la fase de búsqueda. En escenarios donde se ejecutan varias búsquedas en un conjunto de datos sin un índice adecuado, este operador se convierte en una solución efectiva.

Row Count Spool

El «Row Count Spool» es un tipo de operador que se emplea principalmente para controlar el número de filas que se procesan en una operación. A diferencia de los Spool anteriores, este no almacena datos per se, sino que mantiene un conteo de las filas que pasan a través de él. Este operador suele aparecer en situaciones donde se requiere un número preciso de filas como resultado de una subconsulta, como cuando usamos la cláusula TOP o una condición de filtrado que limita las filas a procesar.

En resumen, este operador actúa como un portero de discoteca que asegura que solo pasen el número exacto de filas necesarias. Es especialmente útil en operaciones que pueden generar un gran número de filas intermedias, pero donde solo se necesita un subconjunto de ellas. Así, el Row Count Spool ayuda a evitar el procesamiento innecesario, optimizando el rendimiento de la consulta.

Window Spool

El «Window Spool» es menos común pero no menos importante. Este tipo de operador se emplea principalmente en consultas que utilizan funciones de ventana, como ROW_NUMBER(), RANK() o LEAD(). El propósito del Window Spool es soportar el cálculo de estas funciones, almacenando temporalmente el conjunto de datos sobre el cual se aplicarán las funciones de ventana.

Las funciones de ventana requieren acceso a un conjunto completo de datos para calcular correctamente sus resultados. El operador Window Spool permite que SQL Server mantenga un «almacén» de estas filas mientras las operaciones de ventana se ejecutan, garantizando así que el resultado sea el esperado. Aunque puede añadir cierta sobrecarga en términos de memoria, su beneficio en la correcta ejecución de funciones analíticas es crucial.

Optimización y uso

Entender cuándo y cómo aparecen los Spool en los planes de ejecución es vital para optimizar el rendimiento de nuestras consultas. Si bien estos operadores pueden mejorar la eficiencia en muchos casos, su uso inadecuado o innecesario puede tener el efecto contrario. Es fundamental analizar los planes de ejecución y evaluar si la presencia de un Spool está realmente justificada en base al coste adicional que implica su utilización.

En algunos casos, podríamos encontrar que la eliminación de un Spool innecesario, ya sea mediante la reescritura de la consulta o ajustando los índices, resulta en un rendimiento superior. También es importante recordar que estos operadores suelen consumir memoria temporal, por lo que su impacto en la carga general del sistema debe ser monitorizado de cerca.

Recursión

Las consultas recursivas son un ejemplo de la necesidad de operadores Spool. En una consulta recursiva típica, SQL Server tiende a utilizar dos tipos de Spool que resultan esenciales para su correcto funcionamiento y optimización: el Table Spool y el Index Spool.

Table Spool en recursividad

Al principio de una consulta recursiva, SQL Server suele emplear un Table Spool. Este operador, como hemos visto, se utiliza para almacenar el conjunto inicial de filas que formarán la base de la recursión, conocido como la parte ancla en un CTE recursivo. La función principal de este operador es capturar estas filas iniciales para que puedan ser reutilizadas a lo largo de las iteraciones recursivas sin necesidad de recalcular o volver a leer los datos desde el origen.

Este Table Spool es especialmente útil en este contexto porque permite que el proceso recursivo se inicie de manera eficiente, asegurando que las filas base estén disponibles para las iteraciones subsiguientes sin añadir un coste significativo de I/O o de CPU. Este operador se convierte en un «almacén temporal» que facilita la generación de los resultados recursivos de manera escalable.

Index Spool en recursividad

En la fase final de la recursión, cuando se procesan y ordenan los resultados, SQL Server suele introducir un Index Spool. Este operador crea un índice temporal en memoria sobre el conjunto de datos generado durante la recursión. La finalidad de este índice es acelerar la búsqueda y ordenación de los datos, especialmente en consultas que requieren un orden específico o que deben cumplir con condiciones adicionales de filtrado.

El Index Spool optimiza la fase de finalización de la consulta recursiva, permitiendo que SQL Server gestione grandes volúmenes de datos generados por la recursión de manera más eficiente. La creación de este índice temporal puede ser costosa en términos de memoria y CPU, pero su impacto positivo en el rendimiento de la consulta suele justificar su utilización, especialmente en estructuras de datos jerárquicas complejas.

Conclusión

Los operadores Spool en SQL Server son herramientas poderosas que, cuando se utilizan correctamente, pueden mejorar significativamente el rendimiento de nuestras consultas. Desde el Table Spool, que almacena datos para evitar cálculos repetidos, hasta el Window Spool, que soporta funciones analíticas, cada tipo de Spool tiene un propósito específico y un impacto en la forma en que SQL Server procesa las consultas.

Para sacar el máximo provecho de los Spool, es esencial comprender cómo y cuándo aparecen en los planes de ejecución y evaluar su eficacia en cada caso. Aunque estos operadores pueden añadir complejidad al plan de ejecución, su correcta utilización puede ser la clave para lograr un rendimiento óptimo en SQL Server.

En definitiva, los Spool no son solo un detalle técnico, sino una pieza fundamental en la optimización avanzada de consultas. Con el conocimiento adecuado, podemos utilizarlos para transformar consultas lentas en operaciones altamente eficientes, maximizando el rendimiento de nuestras bases de datos.

Sep

2024

Buenas prácticas en Power BI Report Server (PBIRS)

Continuamos con los artículos sobre Power BI Report Server, ya hemos visto tanto sus características principales como los consejos de implantación y mantenimiento y hoy, y para cerrar esta semana temática, vamos a hablar de buenas prácticas. Lo primero que tenemos que recordar es que Power BI Report Server (PBIRS) está construido sobre la base de SQL Server Reporting Service (SSRS), una herramienta de reporte de BI de Microsoft con más de 15 años en el mercado. Con esto quiero decir que la mayoría de las cosas que vamos a ver ahora os sonarán familiares si ya habéis administrado SSRS pero si no es así no os preocupéis que para eso lo vamos a ver.

Configuración avanzada de Report Server

Cuando instalamos PBIRS tendremos a nuestra disposición una herramienta de configuración calcada a la de SSRS donde podremos realizar las configuraciones más básicas de este servicio. Sin embargo, esto no es todo,habrá aspectos que configuraremos en el propio servicio web y otros, los más avanzados, para los que necesitaremos un SSMS. Y, en concreto, son tres de estas configuraciones de las que vamos a hablar en este apartado. Configuraciones que, para la mayoría de las empresas pueden funcionar pero, para otras igual no tanto.

Para acceder a estas configuraciones nos conectaremos a nuestro PBIRS desde nuestro Management Studio (SSMS) usando la opción de conexión a SQL Server Reporting Service (SSRS). Una vez conectados abriremos las propiedades de la instancia y accederemos a las propiedades avanzadas. Aquí, entre otras, podremos encontrar la siguientes configuraciones:

EnableMyReports

La configuración “Enable and disable My Reports» nos permite a los administradores activar o desactivar la funcionalidad de «Mis informes». Esta función, desactivada por defecto, ofrece a los usuarios la posibilidad de crear un espacio personal dentro del servidor donde pueden guardar y gestionar sus propios informes. Esto es similar al concepto Mi espacio de trabajo que tienen los usuarios dentro del servicio Power BI. Habilitar Mis informes es una excelente manera de fomentar la BI de autoservicio y puede ser beneficioso para fomentar la personalización y la autonomía de los usuarios, permitiéndoles trabajar de manera más eficiente sin sobrecargar los espacios compartidos del servidor. No obstante, dejarlo desactivado puede ser preferible en entornos donde la uniformidad y el control sobre los informes es una prioridad.

ExecutionLogDaysKept

ExecutionLogDaysKept es otra configuración importante que define cuántos días se conservan los registros de ejecución de informes en el servidor. Estos logs son fundamentales para el análisis de rendimiento y la solución de problemas, ya que contienen información detallada sobre cada ejecución de informes. Ajustar esta configuración nos permite a los administradores balancear entre la retención de información suficiente para análisis detallados y la gestión eficiente del espacio de almacenamiento. Por defecto esta propiedad está establecida en 60 días, un periodo de retención más largo puede ser útil para auditorías y análisis históricos, sobre todo si tienes informes que se ejecutan sólo una vez al mes o menos. Por otro lado, un periodo más corto puede ayudar a optimizar el rendimiento del servidor.

EnablePowerBIReportExportUnderlyingData

Por último, la configuración EnablePowerBIReportExportUnderlyingData controla si los usuarios tienen permiso para exportar los datos subyacentes de los informes de Power BI. Esta opción es crucial para mantener la seguridad y privacidad de los datos. Permitir la exportación puede ser necesario para usuarios que requieran analizar la información fuera de la plataforma, pero también puede suponer un riesgo si los datos son sensibles. Por ello, esta configuración debe ser ajustada con cuidado, asegurando que solo los usuarios adecuados tengan acceso a esta funcionalidad y que se cumplan las políticas de seguridad de la organización.

Si me preguntáis por mi opinión, yo soy totalmente partidario de deshabilitar esta opción. Además, un abuso de la descarga de información en horas de mucha actividad de usuarios puede suponernos un verdadero quebradero de cabeza.

Seguridad a nivel de carpetas en Report Server

Llegamos a una de las principales diferencias entre Power BI Report Server y el servicio en la nube de Power BI. Mientras en el cloud tenemos Workspaces que sirven como entornos aislados colaborativos para que los equipos desarrollen contenido de Power BI al unísono. Después creamos aplicaciones para facilitar la entrega del contenido a los usuarios. Estos conceptos no existen en Power BI Report Server. En PBIRS tendremos que usar carpetas.

Las carpetas dentro de Power BI Report Server (y SSRS) se comportan como carpetas dentro de un sistema de archivos. La seguridad a nivel de carpeta se puede aplicar para restringir el acceso a todo el contenido de la carpeta. Además, al igual que un sistema de archivos, se puede crear una jerarquía de carpetas. Esto es diferente a la naturaleza aplanada de App Workspaces dentro del servicio Power BI.

Gestión de los permisos

Estemos alojando informes en el servicio o en PBIRS, debemos realizar una planificación cuidadosa desde el principio para proteger adecuadamente su contenido. Normalmente, tiene sentido crear carpetas para diferentes departamentos o equipos de la empresa como, por ejemplo, ventas, contabilidad, marketing, etc…

Aunque en Power BI Report Server (PBIRS), también podemos definir la seguridad en elementos individuales (por ejemplo, un único informe), normalmente no es una práctica. En implementaciones grandes, podemos encontrarnos con decenas o cientos de informes y mantener individualmente los permisos sería una pesadilla. Del mismo modo tenemos que huir de los permisos a usuarios individuales y, siempre que sea posible, utilizar grupos de usuarios. Si llevamos esto a rajatabla, podremos proteger múltiples informes relacionados y habilitar su uso para un subconjunto de usuarios sin complicaciones.

En la mayoría de los casos, también recomiendo que os ciñais a una estructura de carpetas plana. De este modo, no solo será más fácil proteger las carpetas, también PBIRS coincidirá lógicamente con la estructura plana de Workspaces en el servicio Power BI. Esto nos facilitará la tarea de migración o transferencia del contenido de Power BI Report Server (PBIRS) al servicio Power BI en la nube si alguna vez queremos hacerlo.

Reutilizar un modelo de datos en Report Server

Una de las limitaciones de Power BI Report Server (PBIRS) frente al servicio de Power BI en la nube es la capacidad de utilizar un mismo modelo de datos para diferentes informes. Así, mientras que en Power BI en la nube todos nuestros informes pueden acceder a un mismo modelo, si tenemos 12 informes que usan el mismo modelo de datos, en Power BI Report Server (PBIRS) tendremos que mantener 12 copias del modelo de datos. Esto, no hace falta que os lo diga, es un problema a la hora de actualizar los modelos y puede generar una discrepancia de datos entre los informes, que, en el mejor de los casos, nos provocará una reprimenda por parte de los usuarios.

Sin embargo, nosotros que somos DBAs y sabemos de bases de datos y, sobre todo, de servicios de SQL Server, sabemos que podemos aprovecharnos de las capacidades de SQL Server Analysis Services para almacenar nuestras bases de datos dimensionales y, desde los informes de Power BI simplemente acceder a ese único origen de datos compartido para todos los reportes.

Analysis Services es una excelente opción si ya tenemos una inversión en SQL Server y sus componentes de BI, que la tendremos si hemos licenciado PBIRS con la licencia de SQL Server Enterprise. Sin embargo, si estamos implementando Power BI Report Server gracias al licenciamiento de Power BI Premium, también podemos aprovechar los conjuntos de datos que residen en la capacidad Premium como modelos de datos reutilizables.

Podemos establecer una conexión desde nuestros informes de Power BI a un conjunto de datos Premium como si fuera un modelo de Analysis Services. Para ello, debemos asegurarnos de que nuestra capacidad Premium tenga habilitada la lectura en la configuración del extremo XMLA.

Conclusión

En resumen, Power BI Report Server (PBIRS) es una herramienta muy potente, que, si se configura y gestiona adecuadamente, puede convertirse en un pilar fundamental para la inteligencia de negocio en tu organización. Desde la configuración avanzada para habilitar funciones como «Mis informes» o controlar la exportación de datos subyacentes, hasta la gestión cuidadosa de la seguridad a nivel de carpetas y la reutilización de modelos de datos, podemos optimizar cada aspecto de PBIRS para alinearlo con las necesidades y políticas de nuestra empresa. Implementar estas buenas prácticas no solo mejorará el rendimiento y la seguridad de nuestro entorno de reportes, sino que también facilitará futuras migraciones al servicio Power BI en la nube, asegurándonos que nuestra infraestructura de BI está preparada para el crecimiento y el cambio.

Sep

2024

PBIRS vs Power BI Service

Cuando hablamos de soluciones de Business Intelligence (BI) dentro del ecosistema de Microsoft, la primera opción que viene a la mente es Power BI. Sin embargo, dentro de esta herramienta tan robusta existen dos variantes principales que, a menudo, generan dudas sobre cuál elegir: Power BI Report Server (PBIRS) y el servicio de Power BI en la nube. En este artículo, exploraremos en profundidad las características, ventajas y limitaciones de cada opción, ayudándonos a discernir cuál se adapta mejor a nuestras necesidades organizativas.

¿Qué es Power BI?

Power BI es una suite de herramientas de análisis empresarial (BI) desarrollada por Microsoft que nos permite convertir datos en información útil mediante informes interactivos y visualizaciones impactantes. Dentro de Power BI tenemos integración de manera efectiva con una amplia gama de fuentes de datos, permitiéndonos analizar y compartir insights con facilidad prácticamente sea cual sea su origen. Además de poder combinar en un solo modelo de datos e informes datos de varios orígenes.

Hemos hablado de modelo de datos e informes y es que, Power BI Desktop (la aplicación que se instala en el ordenador y nos permite a los usuarios crear informes complejos y dashboards interactivos) consta de dos partes principales. Por un lado la integración de datos de los informes y su adaptación (lo que tradicionalmente se conoce como ETL) a cargo de Power Query y la propia aplicación para diseñar los informes que además permite cálculos avanzados con DAX.

Así, a grandes rasgos, con Power BI Desktop, podemos conectar, transformar y modelar datos antes de visualizarlos en gráficos y tablas que ayuden a tomar decisiones informadas. La interfaz es intuitiva y, a pesar de su poder, está diseñada para ser accesible tanto a analistas de datos experimentados como a usuarios menos técnicos. Una vez que los informes están listos, los podemos publicar y compartir a través de Power BI Service (en la nube) o mediante Power BI Report Server (PBIRS), según la infraestructura y las necesidades de la organización.

Entendiendo PBIRS y Power BI en la Nube

Antes de entrar en sus diferencias, es importante entender qué son PBIRS y el servicio de Power BI en la nube. PBIRS es una plataforma local de informes basada en SSRS que nos permite mantener los datos y reportes dentro de nuestra infraestructura local, sin necesidad de depender de servicios externos. Esta opción es especialmente útil para aquellas empresas con estrictos requisitos de seguridad o que operan en sectores altamente regulados. Por otro lado, Power BI en la nube ofrece un servicio completamente gestionado por Microsoft, con actualizaciones frecuentes, escalabilidad casi ilimitada y acceso desde cualquier lugar.

Seguridad y Cumplimiento. Punto para PBIRS

Uno de los principales argumentos a favor de PBIRS es la seguridad. Muchas organizaciones tienen normativas estrictas sobre dónde deben residir los datos, lo que hace que la opción de mantener todo «en casa» sea atractiva. Con PBIRS, el control total sobre los servidores, bases de datos y la red es una realidad. Esto es crucial en sectores como el financiero, sanitario o gubernamental, donde el cumplimiento de normativas es ineludible.

Además, PBIRS permite la integración directa con las políticas de seguridad corporativas existentes, como Active Directory, lo que facilita la implementación de controles de acceso granulares y personalizados. En contraste, Power BI en la nube, aunque seguro y conforme a muchas normativas internacionales, deja el control de la infraestructura en manos de Microsoft, lo que puede no ser ideal para todas las organizaciones.

Flexibilidad y Personalización. Otro punto para PBIRS

PBIRS nos ofrece una mayor flexibilidad en términos de personalización y control de la infraestructura. Podemos ajustar los servidores a las necesidades específicas de nuestros informes y modelos de datos, lo que es fundamental cuando trabajamos con grandes volúmenes de información o requerimos configuraciones especializadas. Además, PBIRS permite utilizar Reporting Services, Power BI y Excel, lo que proporciona una solución integral para la gestión de informes en una única plataforma.

En contraste, Power BI en la nube se enfoca más en la simplicidad y la facilidad de uso. Aunque ofrece un entorno muy completo, su flexibilidad en cuanto a personalización es menor, ya que estamos limitados a las opciones y configuraciones que Microsoft ha diseñado para el servicio. Sin embargo, esta «limitación» viene acompañada de una gestión simplificada y la eliminación de la carga de mantenimiento y actualizaciones de la infraestructura.

Licenciamiento. Punto para el Servicio

Un aspecto clave en la decisión de optar por PBIRS o Power BI en la nube, es el modelo de licenciamiento. En PBIRS, los usuarios pueden consultar informes sin necesidad de adquirir licencias adicionales. Una vez que el servidor está configurado y licenciado, cualquier usuario de la organización con acceso al servidor puede visualizar los informes sin coste adicional. Sin embargo, las opciones de licenciamiento de PBIRS son escasas y caras, muy caras. Realmente no podemos licenciar exclusivamente PBIRS y, si lo queremos usar debemos adquirir una licencia de otro producto que incluya este. Estas licencias de otros productos que incluyen PBIRS son SQL Server Enterprise con Software Assurance o una capacidad Premium de Power BI (mínimo una F64 de instancia reservada y no pago por uso).

Este modelo contrasta con el de Power BI en la nube, donde cada usuario que quiera acceder a los informes debe contar con una licencia, ya sea Power BI Pro o Premium. Aunque este modelo de suscripción tiene sus ventajas en términos de escalabilidad y simplicidad de gestión, puede resultar costoso para organizaciones grandes o aquellas con muchos usuarios ocasionales.

Esta diferencia en el licenciamiento hace que PBIRS sea poco atractivo ya que muchas empresas no pueden permitirse el desembolso de dinero del que estamos hablando. Una licencia de SQL Server Enterprise cuesta unos 14.000€ por cada dos cores del servidor (y que menos que 8 cores para un servidor decente, lo que suman ya más 55.000€ ) más luego la suscripción del Software Assurance y, para el otro modo de licenciamiento, una instancia reservada con capacidad F64 tiene un coste de suscripción de unos 8000€ al mes.

Escalabilidad y Mantenimiento. Otro punto para el servicio

La escalabilidad es otro aspecto donde las diferencias entre PBIRS y Power BI en la nube se hacen evidentes. Power BI en la nube ofrece una escalabilidad casi ilimitada, ya que la infraestructura de Microsoft Azure se encarga de todo. Esto significa que podemos empezar con un pequeño proyecto piloto y escalar sin problemas a nivel empresarial sin necesidad de preocuparnos por la capacidad del servidor o el rendimiento, solo por el coste.

Por otro lado, con PBIRS, la escalabilidad depende completamente de nuestra infraestructura local. Si nuestras necesidades crecen, deberemos estar preparados para invertir en más hardware, espacio y, seguramente, más personal para gestionar y mantener el entorno. Esto puede ser una barrera para organizaciones en rápido crecimiento o que experimentan picos estacionales en la demanda de informes.

El mantenimiento es otro punto clave. Power BI en la nube se actualiza automáticamente, con nuevas características y mejoras implementadas por Microsoft de manera constante. Esto garantiza que siempre tengamos acceso a la última tecnología sin necesidad de realizar cambios manuales en nuestra infraestructura. En cambio, con PBIRS, somos responsables de aplicar las actualizaciones y parches, lo que requiere un equipo dedicado y una planificación cuidadosa para evitar interrupciones en el servicio.

Costes y Retorno de la Inversión. ¿Empate?

A la hora de evaluar PBIRS frente a Power BI en la nube, los costes son un factor determinante. PBIRS suele requerir una inversión inicial significativa en hardware, licencias y recursos humanos. Además, los costes de mantenimiento y actualización deben considerarse a largo plazo. Sin embargo, para organizaciones que ya disponen de una infraestructura robusta, este coste puede ser amortizado más fácilmente.

Por otro lado, Power BI en la nube sigue un modelo de suscripción, lo que permite empezar con costes más bajos y escalarlos según el uso y las necesidades. Aunque a largo plazo, las suscripciones pueden acumularse, ofrecen la ventaja de no requerir una inversión inicial significativa y permiten a las organizaciones ajustar sus gastos según la evolución de sus requerimientos.

El retorno de la inversión (ROI) en ambos casos depende en gran medida de la naturaleza de la organización y de cómo se utilice la herramienta. PBIRS puede ofrecer un ROI más alto en entornos donde la seguridad y el control son primordiales, mientras que Power BI en la nube podría ofrecer un mejor ROI para organizaciones que valoran la flexibilidad y la capacidad de escalar rápidamente.

Facilidad de Implementación y Adopción. El cloud gana esta batalla

La facilidad de implementación es otra área donde Power BI en la nube sobresale. Al ser un servicio gestionado, la configuración inicial es mínima y la adopción por parte de los usuarios finales suele ser más rápida. Los informes pueden compartirse fácilmente, y el acceso a los mismos está garantizado desde cualquier lugar y dispositivo, lo que fomenta una cultura de datos más abierta y colaborativa.

Por otro lado, PBIRS puede requerir un proceso de implementación más complejo, especialmente si no contamos con una infraestructura avanzada o experiencia en la gestión de servidores de informes.

¿PBIRS o Power BI en la Nube?

La elección entre PBIRS y Power BI en la nube no es sencilla y depende en gran medida de las necesidades específicas de cada organización. Si la seguridad, el cumplimiento normativo y el control absoluto sobre la infraestructura son prioridades, PBIRS es la opción ideal. Si ya contamos en nuestra organización con una licencia de SQL Server Enterprise con SA ese problema de costes de licenciamiento se diluye y PBIRS pasa a ser una opción muy atractiva. Además, el hecho de que no se necesitan licencias adicionales para que los usuarios visualicen informes puede representar un ahorro significativo, en entornos con un gran número de usuarios.

Sin embargo, si valoramos la escalabilidad, la facilidad de uso y la reducción de la carga de mantenimiento, Power BI en la nube se posiciona como la opción más adecuada. Aunque implica un coste por usuario, la flexibilidad y el acceso global que ofrece son difíciles de igualar.

Conclusión

En resumen, ambas herramientas son complementarias y podríamos combinar un servicio en la nube con uno local. La clave está en evaluar cuidadosamente las necesidades de nuestra organización, los recursos disponibles y los objetivos a largo plazo antes de tomar una decisión. Al hacerlo, garantizamos que estamos invirtiendo en la solución que mejor se alinea con nuestra estrategia de BI.

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Antes de cerrar este artículo me gustaría agradecer la inestimable ayuda de mi amigo Ricardo Rincón, experto MVP en Power BI que me ha asesorado y ayudado, sobre todo a poner algo de luz en el tema del licenciamiento.

Sep

2024

Transacciones Distribuidas y DTC

Cuando hablamos de sistemas de bases de datos, uno de los retos más grandes es garantizar la consistencia de los datos en entornos distribuidos. No suele ser lo común pero, a medida que las arquitecturas de aplicaciones se vuelven más complejas, surge la necesidad de coordinar múltiples transacciones que puedan involucrar diferentes bases de datos o incluso diferentes servidores. Aquí es donde entra en juego el concepto de transacciones distribuidas, y en SQL Server, el Distributed Transaction Coordinator (DTC) juega un papel crucial.

¿Qué son las Transacciones Distribuidas?

Una transacción distribuida es aquella que abarca más de un recurso de red, como bases de datos o sistemas de archivos ubicados en diferentes servidores. A diferencia de una transacción local que afecta a una sola base de datos, las transacciones distribuidas tienen la capacidad de coordinar cambios en varias bases de datos, asegurando que todos los participantes de la transacción lleguen a un estado de compromiso o vuelvan a un estado previo en caso de fallo.

El desafío en las transacciones distribuidas es garantizar que todos los nodos involucrados lleguen a un consenso sobre el resultado de la transacción. Esto es fundamental para mantener la integridad de los datos y evitar inconsistencias que podrían llevar a resultados inesperados o, en el peor de los casos, a la corrupción de los datos.

Distributed Transaction Coordinator (DTC)

En SQL Server, el Distributed Transaction Coordinator (DTC) es el componente encargado de gestionar las transacciones distribuidas. Su función principal es asegurar que todas las partes de una transacción distribuida, que pueden involucrar múltiples bases de datos y servidores, se comprometan correctamente o se deshagan en caso de error.

DTC utiliza el protocolo de dos fases (2PC, por sus siglas en inglés) para coordinar las transacciones. Este protocolo se divide en dos fases: la fase de preparación y la fase de compromiso. En la primera fase, DTC pregunta a todos los participantes si están listos para comprometer la transacción. Si todos responden afirmativamente, se procede a la segunda fase, donde se envía la orden de compromiso. Si alguno de los participantes no puede comprometerse, se inicia el proceso de deshacer la transacción en todos los participantes, asegurando que el sistema vuelva a un estado coherente.

Configuración y uso de Transacciones Distribuidas en SQL Server

Para aprovechar las transacciones distribuidas en SQL Server, primero necesitamos asegurarnos de que DTC esté configurado y funcionando correctamente en todos los servidores involucrados. Esto implica la configuración tanto a nivel de sistema operativo como en SQL Server.

Configurar DTC a nivel sistema operativo y red

En cuanto a la configuración del sistema operativo, es crucial que DTC esté habilitado y configurado para permitir transacciones remotas, ya que, por defecto, estas suelen estar desactivadas por razones de seguridad. Para habilitar DTC, desde el panel de control tendremos que acceder a “Agregar o Quitar Componentes de Windows” y activar la opción “Habilitar el acceso DTC de red”. Una vez hecho esto, y reiniciado el servidor si se nos requiere, el equipo estará listo para admitir transacciones distribuidas.

Sin embargo, esto no es todo, es importante asegurarse de que las reglas del firewall permitan la comunicación entre los servicios DTC de los diferentes servidores. DTC usa llamadas al procedimiento remoto RPC por lo que los puertos necesarios son, en primer lugar el puerto 135 TCP y UDP para establecer la comunicación y después un puerto TCP dinámico del rango 49152-65535. Este rango se puede configurar cambiando configuraciones del registro de windows si lo deseamos pero lo importante es que, nuestro firewall admita conexiones tanto por el puerto 135 como por todos los del rango dinámico seleccionado.

Usar DTC en SQL Server

Una vez que DTC esté operativo, podremos comenzar a utilizar transacciones distribuidas en SQL Server. Esto se hace a través de la instrucción BEGIN DISTRIBUTED TRANSACTION, que inicia una transacción distribuida que abarca múltiples servidores. Es importante tener en cuenta que, aunque la sintaxis es similar a la de una transacción local, el alcance y la complejidad son considerablemente mayores.

Un ejemplo sencillo podría involucrar dos servidores SQL Server diferentes. Comenzamos la transacción distribuida en el primer servidor y realizamos las operaciones necesarias. Luego, nos conectamos al segundo servidor y realizamos más operaciones dentro de la misma transacción. Finalmente, decidimos si se comprometen los cambios (commit) o si se deshacen (rollback).

Consideraciones en el Uso de Transacciones Distribuidas

Aunque el uso de transacciones distribuidas y DTC ofrece grandes ventajas en términos de consistencia y fiabilidad, también presenta una serie de retos que debemos considerar.

En primer lugar, las transacciones distribuidas suelen ser más lentas que las locales debido a la sobrecarga de la coordinación entre múltiples nodos. Esto puede afectar el rendimiento de las aplicaciones, especialmente en sistemas con alta concurrencia.

Además, la complejidad de la configuración y la gestión de DTC puede ser un obstáculo en muchas organizaciones donde, también será común involucrar a varias personas de varios departamentos para el cambio. Es vital asegurarse de que todos los servidores involucrados estén correctamente configurados y que la comunicación entre ellos sea fluida. Cualquier problema en la configuración de DTC puede resultar en errores difíciles de diagnosticar, que pueden ser muy costosos de resolver en producción.

Otro aspecto a tener en cuenta es la fiabilidad del sistema. Aunque DTC está diseñado para manejar fallos, es esencial contar con mecanismos adicionales de recuperación y monitorización para minimizar el impacto de posibles fallos de red o de los servidores.

Por último, es fundamental considerar la seguridad en la configuración de DTC. Dado que las transacciones distribuidas pueden involucrar la transferencia de datos sensibles entre servidores, es necesario implementar medidas de seguridad robustas para proteger esta información. Esto incluye el uso de comunicaciones seguras, así como la correcta configuración de permisos y autenticaciones.

Buenas prácticas para la gestión de Transacciones Distribuidas

Para gestionar eficazmente las transacciones distribuidas en SQL Server, es importante seguir una serie de buenas prácticas que nos permitirán minimizar riesgos y maximizar el rendimiento.

En primer lugar, debemos evitar utilizar transacciones distribuidas a menos que sean absolutamente necesarias. Si es posible, debemos buscar alternativas, como la replicación o el uso de servicios distribuidos que manejen la consistencia eventual. Las transacciones distribuidas deben reservarse para casos en los que la consistencia estricta sea un requisito ineludible. Si, el primer consejo es no lo hagas, es lo que hay.

Cuando sea necesario utilizar transacciones distribuidas, es fundamental optimizar el diseño de las mismas para reducir al mínimo el tiempo que la transacción está abierta. Esto incluye realizar todas las operaciones preparatorias fuera de la transacción y asegurarse de que el código dentro de la transacción sea lo más eficiente posible.

Además, es recomendable implementar una monitorización continua del rendimiento y de los posibles errores de DTC. Existen herramientas en SQL Server que nos permiten rastrear y analizar el rendimiento de las transacciones distribuidas, así como diagnosticar problemas en tiempo real. Por ejemplo, SQL Server Profiler, xEvents o DMVs.

Conclusión

Las transacciones distribuidas y el uso de DTC en SQL Server son herramientas poderosas que permiten garantizar la consistencia de los datos en entornos complejos y distribuidos. Sin embargo, su uso requiere una planificación cuidadosa y una gestión rigurosa para evitar problemas de rendimiento y fiabilidad.

Es importante recordar que no todas las aplicaciones necesitan transacciones distribuidas. En muchos casos, existen soluciones alternativas que pueden ofrecer la consistencia y fiabilidad necesarias sin la complejidad adicional. Cuando se opte por utilizar transacciones distribuidas, debemos asegurarnos de seguir las buenas prácticas y mantener una supervisión constante para garantizar el éxito a largo plazo.

Si abordamos las transacciones distribuidas con una comprensión clara de sus beneficios y limitaciones, y si estamos dispuestos a invertir en su correcta implementación, podemos lograr una gestión eficiente y segura de nuestros sistemas distribuidos en SQL Server.

Sep

2024

Bloqueos Optimizados

Hoy vamos a hablar de una de las características nuevas que implementan las bases de datos de Azure para maximizar el rendimiento sin comprometer la integridad de los datos. Cuando diseñamos y gestionamos nuestras bases de datos, debemos considerar cómo se gestionan los bloqueos, especialmente en entornos con alta concurrencia. La gestión de bloqueos es crucial para garantizar que múltiples transacciones puedan ejecutarse en paralelo sin conflictos. En este artículo, exploraremos en profundidad los bloqueos optimizados en las Azure Databases, cómo funcionan y cómo pueden ser aprovechados para mejorar el rendimiento de nuestras aplicaciones.

¿Qué son los bloqueos optimizados en Azure SQL Database?

Los bloqueos optimizados son una característica avanzada de Azure SQL Database diseñada para reducir la contención (bloqueos) y, por tanto, mejorar el rendimiento de las transacciones en entornos con alta concurrencia. En esencia, esta característica permite al motor de bases de datos minimizar el tiempo durante el cual las transacciones mantienen bloqueos, reduciendo así la posibilidad de que otras transacciones tengan que esperar para acceder a los mismos recursos. En entornos con alta concurrencia, como los que a menudo manejamos en la nube, esta optimización puede marcar la diferencia entre una aplicación fluida y una plagada de cuellos de botella.

El principio básico detrás de los bloqueos optimizados es el uso eficiente de los recursos del sistema. Las bases de datos tradicionales suelen imponer bloqueos a nivel de fila, página o tabla, lo que puede llevar a que las transacciones se bloqueen entre sí si intentan acceder a los mismos datos. Con los bloqueos optimizados, Azure SQL Database ajusta dinámicamente el nivel de bloqueo, permitiendo que las transacciones adquieran solo los bloqueos necesarios y los liberen lo antes posible. De este modo, se mejora la eficiencia general del sistema.

¿Dónde puedo usar los bloqueos optimizados?

A día de hoy, los bloqueos optimizados son una característica exclusiva de las Azure SQL Databases, no vamos a encontrar esta funcionalidad ni en versiones de SQL Server ni en Azure Managed Instance. Si estamos trabajando con Azure SQL Databases (sea cual sea nuestro nivel de servicio) debemos saber que los bloqueos optimizados están habilitados por defecto y, por tanto, podremos esperar el comportamiento que veremos a continuación siempre y cuando no los deshabilitemos. También es importante mencionar que esta funcionalidad depende de la recuperación acelerada de base de datos (ADR) por lo que si en algún momento deshabilitamos ADR en nuestra base de datos perderemos la funcionalidad de los bloqueos optimizados.

Funcionamiento de los bloqueos optimizados

Para entender cómo los bloqueos optimizados logran mejorar el rendimiento, es imprescindible entender cómo funciona esta gestión a bajo nivel. Cuando una transacción se ejecuta en Azure SQL Database, el motor de la base de datos evalúa el impacto potencial de los bloqueos necesarios. Dependiendo de factores como la naturaleza de la consulta, el nivel de aislamiento de la transacción y la carga actual del sistema, el motor decide si aplicar un bloqueo exclusivo, compartido o, en algunos casos, ninguno en absoluto. De esto ya hemos hablado en nuestro artículo sobre los bloqueos y deadlocks.

Uno de los aspectos clave de esta optimización es la técnica conocida como «lock escalation» o escalado de bloqueos. En lugar de aplicar bloqueos a nivel de fila o página, que pueden ser demasiado restrictivos, el motor de Azure SQL Database puede optar por escalar el bloqueo a un nivel superior (como a nivel de tabla) o utilizar técnicas de versionado de filas (row versioning). Esto permite que múltiples transacciones accedan simultáneamente a diferentes partes de los datos sin interferir entre sí.

Además, los bloqueos optimizados se integran con otras características avanzadas de Azure SQL Database, como las transacciones de larga duración y el procesamiento de consultas en paralelo. El motor de la base de datos tiene la capacidad de ajustar dinámicamente la estrategia de bloqueo según la duración y complejidad de las transacciones, lo que minimiza el impacto en el rendimiento.

TID y LAQ: Las claves para entender esto

Si queremos profundizar en los bloqueos optimizados hay dos conceptos fundamentales que debemos dominar: el Transaction ID (TID) y el Lock Acquisition Queue (LAQ). Estos términos juegan un papel crucial en la forma en que el motor de base de datos gestiona y optimiza los bloqueos, especialmente en entornos con alta concurrencia.

Transaction ID (TID)

El Transaction ID (identificador de transacción) , conocido como TID, es un identificador único asignado por el motor de la base de datos a cada transacción que se inicia en Azure SQL Database. Este identificador es esencial para la gestión de bloqueos, ya que permite al sistema rastrear de manera precisa qué transacción está accediendo a qué recursos en un momento dado. Además, el TID facilita la implementación de estrategias de bloqueo como la escalada de bloqueos y el versionado de filas.

Cuando una transacción se ejecuta en Azure SQL Database, el TID se convierte en la referencia central para todas las operaciones que esa transacción realiza. Cada vez que la transacción intenta leer o modificar un registro, el motor de la base de datos utiliza el TID para determinar si es necesario adquirir un nuevo bloqueo, mantener un bloqueo existente o escalarlo. Esta capacidad de rastreo granular es lo que permite a Azure SQL Database aplicar bloqueos de manera eficiente y minimizar la contención entre transacciones.

El TID también juega un papel fundamental en la resolución de conflictos entre transacciones concurrentes. Si dos transacciones intentan acceder al mismo recurso al mismo tiempo, el motor de la base de datos utilizará los TIDs asociados para decidir cuál transacción obtendrá acceso al recurso y cuál tendrá que esperar o, en casos extremos, finalizará esa transacción y deberá ser reintentada. Este proceso es esencial para mantener la integridad de los datos y evitar condiciones de carrera, donde el resultado de una transacción podría depender del orden en que se completan otras transacciones.

TID en acción

Vamos a ver cómo aplica esto en la práctica con un ejemplo muy sencillo. Para ello partiremos de la siguiente consulta que, como veis, crea una tabla, inserta unos valores y en una transacción actualiza esos registros. Antes de cerrar la transacción consultamos los bloqueos e intentos de bloqueos generados para después cerrar la transacción y borrar la tabla.

CREATE TABLE foo
(foo1 int PRIMARY KEY not null
,foo2 int null);
INSERT INTO foo VALUES (1,10),(2,20),(3,30);
GO
BEGIN TRAN
UPDATE foo 
SET foo2=foo2+10;
SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_type in ('PAGE','RID','KEY','XACT');
COMMIT
GO
DROP TABLE IF EXISTS foo;

Si ejecutamos esto en SQL Server o en una base de datos de una instancia administrada de Azure (Azure Managed Instance) veremos que se generan cuatro registros, tres bloqueos exclusivos a nivel de clave y un intento de bloqueo exclusivo a nivel de página. Os dejo un ejemplo:

Sin embargo, la misma consulta sobre una base de datos de Azure con bloqueos optimizados solo genera un bloqueo exclusivo a nivel de transacción:

Lock Acquisition Queue (LAQ)

El Lock Acquisition Queue (bloqueo después de la calificación), o LAQ, es otro concepto clave en la gestión de bloqueos optimizados. La LAQ es esencialmente una cola en la que las transacciones esperan para adquirir un bloqueo sobre un recurso determinado. Cuando trabajamos en un entorno de base de datos concurrido, donde múltiples transacciones pueden intentar acceder al mismo recurso simultáneamente, la LAQ nos ayuda a gestionar y organizar estas solicitudes de bloqueo para minimizar el tiempo de espera y evitar conflictos.

Cuando una transacción intenta adquirir un bloqueo sobre un recurso que ya está bloqueado por otra transacción, se coloca en la LAQ correspondiente a ese recurso. A medida que los recursos se van liberando, las transacciones en la LAQ se procesan en orden, lo que garantiza que las transacciones que han estado esperando más tiempo tengan prioridad para acceder al recurso. Este enfoque ayuda a reducir la contención y asegura que las transacciones no se bloqueen indefinidamente, lo que nos podría causar tiempos de espera excesivos y degradación del rendimiento.

La LAQ no solo gestiona el orden en que las transacciones adquieren bloqueos, sino que también juega un papel crucial en la optimización de los bloqueos mismos. En lugar de simplemente otorgar un bloqueo cuando un recurso se libera, el motor de Azure SQL Database utiliza la información en la LAQ para decidir si es necesario escalar el bloqueo a un nivel superior, como a nivel de tabla, o si se puede mantener a un nivel más granular, como a nivel de fila. Esta flexibilidad es clave para maximizar la concurrencia y minimizar la sobrecarga de bloqueo.

LAQ en acción

Si recordáis cuando hablamos de los bloqueos, comentamos que las consultas se evalúan fila a fila para comprobar si se pueden realizar o creando primero un bloqueo compartido de actualización (U). En caso de no haber conflicto ese bloqueo escala a un bloqueo (X) antes de realizar la actualización. Este paradigma cambia cuando tenemos bloqueos optimizados y un nivel de aislamiento Read Committed Snapshot o RCSI (por defecto en las bases de datos de Azure) evaluando ahora las consultas contra la versión confirmada más reciente y en caso de no haber conflicto la transacción adquiere un bloqueo (X) y se completa.

¿Te ha sonado a chino todo esto? No te preocupes que te lo enseño con un ejemplo. Mira esta captura sobre una base de datos de Azure.

En el ejemplo anterior, he creado una tabla en la sesión de la izquierda, introducido tres registros y posteriormente he actualizado el primero dentro de una transacción que no he llegado a confirmar ni revertir la transacción. Mientras tanto, en la sesión de la derecha he actualizado otro de los registros de la tabla sin problema. Esto en SQL Server o en Azure Managed instance generaría un bloqueo y la transacción de la derecha no llegaría a completarse pues, aunque son registros diferentes, la tabla no tiene ningún índice y eso hace que el bloqueo (X) no se realice a nivel de fila. ¿No te lo crees? Te lo demuestro.

Beneficios de los Bloqueos Optimizados en entornos de alta concurrencia

Los entornos de alta concurrencia, como en los que solemos trabajar cuando tenemos aplicaciones empresariales críticas, son los que más se benefician de los bloqueos optimizados. En estos escenarios, múltiples usuarios o aplicaciones pueden estar accediendo a la base de datos simultáneamente, realizando lecturas y escrituras en paralelo. Sin una gestión adecuada de los bloqueos, es fácil que se produzcan cuellos de botella, donde una transacción tiene que esperar a que otra libere un recurso.

Con los bloqueos optimizados, Azure SQL Database reduce significativamente la posibilidad de que esto ocurra. Al minimizar el tiempo de bloqueo y ajustar dinámicamente el nivel de bloqueo, nos permite que más transacciones se ejecuten en paralelo sin interferir entre sí. Esto no solo mejora el rendimiento de la base de datos, sino que también reduce el tiempo de respuesta de las aplicaciones que dependen de ella.

Por ejemplo, en una aplicación de comercio electrónico con alta concurrencia de usuarios durante una campaña de ventas, los bloqueos optimizados aseguran que las transacciones de actualización de inventario y procesamiento de pedidos no se bloqueen mutuamente, permitiendo una experiencia de usuario fluida y sin interrupciones.

Inconvenientes de los Bloqueos Optimizados

Aunque los bloqueos optimizados están habilitados por defecto en Azure SQL Database desde Marzo de 2024 y, ahora mismo, no hay manera de deshabilitarlos, es importante que sepamos cómo funciona esta característica para evitar sustos. La clave para ello es entender, tanto la nueva gestión de bloqueos optimizados como el perfil de carga de trabajo de nuestra base de datos. Si bien los bloqueos optimizados son efectivos en la mayoría de los escenarios, ciertos tipos de consultas o transacciones pueden requerir ajustes específicos en la aplicación.

Resultados inesperados

Acabamos de ver un ejemplo de las bondades de los bloqueos optimizados en combinación con RCSI pero esto tiene más implicaciones. El hecho de que ahora las transacciones de escritura se evalúan contra la versión confirmada almacenada en el snapshot y no se bloqueen nos puede traer resultados inesperados. Por ejemplo suponed que tenemos una tabla con un campo ID de empleado y un campo sueldo. Pongamos que una transacción A quiere actualizar los datos del empleado con ID 1 de 1000 a 1100. Mientras esa transacción no ha terminado, tenemos otra transacción B que quiere actualizar los sueldos mayores que 1001 un 10%.

En un entorno tradicional de SQL Server la transacción B esperaría al bloqueo de la transacción A y se ejecutaría tras esta, dando un resultado de un sueldo de 1210 para el empleado con ID 1 pues cuando la transacción B se ejecute el update de la transacción A habrá finalizado y el sueldo será de 1100 cumpliendo con la condición de sueldo mayor que 1001. Sin embargo, con los bloqueos optimizados y RCSI la transacción B se evaluaría contra la última versión confirmada (el snapshot de antes de iniciar la transacción A) y, por tanto, el empleado con ID 1 no cumpliría con la condición de la consulta.

Sin bloqueos Optimizados:

Con bloqueos Optimizados:

¿Podemos hacer algo?

¿Os había dicho que los bloqueos optimizados no se pueden deshabilitar? Vamos a matizarlo. Hemos visto ya que para que funcionen los bloqueos optimizados tenemos que tener habilitado ADR, pues bien esto es como no decir nada pues ADR está habilitado siempre en las bases de datos de Azure sin posibilidad de deshabilitarse.

Entonces, si no puedo deshabilitar los bloqueos optimizados ni ADR, ¿qué opción tengo? Realmente pocas. Básicamente, nuestra única opción es jugar con los niveles de aislamiento. Por definición esta característica es incompatible con Serializable y con Repeteable Reads por lo que cambiar esta configuración sería nuestra única opción. Y seamos sinceros, esto es una broma de mal gusto, no es viable en una base de datos con alta concurrencia. Aunque, en algunos casos, un nivel de aislamiento más alto puede ser necesario para garantizar la integridad de los datos, esto también incrementa el tiempo de bloqueo, y mucho, haciendo inviable técnicamente esta solución teórica.

Conclusión

En resumen, los bloqueos optimizados en Azure SQL Database representan un cambio importante en la gestión de concurrencia. Teóricamente para mejorar el rendimiento de nuestras aplicaciones pero, en ocasiones puede ser un problema. En este momento, se hace imprescindible conocer a fondo los conceptos de Transaction ID (TID) y Lock Acquisition Queue (LAQ) para que esta gestión de bloqueos no nos juegue una mala pasada. Podéis pensar que la solución pasa por migrar nuestras bases de datos de Azure a una instancia administrada pero, seamos sinceros ¿Cuanto creeis que tardarán en aplicar este cambio allí también?

Esto nos genera un debate muy interesante sobre la pérdida de control que hemos sufrido en la nube, especialmente en la infraestructura SAAS. Este caso es especialmente delicado pues un cambio de configuración sin posibilidad de marcha atrás ha cambiado completamente el comportamiento de nuestras aplicaciones pudiendo generar resultados inesperados. Y tú, ¿qué opinas? Te leo en los comentarios.

Cloud

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes