tempdb [base de datos]tempdb database

Se aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL DatabaseSe aplica a:Applies to: síSQL ServerSQL Server (todas las versiones admitidas) yesSQL ServerSQL Server (all supported versions) SíAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

La base de datos del sistema tempdb es un recurso global disponible para todos los usuarios conectados a la instancia de SQL ServerSQL Server o a Azure SQL Database.The tempdb system database is a global resource that's available to all users connected to the instance of SQL ServerSQL Server or connected to Azure SQL Database. tempdb contiene:tempdb holds:

  • Los objetos de usuario temporales que se hayan creado explícitamente.Temporary user objects that are explicitly created. Incluyen tablas e índices temporales locales o globales, procedimientos almacenados temporales, variables de tabla, tablas devueltas en funciones con valores de tabla y cursores.They include global or local temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors.

  • Objetos internos que crea el motor de base de datos.Internal objects that the database engine creates. Incluyen:They include:

    • Tablas de trabajo para almacenar resultados intermedios para colas, cursores, ordenaciones y almacenamiento temporal de objetos grandes (LOB).Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
    • Archivos de trabajo para operaciones de combinación hash o de agregado hash.Work files for hash join or hash aggregate operations.
    • 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.Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.

    Cada objeto interno usa un mínimo de nueve páginas: una página IAM y una extensión de ocho páginas.Each internal object uses a minimum of nine pages: an IAM page and an eight-page extent. Para obtener más información acerca de las páginas y las extensiones, vea Páginas y extensiones.For more information about pages and extents, see Pages and extents.

    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.Azure SQL Database single databases and elastic pools support global temporary tables and global temporary stored procedures that are stored in tempdb and are scoped to the database level.

    Las tablas temporales globales y los procedimientos almacenados temporales globales se comparten entre todos los usuarios en la misma base de datos SQL.Global temporary tables and global temporary stored procedures are shared for all users' sessions within the same SQL database. Las sesiones de usuario de otras bases de datos no pueden acceder a tablas temporales globales.User sessions from other SQL databases can't access global temporary tables. 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]).For more information, see Database scoped global temporary tables (Azure SQL Database). Azure SQL Managed Instance admite los mismos objetos temporales que SQL Server.Azure SQL Managed Instance supports the same temporary objects as does 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.For Azure SQL Database single databases and elastic pools, only the master database and tempdb database apply. Para obtener más información, vea la Qué es un servidor de Azure SQL Database.For more information, see What is an Azure SQL Database server?. 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.For a discussion of tempdb in the context of Azure SQL Database single databases and elastic pools, see tempdb database in Azure SQL Database single databases and elastic pools.

    En el caso de Azure SQL Managed Instance, se aplican todas las bases de datos del sistema.For Azure SQL Managed Instance, all system databases apply.

  • 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.Version stores, which are collections of data pages that hold the data rows that support features for row versioning. Hay dos tipos: un almacén de versiones común y otro de generación de índices en línea.There are two types: a common version store and an online-index-build version store. Los almacenes de versión contienen:The version stores contain:

    • 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.Row versions that are generated by data modification transactions in a database that uses READ COMMITTED through row versioning isolation or snapshot isolation transactions.
    • 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.Row versions that are generated by data modification transactions for features, such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Las operaciones de tempdb se registran de forma mínima, por lo que las transacciones se pueden revertir.Operations within tempdb are minimally logged so that transactions can be rolled back. tempdb se vuelve a crear cada vez que se inicia SQL ServerSQL Server, de forma que el sistema siempre se inicia con una copia limpia de la base de datos.tempdb is re-created every time SQL ServerSQL Server is started so that the system always starts with a clean copy of the database. 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.Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down.

tempdb nunca tiene nada que guardarse de una sesión de SQL ServerSQL Server a otra.tempdb never has anything to be saved from one session of SQL ServerSQL Server to another. No se permite realizar operaciones de copia de seguridad y restauración en tempdb.Backup and restore operations are not allowed on tempdb.

Propiedades físicas de tempdb en SQL ServerPhysical properties of tempdb in 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.The following table lists the initial configuration values of the tempdb data and log files in SQL Server. Los valores se basan en los valores predeterminados para la base de datos model.The values are based on the defaults for the model database. El tamaño de estos archivos puede variar ligeramente para diferentes ediciones de SQL ServerSQL Server.The sizes of these files might vary slightly for different editions of SQL ServerSQL Server.

