| Una buena práctica después de instalar SQL Server (y que interesa revisar periódicamente) es tener bien dimensionada la base de datos TEMPDB, es decir que el tamaño inicial de TEMPDB sea suficiente, y en consecuencia no sea necesario que TEMPDB crezca ni tampoco reducir TEMPDB (SHRINK). Esta artículo explica brevemente para qué sirve TEMPDB, explicacamo cambiar el tamaño inicial de TEMPDB(aumentar o reducir), cómo reducir TEMPDB, cuántos ficheros son recomendables para TEMPDB, etc. |
| ¿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 queel 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):
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:
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:
|
Comentarios
Publicar un comentario