Identifying SQL Statements Causing TempDB Growth in SQL Server

Indicators

• TempDB grows to 500 GB or more, multiple times per month
• TempDB initial file sizes are automatically reset to larger values following growth events
• TempDB files cannot be shrunk due to cached execution plan locks
• Growth is not traceable to explicit temporary table creation but occurs during complex JOIN workloads
• High internal object allocation counts visible in DMV queries during or after growth events
• Disk space alerts firing repeatedly on the TempDB drive

Likely causes

• Complex JOIN queries generating large intermediate result sets spilled to TempDB (hash joins, sort spills)
• Internal SQL Server operations (sort, hash match, index spool) consuming TempDB space without explicit user temp objects
• Version store growth caused by snapshot isolation or read-committed snapshot isolation (RCSI) workloads
• Accumulation of user objects such as temporary tables or table variables across long-running sessions
• Cached execution plans holding open references to TempDB objects, blocking shrink operations
• Undersized or single-file TempDB configuration causing allocation contention and repeated auto-growth

Diagnostic steps

1. Query sys.dm_db_file_space_usage to get a current aggregate breakdown of TempDB space by version store, user objects, and internal objects: SELECT SUM(version_store_reserved_page_count)*8 AS [Version Store KB], SUM(user_object_reserved_page_count)*8 AS [User Objects KB], SUM(internal_object_reserved_page_count)*8 AS [Internal Objects KB], SUM(unallocated_extent_page_count)*8 AS [Free KB] FROM sys.dm_db_file_space_usage — this establishes which category of usage is dominant.
2. Query sys.dm_db_session_space_usage joined with sys.dm_exec_sessions to identify which sessions are consuming the most TempDB space: SELECT s.session_id, DB_NAME(s.database_id) AS db_name, s.host_name, s.program_name, s.login_name, s.status, (u.user_objects_alloc_page_count * 8) AS [User Objects KB], (u.internal_objects_alloc_page_count * 8) AS [Internal Objects KB] FROM sys.dm_db_session_space_usage u INNER JOIN sys.dm_exec_sessions s ON u.session_id = s.session_id ORDER BY (u.user_objects_alloc_page_count + u.internal_objects_alloc_page_count) DESC
3. For currently executing requests, query sys.dm_db_task_space_usage joined with sys.dm_exec_requests and sys.dm_exec_sql_text to get task-level TempDB allocation alongside the SQL statement: SELECT t.session_id, t.request_id, (t.internal_objects_alloc_page_count * 8) AS [Internal KB], (t.user_objects_alloc_page_count * 8) AS [User KB], SUBSTRING(st.text, (r.statement_start_offset/2)+1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS statement_text FROM sys.dm_db_task_space_usage t INNER JOIN sys.dm_exec_requests r ON t.session_id = r.session_id AND t.request_id = r.request_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st ORDER BY (t.internal_objects_alloc_page_count + t.user_objects_alloc_page_count) DESC
4. For sessions identified in step 2 that are no longer actively executing (idle sessions holding space), retrieve the last executed SQL using sys.dm_exec_connections joined with sys.dm_exec_sql_text on the most_recent_sql_handle column.
5. Deploy sp_whoisactive (Adam Machanic) via a SQL Agent job running every 1–5 minutes, logging output to a dedicated table. This enables post-incident analysis to identify which queries were executing during growth events: EXEC sp_whoisactive @output_column_list = '[dd%][session_id][sql_text][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical_io%][query_plan][locks%][%]', @destination_table = 'dbo.WhoIsActiveLog'
6. Review execution plans of the identified offending queries in SSMS (actual execution plan) and look for Sort and Hash Match operators with a Warnings icon indicating spill to TempDB. These are the primary internal object consumers.
7. If TempDB cannot be shrunk, identify cached plans holding locks: run DBCC FREEPROCCACHE to clear the plan cache (schedule during low-traffic window), then retry DBCC SHRINKFILE against each TempDB data file. Confirm no active sessions remain on TempDB beforehand.
8. Check TempDB file configuration: verify the number of data files (target: one per logical CPU core up to 8, all equal size), confirm autogrowth is set to a fixed MB value rather than percentage, and confirm initial sizes are set to a reasonable pre-allocated value to prevent repeated auto-growth events.

Resolution path

• During or immediately after a TempDB growth event, run the sys.dm_db_file_space_usage query to determine whether growth is driven by internal objects (spills), user objects (temp tables), or the version store.
• Run the session-level TempDB usage query (sys.dm_db_session_space_usage joined with sys.dm_exec_sessions) to rank sessions by TempDB consumption and identify the highest consumers.
• For active sessions, use the task-level DMV query (sys.dm_db_task_space_usage joined with sys.dm_exec_sql_text) to retrieve the exact SQL statement currently causing the allocation.
• For idle sessions or post-incident analysis, use sp_whoisactive historical log data or sys.dm_exec_connections to retrieve the last executed statement for each high-consumption session.
• Open the execution plan for identified queries in SSMS and locate Sort or Hash Match operators with spill warnings; these are the direct cause of internal object growth in TempDB.
• Optimise offending queries: add covering indexes to avoid sort spills, rewrite large JOIN chains to reduce intermediate result set sizes, and consider query hints (e.g., OPTION (MERGE JOIN)) where appropriate after testing.
• If TempDB cannot be shrunk due to cached plan locks, schedule DBCC FREEPROCCACHE during a low-traffic window, then run DBCC SHRINKFILE for each TempDB data file back to the target initial size.
• Set TempDB data file initial sizes to a pre-allocated value reflecting normal peak usage to prevent repeated auto-growth events resetting the baseline upward.
• Confirm TempDB has the correct number of equally-sized data files (up to 8, one per logical CPU core) and that autogrowth is configured in fixed MB increments, not percentage.

Prevention

• Deploy sp_whoisactive as a scheduled SQL Agent job (every 1–5 minutes) logging to a table, to enable post-incident root cause analysis without requiring real-time observation.
• Set up SQL Server alerts on TempDB data file auto-growth events (using SQL Server Agent alerts on error 1101/1105 or Performance Monitor thresholds) to catch growth early.
• Pre-size TempDB data and log files to an appropriate initial size reflecting peak workload, with autogrowth as a safety net only, to prevent file fragmentation and repeated baseline resetting.
• Configure TempDB with multiple equally-sized data files (one per logical CPU core up to 8) to reduce SGAM/PFS allocation contention under concurrent workloads.
• Perform regular index tuning reviews (missing index DMVs, index usage stats) to reduce sort spill and hash join operations that are the primary source of internal object TempDB consumption.
• Review and test any new complex reporting or ETL queries in a non-production environment before deployment, specifically checking execution plans for spill warnings under realistic data volumes.
• If RCSI or snapshot isolation is enabled, monitor version store growth separately via sys.dm_tran_version_store and ensure long-running transactions are not inflating the version store.
• Periodically audit TempDB file sizes and reset to target initial sizes during maintenance windows before they grow to inflated auto-expanded values.

Tools

• sys.dm_db_file_space_usage
• sys.dm_db_session_space_usage
• sys.dm_db_task_space_usage
• sys.dm_exec_sessions
• sys.dm_exec_requests
• sys.dm_exec_sql_text
• sys.dm_exec_connections
• sys.dm_exec_cached_plans
• sp_whoisactive (Adam Machanic)
• SQL Server Agent (scheduled logging job)
• SSMS Actual Execution Plan viewer
• DBCC SHRINKFILE
• DBCC FREEPROCCACHE

References

• How to find the SQL statements that caused TempDB growth? – DBA Stack Exchange
• sp_whoisactive – Adam Machanic (Official)
• Monitoring TempDB Usage – Microsoft Docs
• Optimising TempDB Configuration – Microsoft SQL Server Best Practices