Find sessions and requests that consume tempdb

tempdb grows, approaches its limit, or shows pressure during a particular load. We need to know which sessions retain user and internal object assignments and how much corresponds to still active tasks.

tempdb is the system base that SQL Server uses for temporary objects and internal work, such as certain sorts, hash tables, and row versions. An assignment indicates space reserved for that work; It does not identify by itself which operation required it.

What we need to distinguish

A user object is an explicitly created temporary structure such as #Table; an internal object is work the engine creates to run operations, such as sorts or hash tables.

Session counters are updated when a task finishes; Task counters show work that is still active. Dynamic management views (DMVs) expose each of those perspectives. By keeping both parts separate we avoid interpreting a single part as if it represented the entire session.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
WITH TareasActivas AS
(
    SELECT
        tsu.session_id,
        SUM(tsu.user_objects_alloc_page_count
            - tsu.user_objects_dealloc_page_count) AS PaginasUsuario,
        SUM(tsu.internal_objects_alloc_page_count
            - tsu.internal_objects_dealloc_page_count) AS PaginasInternas
    FROM sys.dm_db_task_space_usage AS tsu
    GROUP BY tsu.session_id
)
SELECT
    s.session_id AS IdSesion,
    s.login_name AS InicioSesion,
    s.host_name AS EquipoCliente,
    s.program_name AS Aplicacion,
    solicitud.request_id AS IdSolicitudRepresentativa,
    solicitud.status AS EstadoSolicitud,
    solicitud.command AS Comando,
    CONVERT
    (
        DECIMAL(18, 2),
        8.0 * (ssu.user_objects_alloc_page_count
            - ssu.user_objects_dealloc_page_count) / 1024
    ) AS UsuarioTareasTerminadasMb,
    CONVERT
    (
        DECIMAL(18, 2),
        8.0 * (ssu.internal_objects_alloc_page_count
            - ssu.internal_objects_dealloc_page_count) / 1024
    ) AS InternoTareasTerminadasMb,
    CONVERT
    (
        DECIMAL(18, 2),
        8.0 * COALESCE(ta.PaginasUsuario, 0) / 1024
    ) AS UsuarioTareasActivasMb,
    CONVERT
    (
        DECIMAL(18, 2),
        8.0 * COALESCE(ta.PaginasInternas, 0) / 1024
    ) AS InternoTareasActivasMb,
    texto.text AS LoteRepresentativo
FROM sys.dm_db_session_space_usage AS ssu
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = ssu.session_id
LEFT JOIN TareasActivas AS ta
    ON ta.session_id = ssu.session_id
OUTER APPLY
(
    SELECT TOP (1)
        r.request_id,
        r.status,
        r.command,
        r.sql_handle,
        r.total_elapsed_time
    FROM sys.dm_exec_requests AS r
    WHERE r.session_id = s.session_id
    ORDER BY r.total_elapsed_time DESC
) AS solicitud
OUTER APPLY sys.dm_exec_sql_text(solicitud.sql_handle) AS texto
WHERE s.is_user_process = 1
  AND
  (
      ssu.user_objects_alloc_page_count
        + ssu.internal_objects_alloc_page_count
        - ssu.user_objects_dealloc_page_count
        - ssu.internal_objects_dealloc_page_count
        + COALESCE(ta.PaginasUsuario, 0)
        + COALESCE(ta.PaginasInternas, 0)
  ) > 0
ORDER BY
    UsuarioTareasActivasMb + InternoTareasActivasMb DESC,
    IdSesion;
GO

Parameters or permissions

Run the query in tempdb; both space DMVs have scope on that basis. VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE, is required from SQL Server 2022. Values are converted from 8 KB pages to MB. If a session has several active requests, RepresentativeLot corresponds to the one with the longest duration, while task assignments bring together the entire session.

How to read the result

User objects include temporary tables and table variables. Internal ones can represent sorting, hashing, spooling, or temporary storage work for large objects. Compare completed and active tasks. A session can conserve space after finishing a request, and an active task can quickly switch between captures.

What this does establish

Allows you to locate user sessions with net space allocated in tempdb and separate the cumulative consumption of completed tasks from the currently active job.

What this still does not establish

It does not attribute each page to a specific operator, it does not fully represent the version store, and it does not demonstrate that a session is using space incorrectly.

Next reasonable check

Repeat the capture during growth and retrieve the plan from the relevant request. Review hash operations, sort, spool, temporary tables and memory grants.

Search SQL Server