tempdb [base de datos]

Se aplica a: SQL Server (todas las versiones admitidas) Azure SQL Database

La base de datos del sistema tempdb es un recurso global disponible para todos los usuarios conectados a la instancia de SQL Server o a Azure SQL Database. tempdb contiene:

• Los objetos de usuario temporales que se hayan creado explícitamente. Incluyen tablas e índices temporales locales o globales, procedimientos almacenados temporales, variables de tabla, tablas devueltas en funciones con valores de tabla y cursores.

• Objetos internos que crea el motor de base de datos. Incluyen:

  • Tablas de trabajo para almacenar resultados intermedios para colas, cursores, ordenaciones y almacenamiento temporal de objetos grandes (LOB).
  • Archivos de trabajo para operaciones de combinación hash o de agregado hash.
  • Resultados de orden intermedio de operaciones como crear o volver a generar índices (si se ha especificado SORT_IN_TEMPDB), o algunas consultas GROUP BY, ORDER BY o UNION.

  Cada objeto interno usa un mínimo de nueve páginas: una página IAM y una extensión de ocho páginas. Para obtener más información acerca de las páginas y las extensiones, vea Páginas y extensiones.

  Importante

  Los grupos elásticos y las bases de datos únicas de Azure SQL Database admiten tablas temporales globales y procedimientos almacenados temporales globales que se almacenan en tempdb y que tienen como ámbito el nivel de base de datos.

  Las tablas temporales globales y los procedimientos almacenados temporales globales se comparten entre todos los usuarios en la misma base de datos SQL. Las sesiones de usuario de otras bases de datos no pueden acceder a tablas temporales globales. Para obtener más información, vea Database scoped global temporary tables (Azure SQL Database) (Tablas temporales globales con ámbito de base de datos [Azure SQL Database]). Azure SQL Managed Instance admite los mismos objetos temporales que SQL Server.

  En el caso de los grupos elásticos y las bases de datos únicas de Azure SQL Database, solo se aplican la base de datos maestra y la base de datos tempdb. Para obtener más información, vea la Qué es un servidor de Azure SQL Database. Para obtener una explicación de tempdb en el contexto de los grupos elásticos y las bases de datos únicas de Azure SQL Database, consulte Base de datos tempdb en SQL Database.

  En el caso de Azure SQL Managed Instance, se aplican todas las bases de datos del sistema.

• Almacenes de versiones, que son colecciones de páginas de datos que contienen las filas de datos que admiten las características para las versiones de fila. Hay dos tipos: un almacén de versiones común y otro de generación de índices en línea. Los almacenes de versión contienen:

  • Versiones de fila generadas por las transacciones de modificación de datos en una base de datos que utiliza READ COMMITTED a través de 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 de tempdb se registran de forma mínima, por lo que las transacciones se pueden revertir. tempdb se vuelve a crear cada vez que se inicia SQL Server, de forma que el sistema siempre se inicia 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.

tempdb nunca tiene nada que guardarse 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 SQL Server

En la tabla siguiente se muestran los valores iniciales de configuración de los archivos de datos y registro de tempdb en SQL Server. Los valores se basan en los valores predeterminados para la base de datos model. El tamaño de estos archivos puede variar ligeramente para diferentes ediciones de SQL Server.

El número de archivos de datos secundarios depende del número de procesadores (lógicos) de la máquina. Como regla general, si el número de procesadores lógicos es inferior o igual a ocho, use el mismo número de archivos de datos que procesadores lógicos. Si el número de procesadores lógicos es superior a ocho, utilice ocho archivos de datos. Después, si se mantiene la contención, aumente el número de archivos de datos en múltiplos de cuatro hasta que la contención se reduzca a niveles aceptables, o bien modifique el código o la carga de trabajo.

Mover los archivos de datos y registro de tempdb en SQL Server

Para mover los archivos de registro y de datos de tempdb, consulte Mover bases de datos del sistema.

Opciones de base de datos de tempdb en SQL Server

En la siguiente tabla se enumera el valor predeterminado de cada opción de base de datos en la base de datos tempdb y se indica si la opción se puede modificar. Para ver la configuración actual de estas opciones, utilice la vista de catálogo sys.databases .

Para obtener una descripción de estas opciones de la base de datos, vea Opciones de ALTER DATABASE SET (Transact-SQL).

Base de datos tempdb en SQL Database

tamaños de tempdb para los niveles de servicio basado en DTU

tamaños de tempdb para los niveles de servicio basado en núcleo virtual

Vea Límites de recursos basados en núcleos virtuales.

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.
• Cambiar el nombre de la base de datos o del grupo de archivos principal a 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. No se recomienda porque algunas operaciones rutinarias requieren el uso de tempdb.

Optimizar el rendimiento de tempdb en SQL Server

