¿Para qué sirve TEMPDB? La base de datos TEMPDB es un elemento de gran importancia en una Instancia de SQL Server, ya que TEMPDB es la encargada de almacenar tanto los objetos temporales (tablas temporales, procedimientos almacenados temporales, etc.), como los resultados intermedios que pueda necesitar crear el motor de base de datos, por ejemplo durante la ejecución de consultas que utilizan las cláusulas GROUP BY, ORDER BY, DISTINCT, etc. (es decir, las tablas temporales o WorkTables que se crean al ejecutar consultas complejas). Además, en TEMPDB también es posible crear objetos de usuario, aunque estos no podrán ser persistentes después de un reinicio de la Instancia de SQL Server. De hecho, la creación de tablas de usuario en TEMPDB es una buena práctica, debido a que en TEMPDB sólo se graba en el LOG la información suficiente para deshacer la transacción (y no para rehacerla), lo cual aumenta el rendimiento de operaciones INSERT, UPDATE, etc. Por todo esto, el rendimiento de TEMPDB hace eco en el rendimiento global de la Instancia de SQL Server.
Una buena práctica a realizar inmediatamente después de la instalación, es dimensionar de forma apropiada la base de datos TEMPDB, es decir, cambiar el tamaño por defecto de TEMPDB (el tamaño inicial de TEMPDB) de tal modo que durante el funcionamiento de la Instancia de SQL Server, no sea necesario que TEMPDB tenga que crecer y no necesario reducir TEMPDB. El tamaño por defecto de la base de datos TEMPDB es de 8MB, con un crecimiento automático del 10%. Muchas Instancias de SQL Server requieren de un gran tamaño de TEMPDB debido a su actividad diaria (ej: ejecución de consutas, operaciones de mantenimiento, etc.). Por ello, si no tenemos dimensionada correctamente TEMPDB, se verá obligada a crecer. Pero además, no va a crecer de un único salto. Es decir, si necesitamos una TEMPDB de 20GB, crecerá desde sus 8MB de 10% en 10% hasta alcanzar los 20GB, lo cual tiene un doble impacto: Por un lado, el fichero de TEMPDB potencialmente estará muy fragmentado impactando seriamente en el rendimiento general de toda la Instancia SQL Server, y para colmo, debido a que cada vez que se inicia la Instancia de SQL Server se elimina y vuelve a crear TEMPDB, existe el riesgo de generar fragmentación en el disco utilizado por TEMPDB (es decir, que además de TEMPDB puedan encontrarse fragmentado el disco, en general). No sólo eso, sino que además, cada vez que una operación realizada por la Instancia de SQL Server requiere que TEMPDB crezca para que se pueda realizar, se produce una latencia producida por dicha operación de crecimiento del fichero de TEMPDB (las operaciones de crecimiento y reducción de ficheros de base de datos, son bastante costosas).
¿Cómo dimensionar la base de datos TEMPDB? ¿Qué tamaño inicial de TEMPDB me interesa? Suelo seguir y recomendar dos criterios: Por un lado, siempre darle un tamaño mínimo, aunque sólo sean 200MB o 1GB. Partiendo de aquí, la experiencia que tengamos con nuestra Instancia de SQL Server será la que nos pueda permitir ajustar mejor el tamaño de TEMPDB, tanto por el tamaño que observemos que TEMPDB utiliza con el paso del tiempo, como por la experiencia que podamos tener sobre cuáles de nuestros procesos llenan TEMPDB, cuanto tamaño requieren y con qué periodicidad se ejecutan. Sobre todo, debemos tener en cuenta que el objetivo deseado es que el tamaño inicial de TEMPDB sea suficiente para que TEMPDB no necesite crecer.
¿Cómo aumentar el tamaño inicial de TEMPDB? Una vez decidido el tamaño inicial de TEMPDB que deseamos, para cambiar el tamaño inicial de TEMPDB tan sólo necesitaremos ejecutar un comando ALTER DATABASE tempdb MODIFY FILE como en el siguiente ejemplo (suponiendo que TEMPDB tiene un único fichero de datos):
USE master
GO
-- *** Cambiar el Tamaño inicial de TEMPDB a 200MB ***
ALTER DATABASE tempdb
MODIFY FILE ( NAME = N'tempdev', SIZE = 204800KB )
GO
|
Tan pronto ejecutemos el comando ALTER DATABASE tempdb MODIFY FILE, el cambio tomará efecto (no es necesario reiniciar la Instancia de SQL Server, etc.).
Sin embargo, siendo puristas, en entornos críticos además de cambiar el tamaño inicial de TEMPDB es muy recomendable aumentar el número de ficheros de TEMPDB, a poder ser un fichero por cada CPU (es decir, por cada Core), con el objetivo de maximizar la afinidad de CPU, esto es facilitar que puedan paralelizarse operaciones de entrada/salida (IOs) y así obtener una mejora de rendimiento. Claro está, que si cada fichero pudiese estar en un disco diferente, y cada uno de estos discos se accediese a través de un camino (path) de fibra distinto (es decir, diferentes puertos de fibra de las HBAs), estaríamos facilitando al máximo la optimización del acceso a disco de TEMPDB. Así, para añadir ficheros a TEMPDB es suficiente con ejecutar un comando ALTER DATABASE tempdb ADD FILE, como se muestra en el siguiente ejemplo:
USE master
GO
-- *** Añadir un fichero a TEMPDB con un Tamaño inicial de 200MB ***
ALTER DATABASE tempdb
ADD FILE ( NAME = N'tempdev02', FILENAME = N'F:\DATA\tempdb02.ndf', SIZE = 204800KB, FILEGROWTH = 10% )
GO
|
Llegados a este punto, podemos observar que si tenemos una máquina con 16 CPUs y necesitamos 16GB de TEMPDB, la recomendación que deberíamos seguir es configurar TEMPDB con 16 ficheros de datos, cada uno de ellos con un tamaño inicial de 1GB. La cosa empieza a complicarse con máquinas que tenga muchas CPUs (ej: un servidor con 48 CPUs: ¿cuantos ficheros le ponemos a TEMPDB?), en cuyo caso puede utilizarse una proporción de 1:4 ó 1:8, por tomar una referencia inicial.
A todo esto, por tener una referencia más oficial, se puede consultar la KB2154845 de Microsoft: Recommendations to reduce allocation contention in SQL Server tempdb database
Si TEMPDB no está correctamente dimensionado, puede ocurrir que empiece a crecer incluso tomando todo el espacio libre del disco, en cuyo caso puede que nos interese reducir TEMPDB. ¿Cómo podemos reducir TEMPDB?
Si TEMPDB ha aumentado de tamaño desde que se inició la Instancia de SQL Server, la forma más sencilla de recuperar el espacio de más utilizado por TEMPDB es reiniciar la Instancia de SQL Server. De este modo, al detener SQL Server se mantendrán los ficheros de TEMPDB, por lo que todavía no habremos ganado espacio libre en disco. Sin embargo, al iniciar la Instancia de SQL Server, se elimina TEMPDB y se vuelve a crear de nuevo (como copia de MODEL, pero con el tamaño inicial de TEMPDB), recuperando de éste modo, el espacio libre que estaba ocupando de más. El principal inconveniente, es debido a que implica cortar el servicio a los usuarios.
También es posible intentar utilizar los comandos DBCC SHRINKDATABASE y DBCC SHRINKFILE, pero ejecutar estos comandos sobre TEMPDB con actividad en TEMPDB puede implicar que no se pueda reducir TEMPDB (porque existan páginas al final de los ficheros de TEMPDB que estén asignadas o objetos temporales, etc.) o bien pueden producirse errores que tampoco permitan reducir TEMPDB con éxito, como se muestra en el Artículo de Microsoft How to shrink the tempdb database in SQL Server. Además, mientras se está reduciendo (SHRINK) TEMPDB es posible que se generen bloqueos sobre otras transacciones que necesiten acceder a TEMPDB.
Dicho de otro modo: Debemos dimensionar TEMPDB (en la medida de lo posible), para que durante el funcionamiento de la Instancia de SQL Server no sea necesario ni que TEMPDB tenga que crecer, ni que sea necesario reducir TEMPDB.
En cualquier caso, también es cierto que en SQL Server 2005 existe un truco. Desde SQL Server 2005, aquellas consultas cuya ejecución necesite la creación de tablas de trabajo (WorkTables), implicarán que tras su ejecución, el Plan de Ejecución permanecerá en memoria para su reutilización, pero además, las propias tablas de trabajo (Worktable) permanecerán en TEMPDB después de la ejecución de la consulta para ser reutilizadas (junto al Plan de Ejecución), impidiendo en ocasiones la reducción de TEMPDB. Entonces, si tenemos las tablas de trabajo (que pueden estar ocupando páginas de TEMPDB, por ejemplo, al final del fichero, impidiendo su reducción) ¿Cómo podemos reducir TEMPDB en SQL Server 2005? Bien, pues existe el truco de ejecutar el comando DBCC FREEPROCCACHE, de tal modo que al vaciarse la caché de procedimientos (la zona de memoria en la que se almacenan los Planes de Ejecución para su reutilización) estamos eliminando de forma implícita las Tablas de Trabajo (Worktables) asociadas a dichos Planes de Ejecución. Esto lo he probado, en un cliente que NO podía reducir TEMPDB (aún tenien un montón de espacio sin utilizar), y después de la ejecución de DBCC FREEPROCCACHE, voalá !! Se consiguió reducir TEMPDB (no del todo, pero si bastantes Gigas, lo suficiente para evitar tener que reiniciar la instancia de SQL Server 2005). Tengo entendido que, de forma similar, también puede resultar de utilidad ejecutar el comando DBCC FREESYSTEMCACHE para poder liberar espacio en TEMPDB, aunque aún no he tenido oportunidad de probarlo en un entorno de producción.
El último caso que nos puede interesar conocer, es el tener sobreconfigurado el tamaño inicial de TEMPDB, es decir, tener TEMPDB con un tamaño demasiado grande, quizás porque en algún momento era necesario, y después de algún cambio en alguna aplicación que consumiese mucho TEMPDB, nos encontrásemos con un TEMPDB demasiado grande. En ese caso ¿Cómo reducir el tamaño inicial de TEMPDB? Existen varias alternativas, como se describe en el Artículo de Microsoft How to shrink the tempdb database in SQL Server:
- Iniciar la Instancia de SQL Server en el modo de configuración mínima (sqlservr -c -f), que implica una base de datos TEMPDB con una ficheros diminutos, y entonces, ejecutar comandos ALTER DATABASE tempdb MODIFY FILE para establecer el o los tamaños deseados para el o los ficheros de TEMPDB.
- Utilizar los comandos DBCC SHRINKDATABASE y DBCC SHRINKFILE. Como se comenta un poco antes en este artículo, puede haber problemas si se ejecutan con actividad en la base de datos, por lo que puede ser necesario ejecutar dichos comandos después de iniciar la Instancia de SQL Server en modo de usuario único (sqlservr -m). Para mayor detalle entre las diferencias entre DBCC SHRINKDATABASE y DBCC SHRINKFILE e información sobre cómo reducir bases de datos, puede consultarse el artículo ¿Cómo se puede reducir una base de datos?
Por último, aprovecho para recomendar la lectura del artículo de Microsoft TechNet Working with tempdb in SQL Server 2005. Un poco largo, pero de gran interés técnico.
