Configuración Óptima de la Base de Datos TempDB en SQL Server

La base de datos TempDB en realidad debería de llamarse ImportanDB. En todos los años que tengo brindando consultorías de SQL este es uno de los problemas más recurrentes, la mala configuración de esta base de datos.

Recomendaciones

1. Separación de los archivos de datos de la base de datos TempDB en otro disco. Es muy importante para efectos de rendimiento y de monitoreo del tamaño de esta base de datos en un disco propio, separado del disco donde tenemos los archivos de datos de nuestras bases de datos o sistema operativo. Es importante para la separación de las cargas de trabajo. Tomando como base esta recomendación, es importante que puedas tener tus archivos de datos de las bases de datos propias en un disco, y otro disco para los log. Entonces vas a ocupar mínimo tres discos en producción 1. TempDB, 2. Archivos mdf o ndf, 3. Archivos log.

Si ya tienes tu servidor en producción y quieres mover la base de datos TempDB a otro disco, te adjunto el script para realizarlo:

USE master; 
GO 
ALTER DATABASE tempdb  
MODIFY FILE (NAME = tempdev, FILENAME = 'H:\Databases\DATA\tempdb.mdf'); 
GO 
ALTER DATABASE tempdb  
MODIFY FILE (NAME = templog, FILENAME = 'H:\Databases\LOG\templog.ldf'); 
GO

-- Después de esto es requerido reiniciar la base de datos

2. Tamaño iniciar y crecimiento automático. En una instalación inicial en versiones previas a la 2019, es probable que se creará una base de datos TempBD con un archivo inicial de 8MB y un archivos log de 1MB y con un crecimiento automático de 10%. Es importante recalcar que después del uso, la base de datos va a ir creciendo y en el momento que el servidor se reinicie por cualquier motivo, TempDB volverá al tamaño de la configuración base.

¿De qué tamaño tengo que configurar mi TempDB? Esta es una pregunta muy difícil, va a depender de la carga de tu servidor. Si tienes la opción de tener un disco propio para esta importante base de datos y tienes la posibilidad de asignarle bastantes gigas, entonces puedes indicarle que el tamaño original sea de un 90% de este disco, así te puedes asegurar que ningún otro archivo va a usar ese disco y no te vas a preocupar por el crecimiento automático. Si no puedes tener tu propio disco, vas a tener que administrar el tamaño. En este caso puedes dejar por un tiempo prudencial este crezca y así sabrás cuál va a ser el comportamiento que va a tener antes de asignarle un tamaño inicial y asignarle un crecimiento automático considerable, para que no este proceso no se ejecute de forma regular.

¿Cuál es el crecimiento automático recomendable? Si tienes un disco propio y pudiste separar el espacio, no vas a tener que configurar este parámetro ya que no se va a usar en el mejor de los casos. Puedes dejarlo configurado a una cantidad de megas considerable, pensando en el espacio en disco libre. Si no tienes el disco propio y compartes unidad con el sistema operativo, por ejemplo, entonces después de un tiempo prudencial viendo el comportamiento de esta base de datos, ya sabrás cuál valor para el crecimiento automático pueda tener, por ejemplo, si viste que todos los días la BD crece 5 megas, entonces puedes asignarle un valor de crecimiento de 50MB, con esto vas a asegurarte que no todos los días se ejecutará el autoincremento, no vas a tener un cuello de botella y así administrar mejor los recursos del servidor.

3. Tener varios archivos para la base de datos. Si tenemos la posibilidad de crear varios archivos. vamos a aumentar el rendimiento en E/S en nuestro servidor. SQL va a utilizar un algoritmo de llenado proporcional y con esto nos aseguramos que vamos a tener menos bloqueos y menos cuellos de botella, ya que si un archivo está haciendo algún proceso importante, SQL va a utilizar otro disponible.

¿Cuántos archivos? Antes se recomendaba que fuera un archivo por CPU, que aunque todavía es un buen comienzo ya no es necesario, ahora se recomienda que la configuración inicial comenzar con 8 archivos y conforme se va analizando el comportamiento añadir archivos adicionales si es necesario.