ArchivoFile Nombre lógicoLogical name Nombre físicoPhysical name Tamaño inicialInitial size Crecimiento del archivoFile growth
Datos principalesPrimary data tempdevtempdev tempdb.mdftempdb.mdf 8 megabytes8 megabytes Crecimiento automático de 64 MB hasta llenar el disco.Autogrow by 64 MB until the disk is full
Archivos de datos secundariosSecondary data files temp#temp# tempdb_mssql_ # .ndftempdb_mssql_#.ndf 8 megabytes8 megabytes Crecimiento automático de 64 MB hasta llenar el disco.Autogrow by 64 MB until the disk is full
LogLog templogtemplog templog.ldftemplog.ldf 8 megabytes8 megabytes Crecimiento automático de 64 megabytes hasta un máximo de 2 terabytes.Autogrow by 64 megabytes to a maximum of 2 terabytes

El número de archivos de datos secundarios depende del número de procesadores (lógicos) de la máquina.The number of secondary data files depends on the number of (logical) processors on the machine. 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.As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. Si el número de procesadores lógicos es superior a ocho, utilice ocho archivos de datos.If the number of logical processors is greater than eight, use eight data files. 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.Then if contention continues, increase the number of data files by multiples of four until the contention decreases to acceptable levels, or make changes to the workload/code.

Nota

El valor predeterminado para el número de archivos de datos se basa en las directrices KB 2154845.The default value for the number of data files is based on the general guidelines in KB 2154845.

Mover los archivos de datos y registro de tempdb en SQL ServerMoving the tempdb data and log files in SQL Server

Para mover los archivos de registro y de datos de tempdb, consulte Mover bases de datos del sistema.To move the tempdb data and log files, see Move system databases.

Opciones de base de datos de tempdb en SQL ServerDatabase options for tempdb in 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.The following table lists the default value for each database option in the tempdb database and whether the option can be modified. Para ver la configuración actual de estas opciones, utilice la vista de catálogo sys.databases .To view the current settings for these options, use the sys.databases catalog view.

Opción de base de datosDatabase option Valor predeterminadoDefault value Se puede modificarCan be modified
ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION ApagadoOFF Yes
ANSI_NULL_DEFAULTANSI_NULL_DEFAULT ApagadoOFF Yes
ANSI_NULLSANSI_NULLS ApagadoOFF Yes
ANSI_PADDINGANSI_PADDING ApagadoOFF Yes
ANSI_WARNINGSANSI_WARNINGS ApagadoOFF Yes
ARITHABORTARITHABORT ApagadoOFF Yes
AUTO_CLOSEAUTO_CLOSE ApagadoOFF NoNo
AUTO_CREATE_STATISTICSAUTO_CREATE_STATISTICS ACTIVARON Yes
AUTO_SHRINKAUTO_SHRINK ApagadoOFF NoNo
AUTO_UPDATE_STATISTICSAUTO_UPDATE_STATISTICS ACTIVARON Yes
AUTO_UPDATE_STATISTICS_ASYNCAUTO_UPDATE_STATISTICS_ASYNC ApagadoOFF Yes
CHANGE_TRACKINGCHANGE_TRACKING ApagadoOFF NoNo
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL ApagadoOFF Yes
CURSOR_CLOSE_ON_COMMITCURSOR_CLOSE_ON_COMMIT ApagadoOFF Yes
CURSOR_DEFAULTCURSOR_DEFAULT GLOBALGLOBAL Yes
Opciones de disponibilidad de la base de datosDatabase Availability Options ONLINEONLINE

MULTI_USERMULTI_USER

READ_WRITEREAD_WRITE
NoNo

NoNo

NoNo
DATE_CORRELATION_OPTIMIZATIONDATE_CORRELATION_OPTIMIZATION ApagadoOFF Yes
DB_CHAININGDB_CHAINING ACTIVARON NoNo
ENCRYPTIONENCRYPTION ApagadoOFF NoNo
MIXED_PAGE_ALLOCATIONMIXED_PAGE_ALLOCATION ApagadoOFF NoNo
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT ApagadoOFF Yes
PAGE_VERIFYPAGE_VERIFY CHECKSUM para las nuevas instalaciones de SQL ServerSQL ServerCHECKSUM for new installations of SQL ServerSQL Server

