SQL Server TempDB Usage and Contention Report

calendar Apr 17, 2026 / · 7 min read · sql server tempdb dm db session space usage dm exec sessions dm exec requests dm exec sql text monitoring dba scripts space usage contention performance  ·
Share on: linkedin copy

TempDB is a shared system database used by every session in SQL Server. When TempDB space grows or performance slows, it can be difficult to know which sessions are responsible. This script queries sys.dm_db_session_space_usage to show a clear breakdown of TempDB consumption per session, including the current SQL statement being run.

Purpose and Overview

TempDB is used for temporary tables, table variables, sort operations, hash joins, cursors, and row versioning. Any session can allocate TempDB space, and heavy use can cause disk pressure or contention on allocation pages (GAM, SGAM, PFS).

This script gives you a real-time view of which sessions are using TempDB, how much space they have allocated, and what SQL they are currently executing. It breaks allocation into three categories: user objects, internal objects, and version store. The results are sorted by total allocation so the largest consumers appear first.

Use this script when:

  • TempDB data files are growing unexpectedly
  • You suspect a runaway query is consuming TempDB space
  • You want to baseline TempDB usage during peak load
  • You are troubleshooting slow queries that involve large sorts or spills

Code Breakdown

 1SELECT
 2    s.session_id,
 3    s.login_name,
 4    s.host_name,
 5    s.status,
 6    su.user_objects_alloc_page_count,
 7    su.internal_objects_alloc_page_count,
 8    su.version_store_reserved_page_count,
 9    (su.user_objects_alloc_page_count * 8 / 1024.0)         AS user_objects_mb,
10    (su.internal_objects_alloc_page_count * 8 / 1024.0)     AS internal_objects_mb,
11    (su.version_store_reserved_page_count * 8 / 1024.0)     AS version_store_mb,
12    ((su.user_objects_alloc_page_count
13      + su.internal_objects_alloc_page_count
14      + su.version_store_reserved_page_count) * 8 / 1024.0) AS total_tempdb_mb,
15    r.status                                                  AS request_status,
16    r.wait_type,
17    r.wait_time,
18    r.blocking_session_id,
19    SUBSTRING(
20        t.text,
21        (r.statement_start_offset / 2) + 1,
22        CASE
23            WHEN r.statement_end_offset = -1
24                THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
25            ELSE r.statement_end_offset
26        END - r.statement_start_offset / 2 + 1
27    )                                                         AS current_sql_text
28FROM sys.dm_db_session_space_usage AS su
29INNER JOIN sys.dm_exec_sessions AS s
30    ON su.session_id = s.session_id
31LEFT JOIN sys.dm_exec_requests AS r
32    ON su.session_id = r.session_id
33OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
34WHERE su.session_id >= 50
35  AND (
36        su.user_objects_alloc_page_count > 0
37     OR su.internal_objects_alloc_page_count > 0
38     OR su.version_store_reserved_page_count > 0
39      )
40ORDER BY total_tempdb_mb DESC;

sys.dm_db_session_space_usage

