SQL Server

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.