NONE para las actualizaciones de SQL ServerSQL ServerNONE for upgrades of SQL ServerSQL Server
Yes
PARAMETERIZATIONPARAMETERIZATION SIMPLESIMPLE Yes
QUOTED_IDENTIFIERQUOTED_IDENTIFIER ApagadoOFF Yes
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT ApagadoOFF NoNo
RECOVERYRECOVERY SIMPLESIMPLE NoNo
RECURSIVE_TRIGGERSRECURSIVE_TRIGGERS ApagadoOFF Yes
Opciones de Service BrokerService Broker Options ENABLE_BROKERENABLE_BROKER Yes
TRUSTWORTHYTRUSTWORTHY ApagadoOFF NoNo

Para obtener una descripción de estas opciones de la base de datos, vea Opciones de ALTER DATABASE SET (Transact-SQL).For a description of these database options, see ALTER DATABASE SET Options (Transact-SQL).

Base de datos tempdb en SQL Databasetempdb database in SQL Database

tamaños de tempdb para los niveles de servicio basado en DTUtempdb sizes for DTU-based service tiers

Objetivo de nivel de servicioService-level objective Tamaño máximo del archivo de datos de tempdb (GB)Maximum tempdb data file size (GB) Número de archivos de datos de tempdbNumber of tempdb data files Tamaño máximo de los datos de tempdb (GB)Maximum tempdb data size (GB)
BásicaBasic 13.913.9 11 13.913.9
S0S0 13.913.9 11 13.913.9
S1S1 13.913.9 11 13.913.9
S2S2 13.913.9 11 13.913.9
S3S3 3232 11 3232
S4S4 3232 22 6464
S6S6 3232 33 9696
S7S7 3232 66 192192
S9S9 3232 1212 384384
S12S12 3232 1212 384384
P1P1 13.913.9 1212 166.7166.7
P2P2 13.913.9 1212 166.7166.7
P4P4 13.913.9 1212 166.7166.7
P6P6 13.913.9 1212 166.7166.7
P11P11 13.913.9 1212 166.7166.7
P15P15 13.913.9 1212 166.7166.7
Grupos elásticos básicos (todas las configuraciones de DTU)Basic Elastic Pools (all DTU configurations) 13.913.9 1212 166.7166.7
Grupos elásticos estándar (50 eDTU)Standard Elastic Pools (50 eDTU) 13.913.9 1212 166.7166.7
Grupos elásticos estándar (100 eDTU)Standard Elastic Pools (100 eDTU) 3232 11 3232
Grupos elásticos estándar (200 eDTU)Standard Elastic Pools (200 eDTU) 3232 22 6464
Grupos elásticos estándar (300 eDTU)Standard Elastic Pools (300 eDTU) 3232 33 9696
Grupos elásticos estándar (400 eDTU)Standard Elastic Pools (400 eDTU) 3232 33 9696
Grupos elásticos estándar (800 eDTU)Standard Elastic Pools (800 eDTU) 3232 66 192192
Grupos elásticos estándar (1200 eDTU)Standard Elastic Pools (1200 eDTU) 3232 1010 320320
Grupos elásticos estándar (1600-3000 eDTU)Standard Elastic Pools (1600-3000 eDTU) 3232 1212 384384
Grupos elásticos premium (todas las configuraciones de DTU)Premium Elastic Pools (all DTU configurations) 13.913.9 1212 166.7166.7

tamaños de tempdb para los niveles de servicio basado en núcleo virtualtempdb sizes for vCore-based service tiers

Vea Límites de recursos basados en núcleos virtuales.See vCore-based resource limits.

RestriccionesRestrictions

