Base de datos tempdb
La base de datos del sistema tempdb es un recurso global que está disponible para todos los usuarios conectados a la instancia de SQL Server y se usa para contener lo siguiente:
Objetos de usuario temporales creados explícitamente como: tablas temporales locales o globales, procedimientos almacenados temporales, variables de tabla o cursores.
Objetos internos creados por el motor de base de datos de SQL Server, por ejemplo, tablas de trabajo para almacenar resultados intermedios para colas o ordenación.
Versiones de fila generadas por las transacciones de modificación de datos en una base de datos que utiliza transacciones de lectura confirmada que usan transacciones de aislamiento de versiones de fila o de aislamiento de instantáneas.
Versiones de fila que se generan mediante transacciones de modificación de datos para características como operaciones de índice en línea, conjuntos de resultados activos múltiples (MARS) y desencadenadores AFTER.
Las operaciones realizadas en tempdb se registran con un nivel mínimo. Esto habilita la reversión de las transacciones. tempdb se vuelve a crear cada vez que se inicia SQL Server para que el sistema siempre comience con una copia limpia de la base de datos. Las tablas y los procedimientos almacenados temporales se quitan automáticamente en la desconexión y ninguna conexión permanece activa cuando se cierra el sistema. Por lo tanto, nunca hay nada en tempdb que se va a guardar de una sesión de SQL Server a otra. No se permite realizar operaciones de copia de seguridad y restauración en tempdb.
Propiedades físicas de tempdb
En la tabla siguiente se muestran los valores iniciales de configuración de los archivos de datos y registro de tempdb . El tamaño de estos archivos puede variar ligeramente para diferentes ediciones de SQL Server.
| Archivo | Nombre lógico | Nombre físico | Crecimiento del archivo |
|---|---|---|---|
| Datos principales | tempdev | tempdb.mdf | Crecimiento automático del 10 por ciento hasta llenar el disco |
| Log | templog | templog.ldf | Crecimiento automático del 10 por ciento hasta un máximo de 2 terabytes |
El tamaño de tempdb puede afectar al rendimiento de un sistema. Por ejemplo, si el tamaño de tempdb es demasiado pequeño, el procesamiento del sistema podría estar demasiado ocupado con el crecimiento automático de la base de datos para admitir el requisito de carga de trabajo cada vez que se inicia SQL Server. Puede evitar esta sobrecarga aumentando el tamaño de tempdb.
Mejoras en el rendimiento de tempdb
En SQL Server, el rendimiento de tempdb se mejora de las siguientes maneras:
Las tablas temporales y las variables de tabla se pueden almacenar en caché. El almacenamiento en caché permite que las operaciones que quitan y crean los objetos temporales se ejecuten muy rápidamente y reduce la contención de la asignación de páginas
Se mejora el protocolo de bloqueo temporal de página de asignación. Esto reduce el número de bloqueos temporales UP (de actualización) utilizados.
Se reduce la sobrecarga de registro para tempdb . Esto reduce el consumo de ancho de banda de E/S del disco en el archivo de registro de tempdb .
Se ha mejorado el algoritmo para asignar páginas mixtas en tempdb .
Mover los archivos de datos y registro de tempdb
Para mover los archivos de registro y datos de tempdb , consulte Mover bases de datos del sistema.
Opciones de base de datos
En la tabla siguiente se muestra el valor predeterminado para cada opción de base de datos de la base de datos tempdb y si la opción se puede modificar. Para ver la configuración actual de estas opciones, utilice la vista de catálogo sys.databases .
| Opción de base de datos | Valor predeterminado | Se puede modificar |
|---|---|---|
| ALLOW_SNAPSHOT_ISOLATION | Apagado | Sí |
| ANSI_NULL_DEFAULT | Apagado | Sí |
| ANSI_NULLS | Apagado | Sí |
| ANSI_PADDING | Apagado | Sí |
| ANSI_WARNINGS | Apagado | Sí |
| ARITHABORT | Apagado | Sí |
| AUTO_CLOSE | Apagado | No |
| AUTO_CREATE_STATISTICS | ACTIVAR | Sí |
| AUTO_SHRINK | Apagado | No |
| AUTO_UPDATE_STATISTICS | ACTIVAR | Sí |
| AUTO_UPDATE_STATISTICS_ASYNC | Apagado | Sí |
| CHANGE_TRACKING | Apagado | No |
| CONCAT_NULL_YIELDS_NULL | Apagado | Sí |
| CURSOR_CLOSE_ON_COMMIT | Apagado | Sí |
| CURSOR_DEFAULT | GLOBAL | Sí |
| Opciones de disponibilidad de la base de datos | ONLINE MULTI_USER READ_WRITE |
No No No |
| DATE_CORRELATION_OPTIMIZATION | Apagado | Sí |
| DB_CHAINING | ACTIVAR | No |
| ENCRYPTION | Apagado | No |
| NUMERIC_ROUNDABORT | Apagado | Sí |
| PAGE_VERIFY | CHECKSUM para las nuevas instalaciones de SQL Server. NONE para las actualizaciones de SQL Server. |
Sí |
| PARAMETERIZATION | SIMPLE | Sí |
| QUOTED_IDENTIFIER | Apagado | Sí |
| READ_COMMITTED_SNAPSHOT | Apagado | No |
| RECOVERY | SIMPLE | No |
| RECURSIVE_TRIGGERS | Apagado | Sí |
| Opciones de Service Broker | ENABLE_BROKER | Sí |
| TRUSTWORTHY | Apagado | No |
Para obtener una descripción de estas opciones de la base de datos, vea Opciones de ALTER DATABASE SET (Transact-SQL).
Restricciones
Las siguientes operaciones no se pueden realizar en la base de datos tempdb :
Agregar grupos de archivos.
Realizar una copia de seguridad o restaurar la base de datos.
Cambiar intercalaciones. La intercalación predeterminada es la intercalación de servidor.
Cambiar el propietario de la base de datos. tempdb es propiedad de sa.
Crear una instantánea de base de datos.
Eliminar la base de datos.
Eliminar el usuario guest de la base de datos.
Habilitar el mecanismo de captura de cambios en los datos.
Participar en el reflejo de la base de datos.
Quitar el grupo de archivos principal, el archivo de datos principal o el archivo de registro.
Cambiar el nombre de la base de datos o del grupo de archivos principal.
Ejecutar DBCC CHECKALLOC.
Ejecutar DBCC CHECKCATALOG.
Establecer la base de datos en OFFLINE.
Establecer la base de datos o el grupo de archivos principal en READ_ONLY.
Permisos
Cualquier usuario puede crear objetos temporales en tempdb. Los usuarios solo pueden acceder a sus propios objetos, a menos que reciban permisos adicionales. Es posible revocar el permiso de conexión a tempdb para impedir que un usuario use tempdb, pero no es una práctica recomendada ya que algunas operaciones rutinarias necesitan el uso de tempdb.
Contenido relacionado
Opción SORT_IN_TEMPDB para índices
sys.master_files (Transact-SQL)
Mover archivos de base de datos