Understanding TempDB: Spot Problems and Relieve the Load

/* PSG Stack · SQL Server TempDB · Query 1
   TempDB snapshot: Who is using space right now, and what is holding on to it?

   - Read-only. Changes nothing, ends no session.
   - Every part runs with MAXDOP 1.
   - Returns four results:
       1 Usage of the TempDB data files by type
       2 Sessions with the largest TempDB usage
       3 Version store: size per database and oldest snapshot transactions
       4 TempDB log: fill level, wait reason and open transactions
   - Permissions: VIEW SERVER STATE
     (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough).
     Without this permission, every part fails with error 300 (VIEW DATABASE STATE alone is not enough).
   - SQL Server 2017 and later. Tested in the PSG lab on 2017, 2019, 2022, and 2025.
   - Runs in any database: TempDB values always come from TempDB.
   - Snapshot: values change continuously. Run it several times if needed.
*/

/* 1 · Usage of the TempDB data files by type (MB) */
SELECT
    SUM(fsu.user_object_reserved_page_count)     * 8 / 1024 AS user_objects_mb,
    SUM(fsu.internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(fsu.version_store_reserved_page_count)   * 8 / 1024 AS version_store_mb,
    SUM(fsu.mixed_extent_page_count)             * 8 / 1024 AS mixed_extents_mb,
    SUM(fsu.unallocated_extent_page_count)       * 8 / 1024 AS free_mb,
    COUNT(*)                                                AS data_files
FROM tempdb.sys.dm_db_file_space_usage AS fsu
OPTION (MAXDOP 1);

/* 2 · Sessions with the largest TempDB usage
   Completed tasks (session_space_usage) plus running tasks (task_space_usage).
   User objects: #temp tables, table variables, index builds with SORT_IN_TEMPDB.
   Internal objects: sort, hash, spools, cursors, LOB intermediate results.
   The counters apply per session: if another session drops an object (e.g., a global ## table),
   the space stays charged to the creating session until it ends. The sum can therefore
   exceed result 1. */
WITH space_used AS (
    SELECT ssu.session_id,
           ssu.user_objects_alloc_page_count     - ssu.user_objects_dealloc_page_count     AS user_pages,
           ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count AS internal_pages
    FROM sys.dm_db_session_space_usage AS ssu
    WHERE ssu.database_id = 2
    UNION ALL
    SELECT tsu.session_id,
           tsu.user_objects_alloc_page_count     - tsu.user_objects_dealloc_page_count,
           tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count
    FROM sys.dm_db_task_space_usage AS tsu
    WHERE tsu.database_id = 2
), per_session AS (
    SELECT session_id,
           SUM(user_pages)     AS user_pages,
           SUM(internal_pages) AS internal_pages
    FROM space_used
    GROUP BY session_id
)
SELECT TOP (20)
    js.session_id,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status,
    CAST(CASE WHEN js.user_pages     > 0 THEN js.user_pages     ELSE 0 END * 8 / 1024.0 AS decimal(12,1)) AS user_objects_mb,
    CAST(CASE WHEN js.internal_pages > 0 THEN js.internal_pages ELSE 0 END * 8 / 1024.0 AS decimal(12,1)) AS internal_objects_mb,
    r.command,
    r.wait_type,
    r.granted_query_memory * 8 / 1024                       AS memory_grant_mb,
    DB_NAME(r.database_id)                                  AS database_name,
    LEFT(st.text, 4000)                                     AS sql_text
FROM per_session AS js
JOIN sys.dm_exec_sessions AS es ON es.session_id = js.session_id
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = js.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE es.is_user_process = 1
  AND (js.user_pages > 0 OR js.internal_pages > 0)
ORDER BY (CASE WHEN js.user_pages     > 0 THEN js.user_pages     ELSE 0 END
        + CASE WHEN js.internal_pages > 0 THEN js.internal_pages ELSE 0 END) DESC
OPTION (MAXDOP 1);

/* 3a · Version store in TempDB per database (MB) */
SELECT
    DB_NAME(vs.database_id)                       AS database_name,
    vs.reserved_page_count * 8 / 1024             AS version_store_mb
FROM sys.dm_tran_version_store_space_usage AS vs
WHERE vs.reserved_page_count > 0
ORDER BY vs.reserved_page_count DESC
OPTION (MAXDOP 1);

/* 3b · Oldest snapshot transactions: they hold on to the version store
   snapshot_isolation = 0: transaction without SNAPSHOT isolation that creates row versions
   (e.g., writers under READ_COMMITTED_SNAPSHOT). */
SELECT TOP (10)
    ast.session_id,
    ast.elapsed_time_seconds                      AS open_s,
    ast.is_snapshot                               AS snapshot_isolation,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status,
    es.last_request_end_time                      AS last_request_end
FROM sys.dm_tran_active_snapshot_database_transactions AS ast
LEFT JOIN sys.dm_exec_sessions AS es ON es.session_id = ast.session_id
ORDER BY ast.elapsed_time_seconds DESC
OPTION (MAXDOP 1);

/* 4a · TempDB log: size, fill level and wait reason */
SELECT
    CAST(ls.total_log_size_in_bytes / 1048576.0 AS decimal(12,1)) AS log_mb,
    CAST(ls.used_log_space_in_percent AS decimal(5,1))             AS used_percent,
    d.log_reuse_wait_desc                                          AS log_wait_reason
FROM tempdb.sys.dm_db_log_space_usage AS ls
CROSS JOIN sys.databases AS d
WHERE d.database_id = 2
OPTION (MAXDOP 1);

/* 4b · Open transactions with TempDB log usage, largest first */
SELECT TOP (10)
    st.session_id,
    dt.database_transaction_begin_time                                    AS began_at,
    DATEDIFF(SECOND, dt.database_transaction_begin_time, SYSDATETIME())   AS open_s,
    CAST(dt.database_transaction_log_bytes_used     / 1048576.0 AS decimal(12,1)) AS log_used_mb,
    CAST(dt.database_transaction_log_bytes_reserved / 1048576.0 AS decimal(12,1)) AS log_reserved_mb,
    es.login_name,
    es.program_name,
    es.status
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_sessions AS es ON es.session_id = st.session_id
WHERE dt.database_id = 2
ORDER BY dt.database_transaction_log_bytes_used DESC
OPTION (MAXDOP 1);

1 · Usage by type (MB)
user_objects_mb  internal_objects_mb  version_store_mb  free_mb
40               48                   53                127

2 · Sessions with the largest usage
session_id  status    user_objects_mb  internal_objects_mb
54          sleeping  0.0              42.4
56          sleeping  39.2             0.0
60          running   0.0              6.0

3b · Oldest snapshot transactions
session_id  open_s  snapshot_isolation  status
53          25      1                   sleeping

4a · TempDB log
log_mb  used_percent  log_wait_reason
136.0   99.5          ACTIVE_TRANSACTION

4b · Open transactions with TempDB log usage
session_id  open_s  log_used_mb  status
56          20      78.0         sleeping