Las siguientes operaciones no se pueden realizar en la base de datos tempdb:The following operations can't be performed on the tempdb database:

  • Agregar grupos de archivos.Adding filegroups.
  • Realizar una copia de seguridad o restaurar la base de datos.Backing up or restoring the database.
  • Cambiar intercalaciones.Changing collation. La intercalación predeterminada es la intercalación de servidor.The default collation is the server collation.
  • Cambiar el propietario de la base de datos.Changing the database owner. tempdb es propiedad de sa.tempdb is owned by sa.
  • Crear una instantánea de base de datos.Creating a database snapshot.
  • Eliminar la base de datos.Dropping the database.
  • Eliminar el usuario guest de la base de datos.Dropping the guest user from the database.
  • Habilitar el mecanismo de captura de cambios en los datos.Enabling Change Data Capture.
  • Participar en el reflejo de la base de datos.Participating in database mirroring.
  • Quitar el grupo de archivos principal, el archivo de datos principal o el archivo de registro.Removing the primary filegroup, primary data file, or log file.
  • Cambiar el nombre de la base de datos o del grupo de archivos principal.Renaming the database or primary filegroup.
  • Ejecutar DBCC CHECKALLOC.Running DBCC CHECKALLOC.
  • Ejecutar DBCC CHECKCATALOG.Running DBCC CHECKCATALOG.
  • Establecer la base de datos en OFFLINE.Setting the database to OFFLINE.
  • Cambiar el nombre de la base de datos o del grupo de archivos principal a READ_ONLY.Setting the database or primary filegroup to READ_ONLY.

PermisosPermissions

Cualquier usuario puede crear objetos temporales en tempdb.Any user can create temporary objects in tempdb. Los usuarios solo pueden acceder a sus propios objetos, a menos que reciban permisos adicionales.Users can access only their own objects, unless they receive additional permissions. Es posible revocar el permiso de conexión a tempdb para impedir que un usuario use tempdb.It's possible to revoke the connect permission to tempdb to prevent a user from using tempdb. No se recomienda porque algunas operaciones rutinarias requieren el uso de tempdb.We don't recommend it because some routine operations require the use of tempdb.

Optimizar el rendimiento de tempdb en SQL ServerOptimizing tempdb performance in SQL Server

El tamaño y la ubicación física de la base de datos tempdb puede afectar al rendimiento de un sistema.The size and physical placement of the tempdb database can affect the performance of a system. 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 ServerSQL Server.For example, if the size that's defined for tempdb is too small, part of the system-processing load might be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL ServerSQL 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.If possible, use instant file initialization to improve the performance of growth operations for data files.

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.Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. La asignación previa evita que tempdb se expanda con demasiada frecuencia, lo que afecta al rendimiento.Preallocation prevents tempdb from expanding too often, which affects performance. La base de datos tempdb debe establecerse de modo que crezca automáticamente para aumentar el espacio en disco para las excepciones no previstas.The tempdb database should be set to autogrow to increase disk space for unplanned exceptions.

Los archivos de datos deberían ser del mismo tamaño dentro de cada grupo de archivos, ya que SQL ServerSQL Server utiliza un algoritmo de relleno proporcional que favorece las asignaciones en los archivos con más espacio libre.Data files should be of equal size within each filegroup, because SQL ServerSQL Server uses a proportional-fill algorithm that favors allocations in files with more free space. 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.Dividing tempdb into multiple data files of equal size provides a high degree of parallel efficiency in operations that use 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.Set the file growth increment to a reasonable size to prevent the tempdb database files from growing by too small a value. 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.If the file growth is too small compared to the amount of data that's being written to tempdb, tempdb might have to constantly expand. Esto afectará al rendimiento.That will affect performance.

Para comprobar los parámetros actuales de tamaño y de crecimiento de tempdb, use la consulta siguiente:To check current size and growth parameters for tempdb, use the following query:

 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.Put the tempdb database on a fast I/O subsystem. Cree bandas en disco si hay muchos discos conectados directamente.Use disk striping if there are many directly attached disks. 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.Individual or groups of tempdb data files don't necessarily need to be on different disks or spindles unless you're also encountering I/O bottlenecks.

Coloque la base de datos tempdb en discos diferentes de los que usan las bases de datos de usuario.Put the tempdb database on disks that differ from the disks that user databases use.

Mejoras de rendimiento de tempdb para SQL ServerPerformance improvements in tempdb for SQL Server

