GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

SQL Server FAQ: Tamaño inicial de TEMPDB ¿Cómo aumentar TEMPDB? ¿Cómo reducir TEMPDB?

Volver a: [SQL Server FAQ :: Preguntas y Respuestas Frecuentes de SQL Server :: Manual SQL Server]


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, explica camo 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 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.

Volver a: [SQL Server FAQ :: Preguntas y Respuestas Frecuentes de SQL Server :: Manual SQL Server]


[Fecha del Artículo (UTC): 09/01/2008]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Junio de 2017 (3)
Mayo de 2017 (1)
Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas