tempdb [base de datos]
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 consultasGROUP BY
,ORDER BY
oUNION
.
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
oSNAPSHOT
. - 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
.
- 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
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 ServerEs 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 quetempdb
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 usantempdb
.- 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 detempdb
. - La instalación de SQL agrega varios archivos de datos
tempdb
durante una instalación nueva de la instancia. Revise las recomendaciones y configure eltempdb
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 datostempdb
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 entempdb
, 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 archivosPRIMARY
.
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 archivostempdb
para permitir el rendimiento máximo del disco. Dado quetempdb
se vuelve a crear al iniciarse, esta opción no es necesaria para proporcionar durabilidad de los datos. Para obtener más información sobreFILE_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 (incluidatempdb
) 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 datostempdb
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).
- Enlace la base de datos
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 entempdb
usan el nivel de aislamiento deSNAPSHOT
. 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 datosCOLUMNSTORE
oCOLUMNSTORE_ARCHIVE
cuando están habilitados los metadatos de TempDB optimizados para memoria.
- Como consecuencia, no se admite el uso del procedimiento almacenado del sistema
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 datostempdb
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 detempdb
) - 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;