A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x), se optimiza el rendimiento de tempdb de las maneras siguientes:Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), tempdb performance is further optimized in the following ways:

  • Las tablas temporales y las variables de tabla se almacenan en caché.Temporary tables and table variables are cached. El almacenamiento en caché permite que las operaciones que quitan y crean los objetos temporales se ejecuten muy rápidamente.Caching allows operations that drop and create the temporary objects to run very quickly. También reduce la asignación de páginas y la contención de metadatos.Caching also reduces page allocation and metadata contention.
  • El protocolo de bloqueo temporal de página de asignación se ha mejorado para reducir el número de bloqueos temporales UP (actualizaciones).The allocation page latching protocol is improved to reduce the number of UP (update) latches that are used.
  • 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.Logging overhead for tempdb is reduced to reduce disk I/O bandwidth consumption on the tempdb log file.
  • El programa de instalación agrega varios archivos de datos tempdb durante una instalación nueva de la instancia.Setup adds multiple tempdb data files during a new instance installation. 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.You can accomplish this task by using the new UI input control in the Database Engine Configuration section and the command-line parameter /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.By default, setup adds as many tempdb data files as the logical processor count or eight, whichever is lower.
  • 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.When there are multiple tempdb data files, all files autogrow at the same time and by the same amount, depending on growth settings. La marca de seguimiento 1117 ya no es necesaria.Trace flag 1117 is no longer required.
  • Todas las asignaciones de tempdb usarán extensiones uniformes.All allocations in tempdb use uniform extents. La marca de seguimiento 1118 ya no es necesaria.Trace flag 1118 is no longer required.
  • Para el grupo de archivos principal, la propiedad AUTOGROW_ALL_FILES se activa y la propiedad no se puede modificar.For the primary filegroup, the AUTOGROW_ALL_FILES property is turned on and the property can't be modified.

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!.For more information on performance improvements in tempdb, see the blog article TEMPDB - Files and Trace Flags and Updates, Oh My!.

Metadatos tempdb optimizados para memoriaMemory-optimized tempdb metadata

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 ServerSQL Server.Metadata contention in tempdb has historically been a bottleneck to scalability for many workloads running on SQL ServerSQL Server. SQL Server 2019 (15.x)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.introduces a new feature that's part of the in-memory database feature family: memory-optimized tempdb metadata.

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.This feature effectively removes this bottleneck and unlocks a new level of scalability for tempdb-heavy workloads. En SQL Server 2019 (15.x)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.In SQL Server 2019 (15.x)SQL Server 2019 (15.x), the system tables involved in managing temporary table metadata can be moved into latch-free, non-durable, memory-optimized tables.

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:Watch this seven-minute video for an overview of how and when to use memory-optimized tempdb metadata:

Configuración y uso de metadatos de tempdb con optimización para memoriaConfiguring and using memory-optimized tempdb metadata

Para poder participar en esta nueva característica, use el siguiente script:To opt in to this new feature, use the following script:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Para que este cambio de configuración surta efecto, es necesario reiniciar el servicio.This configuration change requires a restart of the service to take effect.

Puede comprobar si tempdb está optimizado para memoria mediante el siguiente comando de T-SQL:You can verify whether or not tempdb is memory-optimized by using the following T-SQL command:

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.If the server fails to start for any reason after you enable memory-optimized tempdb metadata, you can bypass the feature by starting the SQL Server instance with minimal configuration through the -f startup option. Después, puede deshabilitar la característica y reiniciar SQL Server en modo normal.You can then disable the feature and restart SQL Server in normal mode.

Para proteger el servidor de posibles condiciones de memoria insuficiente, puede enlazar tempdb a un grupo de recursos.To protect the server from potential out-of-memory conditions, you can bind tempdb to a resource pool. 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.This is done through the ALTER SERVER command rather than the steps you would normally follow to bind a resource pool to a database.

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.This change also requires a restart to take effect, even if memory-optimized tempdb metadata is already enabled.

