tempdb [base de datos]

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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 de SQL Server, Azure SQL Database o Azure SQL Managed Instance.

Información general

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

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

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

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

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

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

    • Versiones de fila generadas por las transacciones de modificación de datos en una base de datos que utiliza READ COMMITTED a través de transacciones de aislamiento de versiones de fila o de aislamiento de instantáneas.
    • Versiones de fila que se generan mediante transacciones de modificación de datos para características, como operaciones de índice en línea, conjuntos de resultados activos múltiples (MARS) y desencadenadores AFTER.

Las operaciones de tempdb se registran de forma mínima, por lo que las transacciones se pueden revertir. tempdb se vuelve a crear cada vez que se inicia SQL Server, de forma que el sistema siempre se inicia con una copia limpia de la base de datos. Las tablas y los procedimientos almacenados temporales se quitan automáticamente en la desconexión y ninguna conexión permanece activa cuando se cierra el sistema.

tempdb nunca tiene nada que guardarse de una sesión de SQL Server a otra. No se permite realizar operaciones de copia de seguridad y restauración en tempdb.

Propiedades físicas de tempdb en SQL Server

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

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.

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

El valor predeterminado para el número de archivos de datos se basa en las directrices KB 2154845.

Para comprobar los parámetros de tamaño y de crecimiento actuales de tempdb, consulte la vista tempdb.sys.database_files.

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

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

Opciones de base de datos de tempdb en SQL Server

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

Opción de base de datos Valor predeterminado Se puede modificar
ALLOW_SNAPSHOT_ISOLATION Apagado Sí
ANSI_NULL_DEFAULT Apagado Sí
ANSI_NULLS Apagado Sí
ANSI_PADDING Apagado Sí
ANSI_WARNINGS Apagado Sí
ARITHABORT Apagado Sí
AUTO_CLOSE Apagado No
AUTO_CREATE_STATISTICS ACTIVAR Sí
AUTO_SHRINK Apagado No
AUTO_UPDATE_STATISTICS ACTIVAR Sí
AUTO_UPDATE_STATISTICS_ASYNC Apagado Sí
CHANGE_TRACKING Apagado No
CONCAT_NULL_YIELDS_NULL Apagado Sí
CURSOR_CLOSE_ON_COMMIT Apagado Sí
CURSOR_DEFAULT GLOBAL Sí
Opciones de disponibilidad de la base de datos ONLINE

MULTI_USER

READ_WRITE		 No

N.º

No
DATE_CORRELATION_OPTIMIZATION Apagado Sí
DB_CHAINING ACTIVAR No
ENCRYPTION Apagado No
MIXED_PAGE_ALLOCATION Apagado No
NUMERIC_ROUNDABORT Apagado Sí
PAGE_VERIFY CHECKSUM para las nuevas instalaciones de SQL Server

NONE para las actualizaciones de SQL Server		 Sí
PARAMETERIZATION SIMPLE Sí
QUOTED_IDENTIFIER Apagado Sí
READ_COMMITTED_SNAPSHOT Apagado No
RECOVERY SIMPLE No
RECURSIVE_TRIGGERS Apagado Sí
Opciones de Service Broker ENABLE_BROKER Sí
TRUSTWORTHY Apagado No

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

tempdb en Azure SQL

El comportamiento de tempdb en Azure SQL Database difiere del comportamiento de SQL Server, Azure SQL Managed Instance y SQL Server en máquinas virtuales de Azure.

tempdb en SQL Database

Las bases de datos únicas y agrupadas de Azure SQL Database admiten tablas temporales globales y procedimientos almacenados temporales globales con ámbito en el nivel de base de datos y almacenados en tempdb. Las tablas temporales globales y los procedimientos almacenados temporales globales se comparten para las sesiones de todos los usuarios dentro de la misma base de datos. Las sesiones de usuario de otras bases de datos no pueden acceder a tablas temporales globales. Para obtener más información, vea Database scoped global temporary tables (Azure SQL Database) (Tablas temporales globales con ámbito de base de datos [Azure SQL Database]).

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

