tempdb [base de datos]

Artículo
03/20/2025

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance Base de datos de Azure SQL de Microsoft Fabric

En este artículo se describe la base de datos del sistema tempdb, un recurso global disponible para todos los usuarios conectados a una instancia del motor de base de datos en SQL Server, Azure SQL Database o Instancia administrada de Azure SQL.

Información general

La base de datos del sistema tempdb es un recurso global que contiene:

Los objetos de usuario que se crean explícitamente. Incluyen:
- Tablas e índices temporales globales o locales en estas tablas
- Procedimientos almacenados temporales
- Variables de tabla
- Tablas devueltas en funciones con valores de tabla
- Cursores
Los objetos de usuario que se pueden crear en una base de datos de usuario también se pueden crear en tempdb, pero se crean sin una garantía de durabilidad y se quitan cuando se reinicia la instancia del motor de base de datos.
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.
Almacenes de versiones, que son colecciones de páginas de datos que contienen las filas de datos que admiten el control de versiones de fila. Hay dos tipos: un almacén de versiones común y un almacén de versiones de compilación de índices en línea. Los almacenes de versión contienen:
- Versiones de fila generadas por transacciones de modificación de datos en una base de datos que usa transacciones de aislamiento basadas en versiones de fila READ COMMITTED o SNAPSHOT.
- Versiones de fila que se generan mediante transacciones de modificación de datos para características como las operaciones de índices en línea, los conjuntos de resultados activos múltiples (MARS) y los desencadenadores AFTER.

Las operaciones dentro de tempdb se registran mínimamente. tempdb se vuelve a crear cada vez que se inicia el motor de base de datos para que el sistema empiece siempre con una base de datos tempdb vacía. Los procedimientos almacenados temporales y las tablas temporales locales se quitan automáticamente cuando la sesión que las creó se desconecta.

tempdb nunca tiene nada que guardarse desde un período de tiempo de actividad del motor de base de datos a otro. 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.

Archivo	Nombre lógico	Nombre físico	Tamaño inicial	Crecimiento del archivo
Datos principales	`tempdev`	`tempdb.mdf`	8 megabytes	Crecimiento automático de 64 MB hasta llenar el disco.
Archivos de datos secundarios	`temp#`	`tempdb_mssql_#.ndf`	8 megabytes	Crecimiento automático de 64 MB hasta llenar el disco.
Log	`templog`	`templog.ldf`	8 megabytes	Crecimiento automático de 64 megabytes hasta un máximo de 2 terabytes

Todos los archivos de datos tempdb siempre deben tener los mismos parámetros de tamaño inicial y crecimiento.

Número de archivos de datos de tempdb

Según la versión del motor de base de datos, su configuración y la carga de trabajo, tempdb podría requerir varios archivos de datos para mitigar la contención de asignación.

El número total recomendado de archivos de datos depende del número de procesadores lógicos de la máquina. Como guía general:

Si el número de procesadores lógicos es menor o igual que ocho, use el mismo número de archivos de datos.
Si el número de procesadores lógicos es superior a ocho, utilice ocho archivos de datos.
Si todavía se observa contención en la asignación de tempdb, aumente el número de archivos de datos en múltiplos de cuatro hasta que la contención disminuya a niveles aceptables, o realice cambios en la carga de trabajo.

Para obtener más información, consulte las recomendaciones para reducir la contención de asignación en la base de datos tempdb de SQL Server.

Para comprobar el tamaño actual y los parámetros de crecimiento de tempdb, use la vista de catálogo de sys.database_files en tempdb.

Mover 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 .