This DMV is the core of the query. It reports TempDB page allocation and deallocation counts for each active session. The key columns used here are:

  • user_objects_alloc_page_count — pages allocated for user-created objects such as temporary tables (#temp) and table variables (@table)
  • internal_objects_alloc_page_count — pages allocated for internal SQL Server operations such as sort spills, hash joins, cursors, and XML operations
  • version_store_reserved_page_count — pages reserved for row version records used by snapshot isolation and read-committed snapshot isolation (RCSI)

Page Count to MB Conversion

Each SQL Server data page is 8 KB. To convert page counts to megabytes:

1pages * 8 / 1024.0 = megabytes

Using 1024.0 (with the decimal) ensures floating-point division rather than integer division, so values less than 128 pages do not round down to zero.

sys.dm_exec_sessions Join

The INNER JOIN to sys.dm_exec_sessions adds the login_name, host_name, and status columns. This tells you which application or user account is behind each session. Sessions with a session_id below 50 are SQL Server system sessions and are excluded by the WHERE su.session_id >= 50 filter.

sys.dm_exec_requests and sys.dm_exec_sql_text

The LEFT JOIN to sys.dm_exec_requests brings in request-level details: current wait type, wait time in milliseconds, and the blocking_session_id if the session is blocked. Sessions that are idle (not currently executing a request) will return NULL for these columns, which is why a LEFT JOIN is used rather than an INNER JOIN.

sys.dm_exec_sql_text is an apply function that accepts the sql_handle from sys.dm_exec_requests and returns the full SQL batch text. The SUBSTRING expression extracts the specific statement currently executing within the batch, using statement_start_offset and statement_end_offset. When statement_end_offset is -1 the statement runs to the end of the batch.

WHERE and ORDER BY

The filter su.session_id >= 50 removes system sessions. The second condition requires at least one non-zero page count, which removes idle sessions with no TempDB allocation. Results are ordered by total_tempdb_mb descending so the heaviest consumers appear at the top.

Key Benefits and Use Cases

  • Quickly identifies which session or query is consuming the most TempDB space
  • Separates user object space (temp tables) from internal object space (sort spills, hash joins) so you can target the right fix
  • Shows version store usage to help diagnose row versioning overhead from snapshot isolation
  • Includes the active SQL text so you can immediately investigate the problem query
  • Highlights blocked sessions via blocking_session_id and wait_type
  • Works on any SQL Server version that supports these DMVs (SQL Server 2005 and later)
  • Suitable for ad hoc troubleshooting and for inclusion in monitoring dashboards
  • Lightweight read-only query with no impact on production workloads

Performance Considerations

Run frequency. This query reads only DMVs and has minimal overhead. It is safe to run frequently during an incident. For scheduled monitoring, running it every one to five minutes is reasonable.

Page counts are cumulative within a session. The alloc_page_count columns accumulate over the life of the session, not just the current statement. A session that has run many queries may show high totals even if current usage is low. To see net current usage, subtract user_objects_dealloc_page_count from user_objects_alloc_page_count. The query above uses alloc counts for simplicity.

Version store. High version_store_reserved_page_count values indicate long-running transactions when snapshot isolation is in use. Investigate with sys.dm_tran_active_snapshot_database_transactions to find the oldest active transaction.

TempDB file configuration. If you consistently see high TempDB usage, also review your TempDB file configuration. Microsoft recommends one data file per physical CPU core, up to eight files, all of the same size. Trace flag 1117 and 1118 behavior is built into SQL Server 2016 and later by default.

Allocation page contention. This query shows space usage, not latch contention on TempDB allocation pages (GAM/SGAM/PFS). For latch contention, query sys.dm_os_waiting_tasks filtering on wait types PAGELATCH_UP or PAGELATCH_EX on TempDB pages.

Practical Tips

Add a threshold filter. If you only want sessions using more than 100 MB, add a HAVING-style filter by wrapping the query or adding a condition in the WHERE clause against the computed total:

1WHERE su.session_id >= 50
2  AND (su.user_objects_alloc_page_count
3       + su.internal_objects_alloc_page_count
4       + su.version_store_reserved_page_count) * 8 / 1024.0 > 100

Log results over time. Insert results into a DBA monitoring table on a schedule to capture TempDB trends. This helps identify peak usage periods and track whether a fix reduced consumption.

Combine with blocking detection. The blocking_session_id column in the output connects directly to a blocking analysis workflow. If a large TempDB consumer is also blocked, address the blocking first.

Review query plans. High internal object usage often means a sort or hash join spilled to TempDB. Retrieve the execution plan for the offending query and look for warning icons on Sort or Hash Match operators indicating a spill. Adding indexes or increasing max server memory can reduce spills.

Run at the right time. TempDB allocation exists only while the session is active. Run this script during the period of high TempDB growth, not after queries complete. Set up a SQL Server Agent job to capture this data automatically when TempDB file usage crosses a threshold.

Conclusion

This script gives you an immediate, session-level view of TempDB consumption in SQL Server. By combining sys.dm_db_session_space_usage with session, request, and SQL text DMVs, you can identify the exact query driving TempDB growth, understand whether the cause is user objects or internal spills, and take action quickly. Add it to your DBA toolkit for TempDB troubleshooting and routine monitoring.

References

Posts in this series