Checkpoints en SQL Server

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.