Rendimiento

Hoy vamos a hablar de un tema muy interesante y relevante para los DBA: la TempDB de SQL Server. En este artículo vamos a intentar explicar qué es la TempDB, para qué sirve, cómo optimizarla y qué recomendaciones hay seguir para gestionarla correctamente.

¿Qué es la TempDB de SQL Server?

La TempDB es una base de datos especial que SQL Server utiliza para almacenar datos temporales, como tablas temporales, variables de tabla, cursores, versiones de filas, resultados intermedios de consultas, etc. Esta base de datos se crea cada vez que se inicia el servicio de SQL Server y se borra al apagarlo. Por lo tanto, no es necesario hacer copias de seguridad ni restauraciones de ella

¿Por qué es importante la TempDB para el rendimiento de SQL Server?

La TempDB es muy importante para el rendimiento y la estabilidad de SQL Server, ya que se usa en casi todas las operaciones que realiza el motor de base de datos. Si está mal configurada o tiene problemas de contención, puede afectar negativamente a todo el sistema. Por eso, es fundamental seguir las mejores prácticas que proporciona Microsoft en su documentación. Muchas veces hemos hablado de estas buenas prácticas en otros artículos pero hoy vamos a profundizar un poco más sobre ellas.

¿Por qué se llena la TempDB?

Como hemos dicho, es una base de datos que interviene en la mayoría de operaciones de SQL Server. Es lógico pensar que es de las bases de datos con más uso entonces, pero hay varias razones que pueden hacer que crezca en exceso. Veámoslo:

• El uso excesivo de datos temporales por parte de las aplicaciones o los usuarios. Esto puede generar un gran número de objetos temporales que ocupan espacio en la TempDB hasta que se eliminan o se desconecta la sesión que los creó.
• El uso de características que requieren versiones de fila, como el aislamiento de instantáneas o el aislamiento mediante versiones de fila. Esto puede generar un gran volumen de versiones de fila que se almacenan en el almacén de versiones de la TempDB hasta que se liberan o se desencadenan.
• El uso de la TempDB como espacio de intercambio cuando se llena la memoria RAM del servidor. Esto puede ocurrir cuando se realizan operaciones que generan grandes volúmenes de datos intermedios o finales que no caben en memoria y tienen que ser escritos en disco.

Cuando la TempDB se llena, puede provocar errores en las operaciones que dependen de ella y afectar al rendimiento general del sistema. Por eso, es importante monitorizar el uso y el crecimiento de la TempDB y tomar medidas preventivas o correctivas para evitar que se llene.

Uso de la TempDB como espacio de intercambio

Otro aspecto a considerar es el uso de la TempDB como espacio de intercambio cuando se llena la memoria RAM del servidor. SQL Server puede usar la TempDB para almacenar los resultados intermedios o finales que no caben en memoria y que tienen que ser escritos en disco. Esto puede ocurrir cuando se realizan operaciones como ordenaciones, combinaciones hash o agregados hash. Este uso puede aumentar considerablemente el tamaño y la actividad de la TempDB y afectar al rendimiento general del sistema. Cuando esto pase veremos un aviso de alerta en los planes de ejecución de las consultas. Para evitarlo o minimizarlo, se recomienda lo siguiente:

• Aumentar la memoria RAM del servidor para que pueda albergar más datos en memoria y reducir la necesidad de usar la TempDB como espacio de intercambio si es necesario.
• Optimizar las consultas que generan grandes volúmenes de datos temporales, usando índices adecuados, estadísticas actualizadas, planes de ejecución óptimos y técnicas de programación eficientes.
• Monitorizar el uso de la TempDB como espacio de intercambio, usando el monitor de rendimiento o los DMV sys.dm_db_task_space_usage (devuelve la actividad de asignación y desasignación de páginas por tarea de la base de datos) y sys.dm_db_session_space_usage (devuelve el número de páginas asignadas y desasignadas por cada sesión en la base de datos).

¿Cómo optimizar la TempDB de SQL Server?

Para optimizar la TempDB de SQL Server, hay que tener en cuenta varios aspectos, como el tamaño, el número de archivos, la ubicación, la caché y el uso de datos temporales. A continuación, os damos algunas recomendaciones que podéis aplicar para mejorar el funcionamiento de la TempDB.

Tamaño de la TempDB

Asignar un tamaño adecuado a la TempDB para evitar el crecimiento automático, que puede causar fragmentación y pérdida de rendimiento. Se puede usar el monitor de rendimiento o el DMV sys.dm_db_file_space_usage (devuelve información de uso del espacio para cada archivo de datos de la base de datos) para estimar el espacio necesario. La TempDB se llena cuando los datos temporales ocupan todo el espacio asignado y no hay más espacio disponible en el disco. Para evitar esto, se debe monitorizar el uso de la TempDB y ampliar su tamaño si es necesario.