El tamaño y la ubicación física de la base de datos tempdb puede afectar al rendimiento de un sistema. Por ejemplo, si el tamaño definido para tempdb es demasiado pequeño, parte de la carga de procesamiento del sistema puede deberse al crecimiento automático de tempdb hasta alcanzar el tamaño necesario para admitir la carga de trabajo cada vez que se reinicie la instancia de SQL Server.

Si es posible, use la inicialización de archivo instantáneos para mejorar el rendimiento de las operaciones de crecimiento de los archivos de datos.

Asigne espacio previamente para todos los archivos de tempdb. Para ello, establezca el tamaño de archivo en un valor lo suficientemente alto para contener la carga de trabajo habitual del entorno. La asignación previa evita que tempdb se expanda con demasiada frecuencia, lo que afecta al rendimiento. La base de datos tempdb debe establecerse de modo que crezca automáticamente para aumentar el espacio en disco para las excepciones no previstas.

Los archivos de datos deberían ser del mismo tamaño dentro de cada grupo de archivos, ya que SQL Server utiliza un algoritmo de relleno proporcional que favorece las asignaciones en los archivos con más espacio libre. La división de tempdb en varios archivos de datos del mismo tamaño proporciona un alto grado de eficiencia paralela en las operaciones que usan tempdb.

Establezca el incremento de crecimiento de archivos en un tamaño razonable para evitar que los archivos de la base de datos tempdb crezcan en un porcentaje demasiado pequeño. Si el crecimiento de los archivos es demasiado pequeño comparado con la cantidad de datos que se escriben en tempdb, es posible que sea necesario expandir tempdb constantemente. Esto afectará al rendimiento.

Para comprobar los parámetros actuales de tamaño y de crecimiento de tempdb, use la consulta siguiente:

 SELECT name AS FileName,
    size*1.0/128 AS FileSizeInMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file grows to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

Coloque la base de datos tempdb en un subsistema de E/S rápido. Cree bandas en disco si hay muchos discos conectados directamente. No es necesario que los archivos de datos individuales o de grupos de tempdb estén en discos o ejes diferentes, a menos que también se estén produciendo cuellos de botella de E/S.

Coloque la base de datos tempdb en discos diferentes de los que usan las bases de datos de usuario.

Mejoras de rendimiento de tempdb para SQL Server

A partir de SQL Server 2016 (13.x), se optimiza el rendimiento de tempdb de las maneras siguientes:

• Las tablas temporales y las variables de tabla se almacenan en caché. El almacenamiento en caché permite que las operaciones que quitan y crean los objetos temporales se ejecuten muy rápidamente. También reduce la asignación de páginas y la contención de metadatos.
• El protocolo de bloqueo temporal de página de asignación se ha mejorado para reducir el número de bloqueos temporales UP (actualizaciones).
• Se reduce la sobrecarga del registro de tempdb para reducir el consumo de ancho de banda de E/S del disco en el archivo de registro de tempdb.
• El programa de instalación agrega varios archivos de datos tempdb durante una instalación nueva de la instancia. Esta tarea puede realizarse con el nuevo control de entrada de la IU en la sección Configuración del motor de base de datos y un parámetro de línea de comandos /SQLTEMPDBFILECOUNT. De manera predeterminada, la configuración agrega tantos archivos de datos de tempdb como el número de procesadores lógicos u ocho, lo que sea menor.
• Si hay varios archivos de datos tempdb, todos crecen automáticamente al mismo tiempo y la misma cantidad en función de la configuración de crecimiento. La marca de seguimiento 1117 ya no es necesaria.
• Todas las asignaciones de tempdb usarán extensiones uniformes. La marca de seguimiento 1118 ya no es necesaria.
• Para el grupo de archivos principal, la propiedad AUTOGROW_ALL_FILES se activa y la propiedad no se puede modificar.

Para obtener más información acerca de las mejoras de rendimiento en tempdb, consulte el artículo del blog TEMPDB: archivos y marcas de seguimiento y actualizaciones: ¡a por ello!.

Metadatos tempdb optimizados para memoria

La contención de metadatos en tempdb ha sido históricamente un cuello de botella en la escalabilidad para muchas cargas de trabajo que se ejecutan en SQL Server. SQL Server 2019 (15.x) presenta una nueva característica que forma parte de la familia de características de base de datos en memoria: metadatos de tempdb optimizados para memoria.

Esta característica elimina eficazmente este cuello de botella y desbloquea un nuevo nivel de escalabilidad para cargas de trabajo con cargas de trabajo pesadas de tempdb. En SQL Server 2019 (15.x), las tablas del sistema implicadas en la administración de metadatos de la tabla temporal del sistema se pueden mover a tablas optimizadas para memoria no duraderas y sin bloqueos temporales.

Actualmente, la característica de metadatos tempdb optimizada para memoria no está disponible en Azure SQL Database ni en Azure SQL Managed Instance.