Limitaciones de tempdb optimizadas para memoriaMemory-optimized tempdb limitations

  • Activar o desactivar la característica no es una acción dinámica.Toggling the feature on and off is not dynamic. 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.Because of the intrinsic changes that need to be made to the structure of tempdb, a restart is required to either enable or disable the feature.

  • Una única transacción no puede acceder a tablas optimizadas para memoria en más de una base de datos.A single transaction is not allowed to access memory-optimized tables in more than one database. 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.Any transactions that involve a memory-optimized table in a user database won't be able to access tempdb system views in the same transaction. 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:If you try to access tempdb system views in the same transaction as a memory-optimized table in a user database, you'll receive the following error:

    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:Example:

    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.Queries against memory-optimized tables don't support locking and isolation hints, so queries against memory-optimized tempdb catalog views won't honor locking and isolation hints. Como sucede con otras vistas de catálogo del sistema en SQL ServerSQL Server, todas las transacciones realizadas en vistas del sistema estarán en aislamiento READ COMMITTED (o, en este caso, READ COMMITTED SNAPSHOT).As with other system catalog views in SQL ServerSQL Server, all transactions against system views will be in READ COMMITTED (or in this case, READ COMMITTED SNAPSHOT) isolation.

  • 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.Columnstore indexes can't be created on temporary tables when memory-optimized tempdb metadata is enabled.

  • 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.Due to the limitation on columnstore indexes, use of the sp_estimate_data_compression_savings system stored procedure with the COLUMNSTORE or COLUMNSTORE_ARCHIVE data compression parameter is not supported when memory-optimized tempdb metadata is enabled.

Nota

Estas limitaciones se aplican solo cuando se hace referencia a vistas del sistema tempdb.These limitations apply only when you're referencing tempdb system views. Puede crear una tabla temporal en la misma transacción cuando tenga acceso a una tabla optimizada para memoria en una base de datos de usuario, si lo desea.You can create a temporary table in the same transaction as you access a memory-optimized table in a user database, if desired.

Planeamiento de capacidad para tempdb en SQL ServerCapacity planning for tempdb in SQL Server

Determinar el tamaño adecuado para tempdb en un entorno de producción SQL ServerSQL Server depende de muchos factores.Determining the appropriate size for tempdb in a SQL ServerSQL Server production environment depends on many factors. Como se ha descrito anteriormente, estos factores incluyen la carga de trabajo existente y las características de SQL ServerSQL Server que se usan.As described earlier, these factors include the existing workload and the SQL ServerSQL Server features that are used. Se recomienda analizar la carga de trabajo existente llevando a cabo las siguientes tareas en un entorno de prueba de SQL Server:We recommend that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

  • Active el crecimiento automático para tempdb.Set autogrow on for tempdb.
  • Ejecute consultas individuales o archivos de seguimiento de carga de trabajo y supervise el uso del espacio de tempdb.Run individual queries or workload trace files and monitor tempdb space use.
  • Ejecute operaciones de mantenimiento de índice, como volver a generar índices, y supervise el espacio de tempdb.Execute index maintenance operations such as rebuilding indexes, and monitor tempdb space.
  • Use los valores de uso de espacio de los pasos anteriores para predecir el uso de carga de trabajo total.Use the space-use values from the previous steps to predict your total workload usage. Ajuste este valor para la actividad simultánea proyectada y, luego, establezca el tamaño de tempdb según corresponda.Adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.

Supervisión del uso de tempdbMonitoring tempdb use

La falta de espacio en disco en tempdb puede provocar interrupciones importantes en el entorno de producción de SQL ServerSQL Server.Running out of disk space in tempdb can cause significant disruptions in the SQL ServerSQL Server production environment. También puede impedir que las aplicaciones que se ejecutan completen las operaciones.It can also prevent applications that are running from completing operations. 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:You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that's used in the tempdb files:

 -- 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 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 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 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 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.To monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. Estas vistas pueden ayudarle a detectar consultas grandes, tablas temporales o variables de tabla que emplean mucho espacio de disco de tempdb.These views can help you identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. También puede usar varios contadores para supervisar el espacio disponible en tempdb y los recursos que usan tempdb.You can also use several counters to monitor the free space that's available in tempdb and the resources that are using 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;;

Opción SORT_IN_TEMPDB para índices SORT_IN_TEMPDB option for indexes
Bases de datos del sistema System databases
sys.databases sys.databases
sys.master_files sys.master_files
Mover archivos de base de datosMove database files