Opción de base de datos	Valor predeterminado	Se puede modificar
`ACCELERATED_DATABASE_RECOVERY`	`OFF`	No
`ALLOW_SNAPSHOT_ISOLATION`	`OFF`	Sí
`ANSI_NULL_DEFAULT`	`OFF`	Sí
`ANSI_NULLS`	`OFF`	Sí
`ANSI_PADDING`	`OFF`	Sí
`ANSI_WARNINGS`	`OFF`	Sí
`ARITHABORT`	`OFF`	Sí
`AUTO_CLOSE`	`OFF`	No
`AUTO_CREATE_STATISTICS`	`ON`	Sí
`AUTO_SHRINK`	`OFF`	No
`AUTO_UPDATE_STATISTICS`	`ON`	Sí
`AUTO_UPDATE_STATISTICS_ASYNC`	`OFF`	Sí
`AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN)`	`OFF`	No
`CHANGE_TRACKING`	`OFF`	No
`COMPATIBILITY_LEVEL`	Depende de la versión del motor de base de datos. Para obtener más información, consulte Nivel de compatibilidad de ALTER DATABASE (Transact-SQL).	Sí
`CONCAT_NULL_YIELDS_NULL`	`OFF`	Sí
`CONTAINMENT`	`NONE`	No
`CURSOR_CLOSE_ON_COMMIT`	`OFF`	Sí
`CURSOR_DEFAULT`	`GLOBAL`	Sí
Estado de la base de datos	`ONLINE`	No
Actualización de la base de datos	`READ_WRITE`	No
Acceso de usuario de base de datos	`MULTI_USER`	No
`DATE_CORRELATION_OPTIMIZATION`	`OFF`	Sí
`DB_CHAINING`	`ON`	No
`DELAYED_DURABILITY`	`DISABLED` Independientemente de esta opción, la durabilidad diferida siempre está habilitada en `tempdb`.	Sí
`ENCRYPTION`	`OFF`	No
`MIXED_PAGE_ALLOCATION`	`OFF`	No
`NUMERIC_ROUNDABORT`	`OFF`	Sí
`PAGE_VERIFY`	`CHECKSUM` para nuevas instalaciones de SQL Server Es posible que el valor de `PAGE_VERIFY` existente se conserve cuando se actualice una instancia de SQL Server.	Sí
`PARAMETERIZATION`	`SIMPLE`	Sí
`QUOTED_IDENTIFIER`	`OFF`	Sí
`READ_COMMITTED_SNAPSHOT`	`OFF`	No
`RECOVERY`	`SIMPLE`	No
`RECURSIVE_TRIGGERS`	`OFF`	Sí
Intermediario de Servicios	`ENABLE_BROKER`	Sí
`TARGET_RECOVERY_TIME`	60	Sí
`TEMPORAL_HISTORY_RETENTION`	`ON`	Sí
`TRUSTWORTHY`	`OFF`	No

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

tempdb en Azure SQL Database

En Azure SQL Database, algunos aspectos del comportamiento y la configuración de tempdb son diferentes de SQL Server.

Para bases de datos únicas, cada base de datos de un servidor lógico tiene su propio tempdb. En un grupo elástico, tempdb es un recurso compartido para todas las bases de datos del mismo grupo, pero los objetos temporales creados por una base de datos no son visibles para otras bases de datos del mismo grupo elástico.

Los objetos de tempdb, incluidas las vistas de catálogo y las vistas de administración dinámica (DMV), son accesibles a través de una referencia entre bases de datos a la base de datos de tempdb. Por ejemplo, puede consultar la vista sys.database_files:

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Las tablas temporales globales de Azure SQL Database están limitadas al ámbito de la base de datos. Para obtener más información, vea Tablas temporales globales con ámbito de base de datos en base de datos de Azure SQL.

Para obtener más información sobre los tamaños de tempdb en Azure SQL Database, consulte:

Modelo de compra de núcleo virtual: bases de datos únicas, bases de datos agrupadas
Modelo de compra de DTU: bases de datos únicas, bases de datos agrupadas

tempdb en SQL Managed Instance

En Instancia administrada de Azure SQL, algunos aspectos del comportamiento de tempdb y la configuración predeterminada son diferentes de SQL Server.

Puede configurar el número de archivos tempdb, sus incrementos de crecimiento y su tamaño máximo. Para más información sobre cómo configurar las opciones tempdb en Azure SQL Managed Instance, consulte Configuración de tempdb para Azure SQL Managed Instance.