En el caso de las bases de datos únicas y agrupadas en Azure SQL Database, de todas las bases de datos del sistema, solo se puede acceder a la base de datos master y a la base de datos tempdb. Para más información, consulte ¿Qué es un servidor lógico en Azure?

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

tempdb en SQL Managed Instance

Azure SQL Managed Instance admite objetos temporales de la misma manera que SQL Server, donde todas las sesiones de usuario de la misma instancia administrada pueden acceder a todas las tablas temporales globales y a los procedimientos almacenados temporales globales. Del mismo modo, se puede acceder a todas las bases de datos del sistema.

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.

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

Restricciones

Las siguientes operaciones no se pueden realizar en la base de datos tempdb:

  • Agregar grupos de archivos.
  • Realizar una copia de seguridad o restaurar la base de datos.
  • Cambiar intercalaciones. La intercalación predeterminada es la intercalación de servidor.
  • Cambiar el propietario de la base de datos. tempdb es propiedad de sa.
  • Crear una instantánea de base de datos.
  • Eliminar la base de datos.
  • Eliminar el usuario guest de la base de datos.
  • Habilitar el mecanismo de captura de cambios en los datos.
  • Participar en el reflejo de la base de datos.
  • Quitar el grupo de archivos principal, el archivo de datos principal o el archivo de registro.
  • Cambiar el nombre de la base de datos o del grupo de archivos principal.
  • Ejecutar DBCC CHECKALLOC.
  • Ejecutar DBCC CHECKCATALOG.
  • Establecer la base de datos en OFFLINE.
  • Cambiar el nombre de la base de datos o del grupo de archivos principal a READ_ONLY.

Permisos

Cualquier usuario puede crear objetos temporales en tempdb. Los usuarios solo pueden acceder a sus propios objetos, a menos que reciban permisos adicionales. Es posible revocar el permiso de conexión a tempdb para impedir que un usuario use tempdb. No se recomienda porque algunas operaciones rutinarias requieren el uso de tempdb.

Optimización del rendimiento de tempdb en SQL Server

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

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

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

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

Establezca el incremento de crecimiento de archivos en un tamaño razonable y establezca el mismo incremento en todos los archivos de datos para evitar que los archivos de la base de datos tempdb crezcan en un porcentaje demasiado pequeño. Si el crecimiento de los archivos es demasiado pequeño comparado con la cantidad de datos que se escriben en tempdb, es posible que sea necesario expandir tempdb frecuentemente mediante eventos de crecimiento automático. Los eventos de crecimiento automático afectan negativamente al rendimiento.

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

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.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_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

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

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

Nota:

Aunque la opción de base de datos DELAYED_DURABILITY está establecida en DISABLED para tempdb, SQL Server usa confirmaciones diferidas para vaciar los cambios de registro tempdb en el disco, ya que tempdb se crea en el inicio y no es necesario ejecutar el proceso de recuperación.

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. También reduce la asignación de páginas y la contención de metadatos.
  • El protocolo de bloqueo temporal de página de asignación se ha mejorado para reducir el número de bloqueos temporales UP (actualizaciones).
  • Se reduce la sobrecarga del registro de tempdb para reducir el consumo de ancho de banda de E/S del disco en el archivo de registro de tempdb.
  • El programa de instalación agrega varios archivos de datos tempdb durante una instalación nueva de la instancia. Esta tarea puede realizarse con el nuevo control de entrada de la IU en la sección Configuración del motor de base de datos y un parámetro de línea de comandos /SQLTEMPDBFILECOUNT. De manera predeterminada, la configuración agrega tantos archivos de datos de tempdb como el número de procesadores lógicos u ocho, lo que sea menor.
  • Si hay varios archivos de datos tempdb, todos crecen automáticamente al mismo tiempo y la misma cantidad en función de la configuración de crecimiento. La marca de seguimiento 1117 ya no es necesaria. 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 y marcas de seguimiento y actualizaciones: ¡a por ello!.
  • Para el grupo de archivos principal, la propiedad AUTOGROW_ALL_FILES se activa y la propiedad no se puede modificar.

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.
  • Se ha incluido una nueva DMV en SQL Server 2017, sys.dm_tran_version_store_space_usage, para llevar un seguimiento del uso del almacén de versiones de cada base de datos. Esta nueva DMV será útil para supervisar tempdb el uso del almacén de versiones para los DBA que pueden planear tempdb de forma proactiva el ajuste de tamaño en función del requisito de uso del almacén de versiones por base de datos.
  • Las nuevas características de procesamiento de consultas inteligentes, como combinaciones adaptables y comentarios de concesión de memoria, reducen los desbordamientos de memoria en ejecuciones consecutivas de una consulta, lo que reduce el uso innecesario de tempdb.

