Mejores practicas de tempdb

La tempdb es una de las 5 bases de sistema existentes (master, model, msdb, tempdb, resource), y la única que se puede beneficiar de ser configurada de manera distinta a como esta por defecto, aunque la model también esto sería un tema distinto, dependiendo de cada uno de nosotros y los ambientes que manejamos.

Actualizado: 04/22/2016

Una de las clásicas recomendaciones que verán en todo lugar es separar las bases de datos en tres apartados básicamente.

A) Los archivos de datos (.mdf y .ndf) en un disco o unidad lógica.

B) Los archivos de log (.ldf) en un disco o unidad lógica.

C) La tempdb (tanto su .mdf, .ndf y .ldf) en un disco o unidad lógica.

En un inicio esto era debido a la cantidad de spindles o discos físicos y velocidad de acceso que se tenía, ahora con el uso más común de las SAN es un poco difícil el poder detectar cuellos de botella así como controlar de recursos para los administradores de la base de datos y esto queda relegado al administrador de sistema o de storage. Si se tiene una SAN es importante resaltar que los spindles pierden gran parte de su importancia.

Otro punto importante para todas las bases de datos, y no solo para la tempdb es su tamaño inicial, y su crecimiento ya que la recomendación es que las bases de datos no crezcan automáticamente, sino que estas puedan contener todos los datos en ellas, y que no crezcan de manera automática pues esta es una operación  bastante costosa de realizar y que puede tener un impacto negativo en el desempeño del servidor.

El tamaño inicial de la tempdb es de 8mb y de su log de 1mb con un crecimiento ilimitado de 10% cada vez que sea necesario. Si consideramos que cada vez que reiniciamos la instancia o hace failover en el caso de un clúster la tempdb se recrea con estos valores, podemos suponer que cuando llegue una operación que ocupe demasiado de ella crecerá para poder alojarla, si tenemos estos valores y tenemos como ejemplo una operación que pesa 1gb tendrá que crecer 51 veces para poder realizar esto en el data file. Si consideramos que intentara escribir, verificar que no existe el espacio, crecer, reintentar y el ciclo se repite las veces necesarias, será lentitud que el usuario percibirá como lentitud del sistema.

No existe un valor fácil de determinar del nivel inicial, eso dependerá de la carga de cada servidor y el espacio disponible, el crecimiento de la base será inevitable en la mayoría de los casos, pues aun las predicciones pueden llegar a fallar, por lo tanto el crecimiento también debe de ser controlado, es recomendado que el crecimiento sea determinado en un valor fijo y no en un porcentaje.

La ultima recomendación es crear suficientes data files como procesadores lógicos se tengan (con un máximo de 8), recuerden que para que SQL los maneje correctamente deberán de tener el mismo tamaño inicial y crecimiento.

Recomendaciones:

• Usa el disco más rapido para la tempdb
• Haz una cantidad igual al número de procesadores con un máximo de 8, en caso de aun tener contención agregar 2 más hasta el número máximo de procesadores
• Tener un tamaño inicial que de preferencia impida que la tempdb incremente
• Hacer los crecimientos fijos o sea no basados en porcentaje sino una cantidad como 4Gb
• El uso de las banderas (1117, 1118), la 1117 hace que todos los archivos en un grupo de archivos crezcan al mismo tiempo, la 1118 hace que no existan extends mixtos, para mas información vean al final, estas banderas estaran activadas por defecto en SQL Server 2016

Más información:

Think Sindles, Not Space!

Trace flag 1117:To grow all files on a filegroup at the same time

Trace flag 1118:Tempdb, no mixed extends