Azure SQL Managed Instance admite objetos temporales de la misma manera que SQL Server, donde todas las tablas temporales globales y los procedimientos almacenados temporales globales son accesibles para todas las sesiones de usuario dentro de la misma instancia administrada de SQL.

Para más información sobre los tamaños de tempdb en Azure SQL Managed Instance, revise los límites de recursos.

tempdb en la base de datos SQL en Fabric

Para obtener más información sobre los tamaños de tempdb en base de datos SQL de Microsoft Fabric, revise la sección límites de recursos de Comparación de características: base de datos de Azure SQL y base de datos SQL de Microsoft Fabric.

De forma similar a la base de datos de Azure SQL, las tablas temporales globales de la base de datos SQL de Microsoft Fabric tienen ámbito de base de datos. Para obtener más información, vea Tablas temporales globales con ámbito de base de datos en base de datos de Azure 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.
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 no temporales en tempdb, a menos que reciban permisos adicionales.

Es posible revocar el permiso CONNECT en tempdb para evitar que un usuario o rol de base de datos use tempdb. Esto no se recomienda porque muchas operaciones requieren el uso de tempdb.

Optimización del rendimiento de tempdb en SQL Server

El tamaño y la ubicación física de los archivos tempdb pueden afectar al rendimiento. Por ejemplo, si el tamaño inicial de tempdb es demasiado pequeño, el tiempo y los recursos pueden tardar en crecer automáticamente tempdb al tamaño necesario para admitir la carga de trabajo cada vez que se reinicia la instancia del motor de base de datos.

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.
- A partir de SQL Server 2022 (16.x), los eventos de crecimiento de archivos de registro de transacciones de hasta 64 MB también pueden beneficiarse de la inicialización instantánea de archivos. Para obtener más información, consulte inicialización instantánea de archivos y el registro de transacciones.
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 crezca automáticamente con demasiada frecuencia, lo que puede afectar negativamente al rendimiento.
Los archivos de la base de datos de tempdb deben establecerse en crecimiento automático para proporcionar espacio durante los eventos de crecimiento no planeados.
Dividir tempdb en varios archivos de datos de igual tamaño puede mejorar la eficacia de las operaciones que usan tempdb.
- Para evitar el desequilibrio de asignación de datos, los archivos de datos deben tener los mismos parámetros de tamaño inicial y crecimiento, ya que el motor de base de datos usa un algoritmo de relleno proporcional que favorece las asignaciones en archivos con más espacio libre.
- Establezca el incremento de crecimiento del archivo en un tamaño razonable, por ejemplo, 64 MB y haga que el incremento de crecimiento sea el mismo para todos los archivos de datos para evitar el desequilibrio de crecimiento.

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

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END
       AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0  THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1  THEN 'Percent'
       END
       AS growth_increment_value_unit
FROM tempdb.sys.database_files;

Coloque la base de datos tempdb en un subsistema de E/S rápido. No es necesario que los archivos de datos individuales o los archivos de datos de grupos de tempdb estén en discos diferentes, a menos que se estén produciendo cuellos de botella de E/S a nivel de disco.

Si hay contención de E/S entre tempdb y las bases de datos de los usuarios, coloque los archivos de tempdb en discos diferentes a los que usan dichas bases de datos.

Nota:

Para mejorar el rendimiento, la durabilidad diferida siempre está habilitada en tempdb incluso si la opción de base de datos DELAYED_DURABILITY está establecida en DISABLED. Dado que tempdb se vuelve a crear en el inicio, no pasa por un proceso de recuperación y no proporciona una garantía de durabilidad.

Mejoras de rendimiento de tempdb para SQL Server