Introducido en SQL Server 2019 (15.x)

  • A partir de SQL Server 2019 (15.x), SQL Server no usa la opción FILE_FLAG_WRITE_THROUGH al abrir archivos para tempdb para permitir el rendimiento máximo del disco. Dado que tempdb se vuelve a crear al iniciar SQL Server, estas opciones no son necesarias, ya que son para otras bases de datos del sistema y bases de datos de usuario para la coherencia 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 de TempDB optimizados para memoria quitan un cuello de botella en PAGELATCH espera en tempdb y desbloquean un nuevo nivel de escalabilidad. Para obtener más información, vea este vídeo de demostración sobre cómo (y cuándo): Metadatos de TempDB optimizados para memoria. Para obtener más información, lea Supervisión y solución de problemas de metadatos tempdb optimizadospara memoria .
  • Las actualizaciones de la página actualizaciones simultáneas de página con espacio disponible (PFS) reducen la contención de bloqueos temporales de parches en todas las bases de datos, un problema que se suele ver en tempdb. Esta mejora cambia la manera en que se administra la simultaneidad con las actualizaciones de PFS para que puedan actualizarse en un bloqueo temporal compartido, en lugar de un bloqueo 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 En segundo plano: 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 principal o secundaria en contexto. Cada archivo 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)

Metadatos tempdb optimizados para memoria

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

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

Nota:

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

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

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

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

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

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

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

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

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

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

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

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

Limitaciones de tempdb optimizadas para memoria

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

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

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

    Ejemplo:

    BEGIN TRAN;

SELECT *
FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb

INSERT INTO <user database>.<schema>.<mem-optimized table>
VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail

COMMIT TRAN;

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

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

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

  • Un procedimiento almacenado del sistema está disponible para hacer que el motor en memoria libere memoria relacionada con las filas eliminadas de datos en memoria que son aptas para la recolección de elementos no utilizados. Esto puede ayudar a solucionar errores de memoria insuficiente específicos de metadatos tempdb optimizados para memoria (HkTempDB). Para obtener más información, consulte sys.sp_xtp_force_gc (Transact-SQL).

Nota:

Estas limitaciones se aplican solo cuando se hace referencia a vistas del sistema tempdb. 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.

Planeamiento de capacidad para tempdb en SQL Server

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

Se recomienda analizar la carga de trabajo existente llevando a cabo las siguientes tareas en un entorno de prueba de SQL Server:

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

Supervisión del uso de tempdb

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

Por ejemplo, los cuatro scripts de ejemplo siguientes encuentran la cantidad de espacio libre en tempdb, la cantidad de espacio utilizado por el almacén de versiones, la cantidad de espacio utilizado por los objetos internos y la cantidad de espacio utilizado por los objetos de usuario:

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

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

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

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

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

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

-- 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;

Use el siguiente script para encontrar el espacio consumido tempdb por objetos internos en la sesión actual para ambas tareas, en ejecución y completadas:

-- 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;

Contenido relacionado