Vea este vídeo de 7 minutos para obtener información general sobre cómo y cuándo usar los metadatos de tempdb optimizados para memoria:

Configuración y uso de metadatos de tempdb con optimización para memoria

Para poder participar en esta nueva característica, use el siguiente script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Para que este cambio de configuración surta efecto, es necesario reiniciar el servicio.

Puede comprobar si tempdb está optimizado para memoria mediante el siguiente comando de T-SQL:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

En caso de que se produzca un error al iniciar el servidor por algún motivo después de habilitar los metadatos de tempdb optimizados para memoria, se puede omitir la característica si se inicia la instancia de SQL Server con una configuración mínima mediante la opción de inicio -f. Después, puede deshabilitar la característica y reiniciar SQL Server en modo normal.

Para proteger el servidor de posibles condiciones de memoria insuficiente, puede enlazar tempdb a un grupo de recursos. Esto se realiza a través del comando ALTER SERVER en lugar de los pasos que normalmente seguiría para enlazar un grupo de recursos a una base de datos.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');

Este cambio también requiere que el reinicio surta efecto, incluso si los metadatos de tempdb optimizados para memoria ya están habilitados.

Limitaciones de tempdb optimizadas para memoria

• Activar o desactivar la característica no es una acción dinámica. Debido a los cambios intrínsecos que deben realizarse en la estructura de tempdb, es necesario llevar a cabo un reinicio para habilitar o deshabilitar la característica.

• Una única transacción no puede acceder a tablas optimizadas para memoria en más de una base de datos. Cualquier transacción que implique una tabla optimizada para memoria en una base de datos de usuario no podrá acceder a vistas del sistema tempdb en la misma transacción. Si intenta acceder a vistas del sistema tempdb en la misma transacción en forma de tabla optimizada para memoria en una base de datos de usuario, recibirá el error siguiente:

  A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
  

  Ejemplo:

  BEGIN TRAN;
  
  SELECT *
  FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb
  
  INSERT INTO <user database>.<schema>.<mem-optimized table>
  VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail
  
  COMMIT TRAN;
  

• Las consultas en tablas optimizadas para memoria no admiten las sugerencias de bloqueo y aislamiento, por lo que las consultas en vistas de catálogo tempdb optimizadas para memoria no respetarán dichas sugerencias. Como sucede con otras vistas de catálogo del sistema en SQL Server, todas las transacciones realizadas en vistas del sistema estarán en aislamiento READ COMMITTED (o, en este caso, READ COMMITTED SNAPSHOT).

• Los índices de almacén de columnas no se pueden crear en tablas temporales cuando los metadatos de tempdb optimizados para memoria están habilitados.

• Debido a la limitación en los índices de almacén de columnas, no se admite el uso del procedimiento almacenado del sistema sp_estimate_data_compression_savings con el parámetro de compresión de datos COLUMNSTORE o COLUMNSTORE_ARCHIVE cuando se habilitan los metadatos de tempdb optimizados para memoria.

Planeamiento de capacidad para tempdb en SQL Server

Determinar el tamaño adecuado para tempdb en un entorno de producción SQL Server depende de muchos factores. Como se ha descrito anteriormente, estos factores incluyen la carga de trabajo existente y las características de SQL Server que se usan. Se recomienda analizar la carga de trabajo existente llevando a cabo las siguientes tareas en un entorno de prueba de SQL Server:

• Active el crecimiento automático para tempdb.
• Ejecute consultas individuales o archivos de seguimiento de carga de trabajo y supervise el uso del espacio de tempdb.
• Ejecute operaciones de mantenimiento de índice, como volver a generar índices, y supervise el espacio de tempdb.
• Use los valores de uso de espacio de los pasos anteriores para predecir el uso de carga de trabajo total. Ajuste este valor para la actividad simultánea proyectada y, luego, establezca el tamaño de tempdb según corresponda.

Supervisión del uso de tempdb

La falta de espacio en disco en tempdb puede provocar interrupciones importantes en el entorno de producción de SQL Server. También puede impedir que las aplicaciones que se ejecutan completen las operaciones. Puede utilizar la vista de administración dinámica sys.dm_db_file_space_usage para supervisar el espacio en disco que utilizan los archivos de tempdb:

 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Para supervisar la actividad de asignación o desasignación de páginas en tempdb en la sesión o tarea, se pueden usar las vistas de administración dinámica sys.dm_db_session_space_usage y sys.dm_db_task_space_usage. Estas vistas pueden ayudarle a detectar consultas grandes, tablas temporales o variables de tabla que emplean mucho espacio de disco de tempdb. También puede usar varios contadores para supervisar el espacio disponible en tempdb y los recursos que usan tempdb.

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

Pasos siguientes

• Opción SORT_IN_TEMPDB para índices
• Bases de datos del sistema
• sys.databases
• sys.master_files
• Mover archivos de base de datos