Introducido en SQL Server 2016 (13.x)

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. El almacenamiento en caché 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.
La instalación de SQL agrega varios archivos de datos tempdb durante una instalación nueva de la instancia. Revise las recomendaciones y configure el tempdb en la página de configuración del motor de base de datos del programa de instalación de SQL o use el parámetro de línea de comandos /SQLTEMPDBFILECOUNT. De forma predeterminada, el programa de instalación de SQL agrega tantos archivos de datos 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. Para obtener más información, lea Cambios de -T1117 y -T1118 para TEMPDB y bases de datos de usuario.
Todas las asignaciones de tempdb usarán extensiones uniformes. La marca de seguimiento 1118 ya no es necesaria. Para obtener más información acerca de las mejoras de rendimiento en tempdb, consulte el artículo del blog TEMPDB - archivos, marcas de seguimiento y actualizaciones, ¡Dios mío!.
La propiedad AUTOGROW_ALL_FILES siempre está activada para el grupo de archivos PRIMARY.

Introducido en SQL Server 2017 (14.x)

La experiencia de instalación de SQL mejora las instrucciones para la asignación inicial de archivos tempdb. El programa de instalación de SQL advierte a los clientes si el tamaño de archivo inicial está establecido en un valor superior a 1 GB y si la inicialización instantánea de archivos no está habilitada, lo que impide que se produzcan retrasos en el inicio de la instancia.
La vista de administración dinámica sys.dm_tran_version_store_space_usage rastrea el uso del almacén de versiones por cada base de datos. Esta DMV es útil para los DBA que desean planear proactivamente el tamaño de tempdb, en función del requisito de uso del almacén de versiones para cada base de datos.
Las características de procesamiento inteligente de consultas, como combinaciones adaptativas y comentarios sobre la concesión de memoria, reducen los volcados de memoria en ejecuciones consecutivas de una consulta, reduciendo el uso de tempdb.

Introducido en SQL Server 2019 (15.x)

El motor de base de datos no usa la opción FILE_FLAG_WRITE_THROUGH al abrir archivos tempdb para permitir el rendimiento máximo del disco. Dado que tempdb se vuelve a crear al iniciarse, esta opción no es necesaria para proporcionar durabilidad de los datos. Para obtener más información sobre FILE_FLAG_WRITE_THROUGH, vea Registro y algoritmos de almacenamiento de datos que amplían la confiabilidad de los datos en SQL Server.
Los metadatos tempDB optimizados para memoria quita la contención de metadatos de objetos temporales en tempdb.
Las actualizaciones simultáneas de la página de espacio libre de páginas (PFS) reducen la contención de bloqueos temporales de página en todas las bases de datos, un problema que se suele ver en tempdb. Esta mejora cambia la administración de la simultaneidad con las actualizaciones de la página de PFS para que puedan actualizarse en un bloqueo temporal compartido, en lugar de un bloqueo temporal exclusivo. Este comportamiento está activado de forma predeterminada en todas las bases de datos (incluida tempdb) a partir de SQL Server 2019 (15.x). Para obtener más información sobre las páginas PFS, lea Detrás del telón: páginas GAM, SGAM y PFS.
De forma predeterminada, una nueva instalación de SQL Server en Linux crea varios archivos de datos tempdb en función del número de núcleos lógicos (con un máximo de ocho archivos de datos). Esto no se aplica a las actualizaciones de versión menores o mayores in situ. Cada archivo de datos tempdb es de 8 MB, con un crecimiento automático de 64 MB. Este comportamiento es similar a la instalación de SQL Server predeterminada en Windows.

Introducido en SQL Server 2022 (16.x)

Se introdujo una mejor escalabilidad con mejoras de simultaneidad de bloqueos temporales de página del sistema. La actualización simultánea de las páginas de mapa de asignación global (GAM) y de las páginas de mapa de asignación global compartidas (SGAM) reduce la contención de bloqueos temporales de páginas mientras se asignan o desasignan páginas de datos y extensiones. Estas mejoras se aplican a todas las bases de datos de usuario y benefician especialmente a las cargas de trabajo pesadas en tempdb. Para obtener más información sobre las páginas GAM y SGAM, lea Detrás del telón: páginas GAM, SGAM y PFS. Para obtener más información, vea Mejoras de simultaneidad de bloqueos temporales de la página del sistema (Ep. 6) | Datos expuestos.