Número de archivos de la TempDB

Crear varios archivos de datos para la TempDB, uno por cada núcleo del procesador, hasta un máximo de 8. Los archivos deben tener el mismo tamaño y el mismo factor de crecimiento, hasta SQL 2016 debiamos activar la traza  -T1117 en el arranque para que todos los ficheros crezcan simultáneamente pero en las nuevas versiones ya no es necesario. Esto ayuda a reducir la contención en las estructuras internas de la base de datos, como los PFS, GAM y SGAM por lo que mejora el rendimiento. Los PFS son las páginas que almacenan los bits que indican si una página está libre o usada. Los GAM son las páginas que almacenan los bits que indican si un intervalo de páginas está libre o usado. Los SGAM son las páginas que almacenan los bits que indican si un intervalo de páginas tiene al menos una página libre.

Ubicación de la TempDB

Colocar la TempDB en una unidad de disco diferente a las demás bases de datos, preferiblemente en un disco SSD o en una SAN con alto rendimiento y baja latencia. Esto mejora la velocidad de lectura y escritura de los datos temporales.

Caché de planes de ejecución

Habilitar la opción «optimize for ad hoc workloads» en las propiedades del servidor, para evitar que se almacenen en la caché los planes de ejecución de consultas que solo se usan una vez.

Uso de datos temporales

Evitar el uso excesivo de tablas temporales, variables de tabla y cursores, y usar alternativas como tablas derivadas, CTE o funciones escalares. Esto reduce la carga de trabajo sobre la TempDB y mejora la eficiencia de las consultas.

¿Cómo vaciar la TempDB?

En algunos casos, puede ser necesario vaciar la TempDB para liberar espacio en disco o resolver problemas de contención o corrupción. Para ello, se pueden usar los siguientes métodos:

– Usar el comando DBCC SHRINKDATABASE o DBCC SHRINKFILE. Estos comandos permiten reducir el tamaño de la TempDB o de sus archivos individuales, liberando el espacio no usado. Estos comandos se pueden ejecutar mientras el servidor está en funcionamiento, pero pueden causar bloqueos, interbloqueos o pérdida de rendimiento. Además, no garantizan que se pueda reducir el tamaño deseado, ya que dependen del uso y la distribución de los datos temporales. Podemos usar este script:

USE [tempdb]
GO
select 'ALTER DATABASE TEMPDB MODIFY FILE (NAME=' + name + ', FILENAME=''' + physical_name +''',SIZE=262144KB,FILEGROWTH=65536KB)'
FROM sys.database_files order by type_desc
GO
CHECKPOINT;
GO
DECLARE @sqlCmd VARCHAR(MAX)
SET @sqlcmd=''
SELECT @sqlCmd = @sqlCmd + 'DBCC SHRINKFILE (' + name + ' ,0, TRUNCATEONLY);'
FROM sys.database_files
--PRINT @sqlCmd
EXEC (@sqlCmd)

– Reiniciar el servicio de SQL Server. Esto hará que se vuelva a crear la TempDB con el tamaño configurado y se eliminen todos los datos temporales que había en ella. Este método es el más sencillo, pero requiere detener el servidor y afecta a todas las bases de datos y conexiones. Será nuestro último recurso y lo evitaremos siempre que sea posible.

Conclusión

Esperamos que este artículo os haya sido útil y os animamos a seguir aprendiendo sobre SQL Server con nosotros. 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 LinkedIn al que te puedes unir. ¡Hasta la próxima!

En este artículo vamos a hablar de una configuración muy importante para el rendimiento y la recuperación de nuestras bases de datos en SQL Server: el Target Recovery Time. Esta configuración nos permite especificar el tiempo máximo que queremos que tarde SQL Server en recuperar una base de datos después de un fallo o un reinicio. ¿Por qué es importante esto? Porque afecta directamente a la frecuencia y el tamaño de los checkpoints que se realizan en la base de datos, y por tanto, al uso de los recursos de disco y memoria.

¿Qué son los checkpoints y cómo funcionan?

Los checkpoints son operaciones que escriben las páginas modificadas en memoria (dirty pages) al disco, para mantener la consistencia entre el buffer pool y los archivos de datos. Como ya vimos en nuestro artículo sobre Checkpoints en SQL Server, los checkpoints se pueden realizar por diferentes motivos: porque el sistema lo decide automáticamente, porque lo forzamos manualmente, porque hacemos un backup, porque cambiamos el estado de la base de datos, etc. Cada checkpoint tiene un impacto en el rendimiento, ya que consume recursos de E/S y puede generar contención con otras operaciones. Además, cada checkpoint determina el punto de inicio de la recuperación de la base de datos, ya que SQL Server solo tiene que rehacer o deshacer las transacciones que ocurrieron después del último checkpoint.

¿Qué es el Recovery Interval y cómo se usa?

Por defecto, SQL Server usa un algoritmo llamado Automatic Checkpoint para decidir cuándo hacer un checkpoint. Este algoritmo se basa en el Recovery Interval, que es un valor global para todas las bases de datos que indica el tiempo aproximado que queremos que tarde la recuperación. El valor por defecto del Recovery Interval es 0, lo que significa que SQL Server intentará que la recuperación tarde menos de un minuto. Para conseguir esto, SQL Server estima cuántas transacciones puede recuperar en un minuto, y hace un checkpoint cuando se alcanza ese número. Sin embargo, este algoritmo tiene algunos inconvenientes: no tiene en cuenta el tamaño o la duración de las transacciones, ni el impacto que tienen los checkpoints en el rendimiento. Además, el Recovery Interval es un valor aproximado, no una garantía, y solo se aplica a las bases de datos que usan el modelo de recuperación Simple o Bulk-Logged.

¿Qué es el Target Recovery Time y cómo se usa?

Para solucionar estos problemas, SQL Server introdujo una nueva opción a partir de la versión 2012: el Target Recovery Time. Esta opción nos permite especificar el tiempo máximo de recuperación para cada base de datos individualmente, independientemente del modelo de recuperación que use. El valor por defecto del Target Recovery Time es 0 para las versiones anteriores a 2016, y 60 para las versiones 2016 o posteriores. Esto significa que si usamos una versión anterior a 2016, se usa el algoritmo del Recovery Interval por defecto, pero si usamos una versión 2016 o posterior, se usa el algoritmo Indirect Checkpoint por defecto con un tiempo máximo de recuperación de 60 segundos. Pero si le damos un valor distinto de 0 (en versiones anteriores a 2016) o distinto de 60 (en versiones 2016 o posteriores), SQL Server usará el algoritmo Indirect Checkpoint con el valor especificado. Este algoritmo hace checkpoints más frecuentes y más pequeños, para asegurar que el tiempo de recuperación no supere el valor especificado. Además, este algoritmo tiene en cuenta el tamaño de las transacciones y el impacto de los checkpoints en el rendimiento, y ajusta la frecuencia y el tamaño de los checkpoints dinámicamente.

¿Qué ventajas e inconvenientes tiene usar el Target Recovery Time?

¿Qué ventajas tiene usar el Target Recovery Time y el Indirect Checkpoint? Pues varias:

• Podemos tener un control más fino sobre el tiempo de recuperación de cada base de datos, y ajustarlo según nuestras necesidades.
• Podemos reducir el tiempo de recuperación y mejorar la disponibilidad de nuestras bases de datos.
• Podemos reducir el impacto de los checkpoints en el rendimiento, ya que se hacen más pequeños y más frecuentes.
• Podemos reducir la presión sobre el buffer pool, ya que se liberan más rápidamente las páginas modificadas.
• Podemos mejorar la compatibilidad con las nuevas características de SQL Server, como Accelerated Database Recovery (ADR), que requiere usar Indirect Checkpoint para funcionar correctamente.

¿Y qué inconvenientes tiene? Pues también algunos:

• Podemos aumentar el consumo de recursos de disco, ya que se hacen más escrituras al hacer más checkpoints.
• Podemos aumentar la fragmentación interna y externa de los archivos de datos, ya que se escriben más páginas en diferentes posiciones.
• Podemos aumentar el riesgo de corrupción de datos, si hay algún problema con el hardware o con el sistema operativo durante los checkpoints.
• Podemos tener problemas con algunas operaciones que requieren un checkpoint completo, como cambiar el estado o el modelo de recuperación de la base de datos.

¿Cómo cambiar el Target Recovery Time y el Recovery Interval?

Como vemos, no hay una respuesta única sobre si debemos usar o no el Target Recovery Time y el Indirect Checkpoint. Depende de cada caso, de las características de nuestras bases de datos, de nuestros requisitos de rendimiento y disponibilidad, y de los recursos que tengamos disponibles. Lo que sí podemos hacer es probar y medir el efecto que tiene esta configuración en nuestros entornos, y tomar una decisión informada.

Para cambiar el valor del Target Recovery Time de una base de datos, podemos usar el siguiente comando:

ALTER DATABASE [NombreDeLaBaseDeDatos] SET TARGET_RECOVERY_TIME = [ValorEnSegundos] WITH NO_WAIT;

También podemos cambiarlo desde el Management Studio, en las propiedades de la base de datos, en la sección Options. Además del Target Recovery Time, también podemos cambiar el Recovery Interval de las instancias SQL Server, que es el valor global que se usa cuando el Target Recovery Time es 0.

Conclusión

Como hemos visto, podemos usar Recovery Interval y Target Recovery Time para tener mayor control sobre los checkpoints de nuestras bases de datos SQL Server. Con esto no solo conseguimos optimizar el uso de disco duro al reducir los requerimientos de E/S sino que además, controlamos el tiempo de recuperación tras un fallo del sistema. Recuerda siempre hacer las pruebas en un entorno dedicado antes de aplicar en producción. Y como siempre digo, si no es para resolver un problema, piensa si realmente merece la pena el riesgo.

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

Si recordáis, en el pasado artículo sobre la recuperación acelerada de base de datos hablamos de los Checkpoints. Como no lo habíamos explicado en profundidad y ya que son importantes para entender el comportamiento de nuestro SQL Server, les vamos a dedicar el post de hoy. Entender estos conceptos es clave para poder ajustar correctamente varias de las configuraciones de rendimiento que implementa SQL Server.

¿Qué son los Checkpoints?

Un checkpoint o punto de control de base de datos es, como su nombre indica, un punto conocido correcto sobre el que SQL Server aplicará los cambios del log durante la recuperación ante un error. 

Cuando ejecutamos una transacción de escritura (INSERT, UPDATE o DELETE), esta empezará a modificar datos pero, mientras no se confirme los cambios no serán definitivos. Esta es la teoría pero, la realidad difiere un poco. Por motivos de rendimiento, SQL Server carga las páginas de disco en memoria, escribe las modificaciones en las páginas en memoria y cada cierto tiempo, vuelca esas páginas modificadas en memoria (llamadas páginas desfasadas) al disco y al el log de transacciones. Esto es lo que llamamos checkpoint.

Tipos de Checkpoints

SQL Server tiene varios tipos de checkpoints en función de cómo y cuándo se ejecutan. Para entenderlo tenemos que conocer las opciones de configuración target recovery time de base de datos y la opción de instancia recovery interval de las que hablaremos más adelante.

Checkpoints Automáticos

Cuando la opción target recovery time de una base de datos está establecida en 0 se producirán checkpoints automáticos en segundo plano. Habrá un checkpoint cada vez que se alcance el número de páginas que SQL calcula que puede procesar en el tiempo establecido en la opción recovery interval (si está en 0 el tiempo será de 1 minuto).

El tiempo entre checkpoints puede variar en función del uso de las bases de datos, siendo mayor cuantas más transacciones de escritura se procesen. 

Si nuestras bases de datos están en modo simple, cuando el log de transacciones se llene al 70% de su tamaño máximo se detendrá el proceso de checkpoints. Sin embargo, a no ser que el log no se vacíe por algún otro motivo (replicaciones pendientes por ejemplo) el checkpoint automático truncará el log. 

Cuando ocurre un problema y SQL se detiene bruscamente, el tiempo de recuperación de nuestras bases de datos dependerá de la capacidad de E/S de nuestros discos para rehacer las páginas desfasadas al momento del bloqueo. La opción recovery interval tampoco afecta al tiempo para deshacer una transacción de larga duración. Esto hace que no se pueda calcular previamente el tiempo de inactividad en estos casos.

Checkpoints Indirectos

Con la llegada de SQL 2012 se presentaron los checkpoints indirectos gracias a la configuración de base de datos target recovery time. En un principio establecida a 0 por defecto hasta SQL 2016, donde se establece en 60 segundos por defecto para todas las nuevas bases de datos (las creadas previamente o restauradas de una versión anterior permanecerán en 0 por defecto). Esta configuración cambia el paradigma y, con ella, pasamos de contar el número de transacciones como pasaba con los checkpoints automáticos a contar el número de páginas desfasadas para calcular el tiempo de recuperación.

Con los checkpoint indirectos habilitados cualquier operación DML en la base de datos puede provocar un checkpoint en segundo plano para que el tiempo de recuperación se mantenga dentro de los límites establecidos. Esto que parece una buena opción puede no serlo tanto en entornos con muchas transacciones donde el incremento de E/S puede generarnos un cuello de botella en los discos duros. Por otro lado, reducir el tiempo de recuperación y por tanto aumentar la frecuencia de los checkpoints puede reducir los picos de consumo de E/S.

Gracias a estos puntos de control indirectos logramos un mayor control sobre los tiempos de recuperación de las bases de datos, siempre y cuando una transacción prolongada no provoque un tiempo de rollback elevado.

Checkpoint Manual

Estos son los checkpoints que podemos invocar nosotros mismos mediante la ejecución del comando CHECKPOINT situados en una base de datos en concreto. 

Checkpoints Internos

Los checkpoints internos se provocan porque una operación de SQL Server lo necesita. Entre estas operaciones que provocan un checkpoint interno podemos encontrar:

• Modificar los archivos de base de datos mediante ALTER DATABASE.
• Copias de seguridad.
• Instantáneas de base de datos.
• Parada de la base de datos automática porque Auto_Close está habilitado o porque un cambio de configuración requiere reiniciarla.
• Paradas del servicio de SQL Server

Conclusión

Como hemos visto, los checkpoints son clave para el rendimiento de nuestro servidor. Tenemos que tener claro qué tipos de checkpoint están haciendo nuestras bases de datos y probar distintas configuraciones para lograr el menor consumo de E/S y optimizar al máximo el rendimiento de nuestro servidor. Sin embargo, se trata de una configuración crítica, si tienes un servidor de pruebas te aconsejo empezar a cambiar en él y siempre modificando los valores de pocos en pocos segundos. 

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

En este artículo vamos a hablar de una de las novedades más interesantes que trajo SQL Server 2019: el Accelerated Database Recovery (ADR). Se trata de una característica que mejora el rendimiento y la disponibilidad de las bases de datos al reducir el tiempo de recuperación ante fallos o transacciones largas. ¿Quieres saber cómo funciona y cómo activarlo? ¡Sigue leyendo!

¿Qué es el Accelerated Database Recovery?

El Accelerated Database Recovery (ADR) es una funcionalidad que cambia la forma en que SQL Server gestiona el registro de transacciones y el proceso de recuperación. Con ADR, SQL Server utiliza una estructura de datos llamada Persistent Version Store (PVS) que almacena las versiones de las filas modificadas por las transacciones en curso. De esta forma, cuando se produce un rollback o un fallo del sistema, SQL Server no tiene que deshacer las modificaciones una por una, sino que simplemente recupera las versiones anteriores de las filas desde el PVS. Esto hace que el tiempo de recuperación sea mucho más rápido y predecible, independientemente del tamaño o la duración de las transacciones.

Además, el ADR también elimina el problema de los bloqueos por transacciones activas que consumen todo el espacio del log. Con ADR, SQL Server puede truncar el log sin esperar a que se completen las transacciones activas, ya que las versiones de las filas están en el PVS y no en el log. Esto evita que el log se llene y cause problemas de rendimiento o disponibilidad.

¿Qué ventajas tiene el Accelerated Database Recovery?

El Accelerated Database Recovery tiene varias ventajas para los administradores y desarrolladores de bases de datos:

• Reduce el tiempo de recuperación ante fallos o cancelaciones de transacciones. Con ADR, el tiempo de recuperación es constante y no depende del número o tamaño de las transacciones. Esto mejora la disponibilidad y la continuidad del negocio.
• Reduce el consumo de espacio del log. Con ADR, SQL Server puede truncar el log más frecuentemente y liberar espacio para nuevas transacciones. Esto reduce la necesidad de ampliar el log o hacer backups frecuentes.
• Reduce los bloqueos por transacciones activas. Con ADR, SQL Server no tiene que esperar a que se terminen las transacciones activas para truncar el log. Esto evita los bloqueos por falta de espacio en el log y mejora el rendimiento y la concurrencia.
• Facilita la gestión de transacciones largas o complejas. Con ADR, los desarrolladores pueden usar transacciones largas o complejas sin temor a que afecten al rendimiento o la disponibilidad de la base de datos. Esto permite implementar procesos de negocio más sofisticados y flexibles.

Una cosa más que debes saber sobre el ADR es que también afecta al funcionamiento de los checkpoints. Los checkpoints son procesos que escriben las páginas modificadas en memoria al disco para facilitar la recuperación. Con ADR, los checkpoints son más rápidos y eficientes, ya que solo escriben las páginas que han sido modificadas desde el último checkpoint y no dependen del estado de las transacciones. Esto también reduce la carga de E/S y mejora el rendimiento de la base de datos.

¿Cómo activar el Accelerated Database Recovery?

El Accelerated Database Recovery se activa a nivel de base de datos con el siguiente comando:

ALTER DATABASE <nombre_de_la_base_de_datos> SET ACCELERATED_DATABASE_RECOVERY = ON;

Es importante tener en cuenta que para activar el ADR, la base de datos debe estar en modo de compatibilidad 150 o superior (SQL Server 2019 o Azure SQL Database). Además, se recomienda tener suficiente espacio libre en el disco donde se almacena el PVS, ya que este puede crecer según la actividad de las transacciones. PVS se almacena en TempDB

Práctica

En la teoría todo es muy bonito, ahora vamos a verlo en la práctica a ver si es cierto. Para ello crearemos dos bases de datos, una con ADR activado y otra sin ello. Vamos a ejecutar sobre ellas unas consultas pesadas que se inicien justo a la vez y antes de que terminen vamos a forzar un apagado del servicio de SQL Server. Para terminar levantaremos de nuevo el servicio y veremos en el Log de SQL Server cuánto han tardado en estar disponibles:

Para crear las bases de datos he usado este script:

USE MASTER 
GO
CREATE DATABASE NOADR
GO
ALTER DATABASE NOADR SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO
CREATE DATABASE ADR
GO
ALTER DATABASE ADR SET ACCELERATED_DATABASE_RECOVERY = ON;

Lugo he ejecutado estos scripts en dos ventanas separadas (fijaos que se inician exactamente a la misma hora):

WAITFOR TIME '19:30:30';
USE ADR;
GO
DROP TABLE IF EXISTS dbo.t1, dbo.t2, dbo.t3;
SELECT o1.* INTO dbo.t1 FROM sys.all_columns c1 CROSS JOIN sys.all_objects o1
SELECT o2.* INTO dbo.t2 FROM sys.all_columns c2 CROSS JOIN sys.all_objects o2
SELECT o3.* INTO dbo.t3 FROM sys.all_columns c3 CROSS JOIN sys.all_objects o3

WAITFOR TIME '19:30:30';
USE NOADR;
GO
DROP TABLE IF EXISTS dbo.t1, dbo.t2, dbo.t3;
SELECT o1.* INTO dbo.t1 FROM sys.all_columns c1 CROSS JOIN sys.all_objects o1
SELECT o2.* INTO dbo.t2 FROM sys.all_columns c2 CROSS JOIN sys.all_objects o2
SELECT o3.* INTO dbo.t3 FROM sys.all_columns c3 CROSS JOIN sys.all_objects o3

En una tercera ventana he ejecutado este script para detener el servicio justo 5 minutos después de iniciar los anteriores:

WAITFOR TIME '19:35:30';
SHUTDOWN WITH NOWAIT;

Este que podéis ver ha sido el resultado, el rollback y por tanto la recuperación de la base de datos baja de 70 segundos sin ADR a 8 segundos con ADR activado: 

Esto creo que sobra decirlo pero, POR LO QUE MÁS QUERÁIS, NO HAGÁIS ESTA PRUEBA EN PRODUCCIÓN. Estamos probando a detener el servicio de SQL Server de manera forzada.

Conclusión

El Accelerated Database Recovery es una característica muy útil que mejora el rendimiento y la disponibilidad de las bases de datos en SQL Server 2019. Con ADR, se reduce el tiempo de recuperación ante fallos o cancelaciones de transacciones, se reduce el consumo de espacio del log, se reducen los bloqueos por transacciones activas y se facilita la gestión de transacciones largas o complejas. Si quieres aprovechar estas ventajas, te animamos a probar el ADR en tus bases de datos y a compartir tus experiencias con nosotros.

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

Entender la concurrencia de nuestros procesos es muy importante para los DBAs que queremos optimizar el rendimiento de nuestras bases de datos. En este sentido, muchas veces hemos nombrado en el blog los bloqueos. Sin embargo, en este artículo vamos a profundizar en el tema y vamos a tratar de llevar nuestros conocimientos sobre el tema al siguiente nivel.

 ¿Qué es un bloqueo y por qué se produce?

Un bloqueo es un mecanismo que SQL Server utiliza para garantizar la integridad de los datos cuando hay transacciones concurrentes que acceden a los mismos recursos. Un recurso puede ser una fila, una página, una tabla, una base de datos o cualquier otro objeto de SQL Server. Una transacción es una secuencia de operaciones que se ejecutan como una unidad lógica y que deben cumplir las propiedades ACID (atomicidad, consistencia, aislamiento y durabilidad). Cuando una transacción accede a un recurso, solicita un bloqueo sobre él, que puede ser de varios tipos y modos, dependiendo de la operación que realiza y del nivel de aislamiento que tiene.

Por ejemplo, una transacción que lee un recurso solicita un bloqueo compartido (S), que permite que otras transacciones también puedan leer el mismo recurso, pero no modificarlo. Una transacción que modifica un recurso solicita un bloqueo exclusivo (X), que impide que otras transacciones puedan acceder al mismo recurso, ya sea para leerlo o modificarlo.

Tipos de bloqueos

Acabamos de nombrar los bloqueos compartidos (S) y los exclusivos (X) pero, estos no son los únicos tipos de bloqueos en SQL Server. Además de los bloqueos compartidos (S) y los exclusivos (X) nos podemos encontrar con bloqueos de actualización (U) que, son parecidos a los exclusivos pero más flexibles. En estos casos si un recurso tiene un bloqueo compartido el bloqueo de actualización generará otro bloqueo compartido hasta que pueda generar el bloqueo exclusivo. 

Para terminar tenemos los llamados bloqueos de intención o propósito (I), estos se dan cuando SQL Server sabe que va a necesitar uno de los bloqueos anteriores. De esta manera, SQL se garantiza poder obtenerlo cuando lo necesite. Nos podemos encontrar con 3 tipos de bloqueos de intenciones generales: IS, IX o IU en función de si el bloqueo que va a necesitar es compartido, exclusivo o de actualización.
Para terminar con los bloqueos de intención tenemos los bloqueos de intención de conversión y también tenemos 3 tipos. El primero de ellos es el bloqueo SIX que se da cuando SQL va a necesitar un bloqueo compartido (S) sobre una serie de recursos que va a leer y posteriormente tiene intención (I) de generar un bloqueo exclusivo (X) sobre unos pocos de esos recursos que va a actualizar. Del mismo modo tenemos los bloqueos SIU si un bloqueo compartido (S) se va a convertir en uno de actualización (U) y los UIX cuando un bloqueo de actualización (U) se va a convertir en un bloqueo exclusivo (X).

Otros tipos de bloqueos

Por otro lado, SQL Server implementa también bloqueos a nivel de esquema Sch-M y Sch-S. El primero se da cuando ejecutamos una operación DDL que modifica el esquema y es exclusivo mientras que el segundo es compartido y lo veremos cuando una consulta dependa de un esquema. 

Para terminar tenemos los bloqueos BU de carga masiva que se producen al cargar una tabla con Bulk Insert y la opción TABLOCK. Estas cargas no permitirán el acceso a la tabla mientras estén en curso.

Jerarquía de los bloqueos.

Otro aspecto que debemos tener en cuenta es el nivel de granularidad de los bloqueos, es decir, el tamaño de las unidades de datos que se bloquean. SQL Server puede utilizar cuatro niveles de granularidad: base de datos, tabla, página o fila. El primero de ellos nos garantiza por ejemplo que una base de datos no se puede eliminar o restaurar mientras está en uso por alguna consulta. Cualquier operación sobre un objeto de base de datos genera un bloqueo compartido en la propia base de datos.

Los otros tres nos los podremos encontrar en nuestras consultas. El nivel de granularidad afecta al rendimiento y a la contención. Por un lado, un nivel más fino (fila) reduce la probabilidad de que dos transacciones accedan al mismo recurso, pero aumenta el número de bloqueos y el consumo de memoria. Por otro lado, un nivel más grueso (tabla) reduce el número de bloqueos y el consumo de memoria, pero aumenta la probabilidad de contención y tiempo de espera. SQL Server utiliza un algoritmo dinámico para elegir el nivel de granularidad más adecuado para cada caso, pero también podemos influir en esta decisión mediante las opciones de índice o las sugerencias de tabla.

Cómo funcionan los bloqueos

Si habéis llegado hasta este punto con el cerebro como un yogurt es normal. Esto no es fácil de entender, vamos a verlo con un ejemplo simple para entenderlo. Supongamos que en nuestra base de datos Animalitos tenemos un esquema MascotasAdorables y en ella una tabla Gatetes. Cuando nosotros queramos leer una fila de nuestra tabla, SQL Server generará un bloqueo compartido (S) sobre la fila de la tabla que vamos a leer, pero a la vez generará un intento de bloqueo compartido (IS) sobre la página que contiene esa fila y sobre la tabla MascotasAdorables y otro bloqueo (S) sobre la base de datos Animalitos.

Los dos bloqueos compartidos (S) están claros, como hemos dicho SQL siempre genera un bloqueo sobre la base de datos y al ser un select genera otro sobre la fila que estamos leyendo. Los bloqueos de intención compartidos (IS) nos garantizan que se podrán escalar a un bloqueo compartido (S) si alguien intenta borrar o modificar nuestra tabla o la página mientras estamos leyendo. 

Compatibilidad de los bloqueos

Como hemos visto, un recurso puede tener varios bloqueos compartidos simultáneos y sin embargo solo uno exclusivo (por eso sus nombres, tampoco se mataron pensando aquí). Ahora vamos a ver como de compatibles son los bloqueos entre ellos.

Deadlocks

Los deadlocks, en español interbloqueos o bloqueos permanentes, se producen cuando dos o más transacciones se bloquean mutuamente, impidiendo que ninguna de ellas pueda avanzar. 

Imagina que tenemos dos transacciones la A y la B. Nuestro proceso A ha leído una fila de la tabla gatetes y quiere escribirla en la tabla felinos. Es decir ha generado un bloqueo compartido (S) en la tabla gatetes y tiene que generar un bloqueo exclusivo (X) en la tabla felinos. Cuando va a generar este bloqueo se encuentra con que el proceso B ya ha leído de la tabla felinos y tiene sobre ella un bloqueo compartido (S) por lo que se tiene que esperar a que termine (según la tabla anterior S y X no son compatibles). Sin embargo el proceso B no va a terminar nunca porque está esperando a escribir en la tabla gatetes lo que ha leído. Esa escritura es un bloqueo compartido (X) que no puede hacerse porque ya hay un bloqueo compartido sobre esa tabla. 

SQL Server detecta estos escenarios y elige una de las transacciones como víctima, matándola y liberando los recursos que tenía bloqueados. Esto puede causar errores y pérdidas de datos en las aplicaciones que no están preparadas para manejar estos casos. 

¿Cómo podemos evitar o reducir los bloqueos e interbloqueos en SQL Server? 

Hay varias estrategias que podemos aplicar, dependiendo del contexto y el tipo de operaciones que realizamos. Algunas de estas estrategias son:

• Diseñar índices adecuados que permitan acceder a los datos con el menor número de lecturas y escrituras posibles.
• Utilizar transacciones cortas y simples que minimicen el tiempo que mantienen los bloqueos sobre los recursos.
• Elegir el nivel de aislamiento apropiado para cada transacción, teniendo en cuenta el equilibrio entre la consistencia de los datos y el rendimiento. En anteriores artículos hemos hablado de los diferentes niveles de aislamiento que ofrece SQL Server y cómo afectan a los bloqueos y a la visibilidad de los datos.
• Evitar el uso indiscriminado de la opción NOLOCK, que puede provocar lecturas sucias, perdidas o duplicadas. En este artículo explicamos los problemas del uso de NOLOCK.
• Monitorizar los bloqueos e interbloqueos que se producen en nuestras bases de datos, utilizando las herramientas y vistas que SQL Server nos proporciona. De esta forma podremos identificar las causas y las soluciones más adecuadas para cada caso.

¿Cómo podemos identificar los bloqueos e interbloqueos que se producen en nuestras bases de datos?

SQL Server nos ofrece varias herramientas y vistas para monitorizar y analizar estos fenómenos. Algunas de estas herramientas y vistas son:

• El Monitor de actividad, que nos permite ver en tiempo real las sesiones, procesos, bloqueos e interbloqueos que se están produciendo en nuestra instancia.
• La vista sys.dm_tran_locks, que nos muestra información detallada sobre los bloqueos actuales, como el tipo, el modo, el recurso, la sesión y la transacción asociados.
• La vista sys.dm_exec_requests, que nos muestra información sobre las solicitudes en ejecución, como el estado, el tiempo transcurrido, el tiempo esperado y la causa del tiempo de espera.
• La vista sys.dm_os_waiting_tasks, que nos muestra información sobre las tareas en espera, como la sesión, la solicitud, la duración y el tipo del tiempo de espera.
• El evento extendido deadlock graph, que nos muestra información sobre los interbloqueos ocurridos, como las transacciones involucradas, los recursos implicados y la víctima elegida.

Conclusión

Los bloqueos e interbloqueos son aspectos inevitables en cualquier sistema de gestión de bases de datos relacionales, pero no por ello debemos resignarnos a sufrir sus consecuencias negativas. Con un buen diseño, una correcta elección del nivel de aislamiento y una constante monitorización podremos mejorar el rendimiento y la fiabilidad de nuestras bases de datos. Esperamos que este artículo os haya sido útil y os animamos a compartir vuestras experiencias y dudas con nosotros en los comentarios.