¿Por qué tener varios archivos de Tempdb?

Publicaciones anteriores ya nos han mostrado la importancia de esta base de datos de sistema, particularmente cuando queremos mantener sus mejores prácticas. Y dentro de ellas seguro que hemos visto la recomendación de no mantener solamente un datafile como en una instalación por defecto. Aquí veremos la importancia de tener varios archivos de Tempdb.

Un poco de historia…solo un poco

Desde tiempo inmemoriales allá por SQL Server 2000 SP4, ya empezamos con la idea de la separación de archivos de tempdb y el uso del Trace Flag 1118.

Sí…ya hablábamos de contención en tempdb en ese tiempo. Es extraño pensar que fue hace casi 20 años ¿cierto?…Oh por Dios, ya me sentí viejo.

---

Microsoft Knowledge Base 328551 y KB 2154845

Como seguramente has visto, las referencias a las KB 328551 y KB 2154845 ya nos direccionan hacia el uso de diferentes archivos de data para la base tempdb. Y lo importante aquí es entender que el objetivo es claro: evitar la contención en esta base de datos.

Posteriormente en las mejoras de SQL Server a lo largo de sus versiones, las recomendaciones de habilitación de Trace Flags 1117 y 1118, se hicieron comportamientos por defecto en la instalación del motor.

Para habilitar los Trace Flags solamente basta con ejecutar lo siguiente.

DBCC TRACEON (1117, -1)

DBCC TRACEON (1118, -1)

Y para revisar cuáles son los Trace Flags habilitados en la instancia.

DBCC TRACESTATUS

---

¿Qué significan estos Trace Flags?

1117

• Permite el crecimiento de todos los archivos de un filegroup, todos creciendo en los mismos tamaños.
• Desde SQL Server 2016 (13.x) este comportamiento se controla con las configuraciones AUTOGROW_SINGLE_FILE y AUTOGROW_ALL_FILES dejando sin efecto al Trace Flag.

1118

• Nos ayuda a forzar la alocación de páginas en extents uniformes en lugar re extents mixtos. Esto reduce la contención en páginas SGAM. (Más detalle puede verse en los KB referenciados más arriba)
• Desde SQL Server 2016 (13.x) este comportamiento se controla con la configuración SET MIXED_PAGE_ALLOCATION dejando sin efecto al Trace Flag.

Si quieres ver otros Trace Flags puede verlo en documentación oficial.

---

Ahora sí la pregunta del millón: ¿Cuántos archivos de tempdb debo tener?

La regla es muy sencilla.

• 1 por cada procesador lógico. Máximo 8.

¿Dónde está lo importante? En que los tamaños de los archivos de datos sean iguales. Esto mejorará la distribución en el trabajo de Round Robin en la base. Es por eso todo lo que hablamos respecto a los Trace Flags en líneas más arriba. Necesitamos una distribución equitativa en los datafiles de la tempdb.

---

¿Cómo agregamos archivos?

Fácilmente en las propiedades de la base de datos.

O claro, si prefieres utilizar los scripts, solamente cambiando las rutas y tamaños.

USE [master]
GO
ALTER DATABASE [tempdb] 
ADD FILE ( NAME = N'tempdev2', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

---

Desde la Instalación, varios archivos de tempdb

Las nuevas versiones de SQL Server ya saben que tener diferentes archivos de tempdb se ha vuelto vital. Con esta intención, el equipo de Microsoft ha puesto a disposición durante la instalación del motor, la detección de la cantidad de cores en el servidor para mostrarte como sugerencia la misma cantidad de archivos para la base tempdb.

Ahora seguiremos las buenas prácticas desde un principio.

---

Últimos pensamientos sobre los varios archivos de tempdb

Si bien estas prácticas de adicionar varios archivos en tempdb vienen por recomendaciones de buenas prácticas e incluso están perfectamente documentadas por Microsoft, creo que es vital que puedas ser capaz de jsutificar su aplicación.

Esto no es magia. No vayas a tu servidor y esperes que tus consultas despeguen solo por hacer un cambio de este estilo. Verifica, valida.

Ya lo dijimos en una publicación anterior, la contención en tempdb se manifiesta también con tiempos de espera tipo PAGELATCH. Si los ves, este camino puede ayudarte a disminuir las esperas. ¿Aún las ves? Si tienes más de 8 procesadores podrías adicionar un archivo de tempdb por cada 4 procesadores lógicos extras que tengas.