Metadatos de TempDB optimizados para memoria

Históricamente, la contención de metadatos de objetos temporales ha sido un cuello de botella para la escalabilidad en muchas cargas de trabajo de SQL Server. Para solucionarlo, SQL Server 2019 (15.x) presentó una característica que forma parte de la familia de características de base de datos en memoria: metadatos TempDB optimizados para memoria.

Al habilitar la característica de metadatos TempDB optimizados para memoria, se elimina este cuello de botella en cargas de trabajo que antes estaban limitadas por la contención de metadatos de objetos temporales dentro de tempdb. A partir de SQL Server 2019 (15.x), las tablas del sistema implicadas en la administración de metadatos de objetos temporales pueden convertirse en tablas sin bloqueos temporales, no duraderas y optimizadas para memoria.

Sugerencia

Debido a las limitaciones actuales, recomendamos habilitar los metadatos de TempDB optimizados para memoria solo cuando se produzca contención de metadatos de objetos y esto afecte significativamente sus cargas de trabajo.

La siguiente consulta de diagnóstico devuelve una o varias filas si se está produciendo la contención de metadatos de objetos temporales. Cada fila representa una tabla del sistema y devuelve el número de sesiones que sostienen el acceso a esa tabla en el momento en que se ejecuta esta consulta de diagnóstico.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Vea este vídeo de siete minutos para obtener información general sobre cómo y cuándo usar la característica de metadatos tempDB optimizada para memoria:

Nota:

Actualmente, la característica de metadatos TempDB optimizada para memoria no está disponible en Azure SQL Database, SQL Database en Microsoft Fabric y Azure SQL Managed Instance.

Configuración y uso de metadatos de TempDB optimizados para memoria

En las secciones siguientes se incluyen los pasos para habilitar, configurar, comprobar y deshabilitar la característica de metadatos tempDB optimizados para memoria.

Habilitar

Para habilitar esta característica, use el siguiente script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Para obtener más información, vea ALTER SERVER. 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');

Si el valor devuelto es 1 y se ha producido un reinicio después de habilitar la característica, la característica está habilitada.

En caso de que se produzca un error al iniciar el servidor por algún motivo después de habilitar los metadatos TempDB optimizados para memoria, se puede omitir la característica si se inicia la instancia de motor de base de datos con una configuración mínima mediante la opción de inicio -f. A continuación, puede deshabilitar la función y quitar la opción -f para reiniciar el Motor de Base de Datos en modo normal.

Vincularse al grupo de recursos para limitar el uso de memoria

Para proteger el servidor frente a posibles condiciones de memoria insuficientes, se recomienda enlazar tempdb a un regulador de recursos grupo de recursos que limite la memoria consumida por los metadatos de TempDB optimizados para memoria. El siguiente script de ejemplo crea un grupo de recursos y establece su memoria máxima en 20%, habilita regulador de recursosy enlaza tempdb al grupo de recursos.

En este ejemplo se usan 20% como límite de memoria para fines de demostración. El valor óptimo en el entorno puede ser mayor o menor en función de la carga de trabajo y puede cambiar con el tiempo si cambia la carga de trabajo.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

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

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

Verificar el enlace del grupo de recursos y monitorear el uso de memoria

Para comprobar que tempdb está enlazado a un grupo de recursos y supervisar las estadísticas de uso de memoria del grupo, use la consulta siguiente:

WITH resource_pool AS
(
SELECT p.pool_id,
       p.name,
       p.max_memory_percent,
       dp.max_memory_kb,
       dp.target_memory_kb,
       dp.used_memory_kb,
       dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Eliminar enlace del grupo de recursos

Para quitar el enlace del grupo de recursos al mantener habilitados los metadatos de TempDB optimizados para memoria, ejecute el siguiente comando y reinicie el servicio:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Deshabilitar

Para deshabilitar los metadatos de TempDB optimizados para memoria, ejecute el siguiente comando y reinicie el servicio:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Limitaciones de los metadatos de TempDB optimizados para memoria

La habilitación o deshabilitación de la característica de metadatos tempDB optimizada para memoria requiere un reinicio.
En ciertos casos, podría observar un uso elevado de memoria causado por el distribuidor de memoria MEMORYCLERK_XTP, lo que provoca errores de memoria insuficiente en su carga de trabajo.

Para ver el uso de memoria por parte del controlador MEMORYCLERK_XTP en relación con todos los demás controladores de memoria y con respecto a la memoria del servidor objetivo, ejecute la siguiente consulta:
```
SELECT SUM(IIF(type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
       SUM(IIF(type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
       SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
FROM sys.dm_os_memory_clerks
CROSS JOIN sys.dm_os_sys_info;
```
Si la memoria MEMORYCLERK_XTP está elevada, puede mitigar el problema de la siguiente manera:
- Enlace la base de datos tempdb a un grupo de recursos que limita el consumo de memoria por metadatos de TempDB optimizados para memoria. Para obtener más información, consulte Configuración y uso de metadatos tempdb optimizados para memoria.
- Un procedimiento almacenado del sistema se puede ejecutar periódicamente para liberar MEMORYCLERK_XTP memoria que ya no es necesaria. Para obtener más información, consulte sys.sp_xtp_force_gc (Transact-SQL).
Para obtener más información, consulte los errores de falta de memoria de metadatos tempdb optimizados para memoria (HkTempDB).
Cuando se usa In-Memory OLTP, no se permite que una sola transacción acceda a tablas optimizadas para memoria en más de una base de datos. Debido a esto, cualquier transacción de lectura o escritura que implique una tabla optimizada para memoria en una base de datos de usuario tampoco puede acceder a tempdb vistas del sistema en la misma transacción. Si esto ocurre, recibirá el error 41317:
```
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.
```
Esta limitación también se aplica a otros escenarios en los que una única transacción intenta acceder a tablas optimizadas para memoria en más de una base de datos.

Por ejemplo, puede recibir el error 41317 si consulta la vista de catálogo sys.stats en una base de datos de usuario que contiene tablas optimizadas para la memoria. Esto sucede porque la consulta intenta acceder a los datos de estadísticas en una tabla optimizada para memoria en la base de datos de usuario y a los metadatos optimizados para memoria en tempdb.

El script de ejemplo siguiente genera este error cuando los metadatos tempDB optimizados para memoria están habilitados:
```
BEGIN TRAN;

-- Create an In-memory OLTP transaction that accesses a system view in tempdb
SELECT name
FROM tempdb.sys.tables;

-- An attempt to create an In-memory OLTP transaction in the user database fails
INSERT INTO <user database>.<schema>.<memory-optimized table>
VALUES (1);

COMMIT TRAN;
```
Nota:

Esta limitación no se aplica a las tablas temporales. Puede crear una tabla temporal en la misma transacción que accede a una tabla optimizada para memoria en una base de datos de usuario.
Las consultas sobre las vistas del catálogo del sistema siempre utilizan el nivel de aislamiento READ COMMITTED. Cuando se habilitan los metadatos TempDB optimizados para memoria, las consultas en las vistas de catálogo del sistema en tempdb usan el nivel de aislamiento de SNAPSHOT. En cualquier caso, no se respetan las sugerencias de bloqueo.
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.
- Como consecuencia, 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 están habilitados los metadatos de TempDB optimizados para memoria.

Planeamiento de capacidad para tempdb en SQL Server

Determinar el tamaño adecuado para tempdb depende de muchos factores. Estos factores incluyen la carga de trabajo y las características del motor de base de datos que se usan.

Se recomienda que analice el consumo de espacio de tempdb realizando las siguientes tareas en un entorno de prueba donde pueda reproducir su carga de trabajo típica.

Habilite el crecimiento automático para tempdb archivos. Todos los archivos de datos tempdb deben tener el mismo tamaño inicial y configuración de crecimiento automático.
Reproduzca la carga de trabajo y supervise el uso del espacio de tempdb.
Si utiliza mantenimiento de índice periódico, ejecute sus trabajos de mantenimiento y supervise el espacio de tempdb.
Use los valores de espacio máximo usados de los pasos anteriores para predecir el uso total de la carga de trabajo. 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

Quedarse sin espacio en disco en tempdb puede provocar interrupciones significativas y tiempo de inactividad de la aplicación. Puede usar la vista de administración dinámica sys.dm_db_file_space_usage para supervisar el espacio usado en los archivos de tempdb.

Por ejemplo, el siguiente script de ejemplo busca:

Espacio libre en tempdb (sin considerar el espacio libre en disco que podría estar disponible para el crecimiento de tempdb)
Espacio usado por el almacén de versiones
Espacio utilizado por objetos internos
Espacio utilizado por objetos de usuario

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_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 identificar consultas, tablas temporales o variables de tabla que usan grandes cantidades de espacio de tempdb.

Por ejemplo, use el siguiente script de ejemplo para obtener el espacio tempdb asignado y desasignado por objetos internos en todas las tareas que se están ejecutando actualmente en cada sesión:

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;

Utilice el siguiente script de ejemplo para buscar el espacio tempdb asignado y consumido actualmente por objetos internos y del usuario para cada sesión y solicitud, tanto para tareas en ejecución como completadas.

WITH tempdb_space_usage AS
(
SELECT session_id,
       request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
       NULL AS request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
       COALESCE(request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

Compartir a través de

tempdb [base de datos]

Información general

Propiedades físicas de tempdb en SQL Server

Número de archivos de datos de tempdb

Mover archivos de datos y registro de tempdb en SQL Server

Opciones de base de datos de tempdb en SQL Server

tempdb en Azure SQL Database

tempdb en SQL Managed Instance

tempdb en la base de datos SQL en Fabric

Restricciones

Permisos

Optimización del rendimiento de tempdb en SQL Server

Mejoras de rendimiento de tempdb para SQL Server

Introducido en SQL Server 2016 (13.x)

Introducido en SQL Server 2017 (14.x)

Introducido en SQL Server 2019 (15.x)

Introducido en SQL Server 2022 (16.x)

Metadatos de TempDB optimizados para memoria

Configuración y uso de metadatos de TempDB optimizados para memoria

Habilitar

Vincularse al grupo de recursos para limitar el uso de memoria

Verificar el enlace del grupo de recursos y monitorear el uso de memoria

Eliminar enlace del grupo de recursos

Deshabilitar

Limitaciones de los metadatos de TempDB optimizados para memoria

Planeamiento de capacidad para tempdb en SQL Server

Supervisión del uso de tempdb

Comentarios

Recursos adicionales

Compartir a través de

tempdb [base de datos]

Información general

Propiedades físicas de tempdb en SQL Server

Número de archivos de datos de tempdb

Mover archivos de datos y registro de tempdb en SQL Server

Opciones de base de datos de tempdb en SQL Server

tempdb en Azure SQL Database

tempdb en SQL Managed Instance

tempdb en la base de datos SQL en Fabric

Restricciones

Permisos

Optimización del rendimiento de tempdb en SQL Server

Mejoras de rendimiento de tempdb para SQL Server

Introducido en SQL Server 2016 (13.x)

Introducido en SQL Server 2017 (14.x)

Introducido en SQL Server 2019 (15.x)

Introducido en SQL Server 2022 (16.x)

Metadatos de TempDB optimizados para memoria

Configuración y uso de metadatos de TempDB optimizados para memoria

Habilitar

Vincularse al grupo de recursos para limitar el uso de memoria

Verificar el enlace del grupo de recursos y monitorear el uso de memoria

Eliminar enlace del grupo de recursos

Deshabilitar

Limitaciones de los metadatos de TempDB optimizados para memoria

Planeamiento de capacidad para tempdb en SQL Server

Supervisión del uso de tempdb

Contenido relacionado

Comentarios

Recursos adicionales