TempDB SQL Server ¿Qué es y para qué sirve?

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!