Here's How to Keep Tempdb From Becoming Your Worst Nightmare

In SQL Server, the tempdb database is the unsung hero behind countless operations—storing temporary tables, managing sorting and hashing for complex queries, and handling version stores for snapshot isolation. But when tempdb falters, your entire system can grind to a halt. Unexplained slowdowns, sudden space exhaustion, or blocking caused by allocation contention are just a few of the crises that can erupt if tempdb isn’t closely monitored. This guide is your tactical toolkit for diagnosing and resolving tempdb issues before they escalate into full-blown outages. You’ll learn how to: Pinpoint exactly what’s consuming space (user objects, internal operations, or version stores). Track hourly growth trends to catch runaway queries or misconfigured auto-grow settings. Identify the top offenders—whether it’s a specific user, query, or temporary object. Leverage built-in DMVs and queries to uncover hidden bottlenecks. 1. Check Current tempdb Usage and Space Allocation Query 1: Current tempdb Size and Free Space USE tempdb; GO SELECT name AS [File Name], physical_name AS [Physical Path], size/128.0 AS [Current Size (MB)], -- Converts 8KB pages to MB size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Free Space (MB)], growth AS [Growth (MB or %)] -- Growth setting (MB or percentage) FROM sys.database_files; What This Does: Lists all data and log files in tempdb, showing their current size, free space, and auto-growth settings. Why It Matters: Free Space: Helps identify if tempdb is close to capacity, which can trigger auto-growth events (a performance killer). Growth Settings: Reveals whether auto-growth is configured in fixed MB (recommended) or percentage (risky for large databases). Query 2: Space Usage by Component SELECT SUM(user_object_reserved_page_count)*8/1024 AS [User Objects (MB)], -- Temp tables, table variables SUM(internal_object_reserved_page_count)*8/1024 AS [Internal Objects (MB)], -- Sorts, hashes, spools SUM(version_store_reserved_page_count)*8/1024 AS [Version Store (MB)], -- Snapshot isolation versions SUM(unallocated_extent_page_count)*8/1024 AS [Free Space (MB)] -- Unused space FROM sys.dm_db_file_space_usage; What This Does: Breaks down tempdb space usage into user objects (e.g., temp tables), internal objects (query execution artifacts), and version store (snapshot isolation). Why It Matters: User Objects: Identify sessions creating excessive temporary tables. Internal Objects: High values indicate heavy query operations like sorting (ORDER BY) or grouping (GROUP BY). Version Store: Growth here suggests long-running transactions using snapshot isolation. 2. Hourly tempdb Growth Over Last 24 Hours DECLARE @current_trace NVARCHAR(500); SELECT @current_trace = path FROM sys.traces WHERE is_default = 1; SELECT tg.DatabaseName, tg.StartTime, tg.FileName, tg.Duration/1000 AS [Duration (ms)], -- Time taken for auto-growth tg.ApplicationName, tg.LoginName, (tg.IntegerData * 8)/1024 AS [Growth (MB)] -- Pages grown converted to MB FROM sys.fn_trace_gettable(@current_trace, DEFAULT) tg WHERE (tg.EventClass = 92 OR tg.EventClass = 93) -- 92: Data file grow, 93: Log file grow AND tg.DatabaseName = 'tempdb' AND tg.StartTime >= DATEADD(HOUR, -24, GETDATE()) ORDER BY tg.StartTime DESC; What This Does: Retrieves tempdb auto-growth events from the default trace over the last 24 hours. Why It Matters: Frequent auto-growth events indicate undersized tempdb files, leading to file fragmentation and I/O latency. Correlate growth events with LoginName or ApplicationName to pinpoint responsible workloads. 3. Identify Top tempdb Consumers by Session/User Query 1: Active Sessions Using tempdb SELECT es.session_id, es.login_name, es.program_name, es.host_name, (tsu.user_objects_alloc_page_count * 8)/1024 AS [User Objects (MB)], (tsu.internal_objects_alloc_page_count * 8)/1024 AS [Internal Objects (MB)] FROM sys.dm_db_session_space_usage tsu INNER JOIN sys.dm_exec_sessions es ON tsu.session_id = es.session_id ORDER BY [User Objects (MB)] + [Internal Objects (MB)] DESC; What This Does: Lists active sessions and their tempdb usage in MB. Why It Matters: User Objects: Temporary tables or table variables created by the session. Internal Objects: Query execution artifacts (e.g., worktables for sorting). Use this to terminate runaway queries or optimize heavy processes. Query 2: Top Queries Consuming tempdb SELECT er.session_id, es.login_name, er.start_time, er.status, er.command, (tsu.user_objects_alloc_page_count * 8)/1024 AS [User Objects (MB)], (tsu.internal_objects_alloc_page_count * 8)/1024 AS [Internal Objects (MB)], qt.text AS [Query Text] FROM sys.dm_db_task_space_usage tsu INNER JOIN sys.dm_exec_requests er ON tsu.session_id = er.session_id INNER JOIN sys.dm_exec_sessions es ON tsu.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) qt ORDER BY [User Objects (MB)] + [Internal Objects (MB)] DESC; What This Does: Identifies actively running queries and their tempdb consumption. Why It Matters: Links high tempdb usage to specific SQL statements (e.g., sorts, hashes, temp tables). Example: A query with ORDER BY on a large dataset may consume internal objects. 4. Identify Objects in tempdb SELECT t.name AS [Object Name], t.type_desc AS [Object Type], (au.total_pages * 8)/1024 AS [Size (MB)], au.type_desc AS [Allocation Type] FROM tempdb.sys.allocation_units au INNER JOIN tempdb.sys.partitions p ON au.container_id = p.hobt_id INNER JOIN tempdb.sys.tables t ON p.object_id = t.object_id WHERE au.type_desc IN ('IN_ROW_DATA', 'LOB_DATA', 'ROW_OVERFLOW_DATA'); What This Does: Lists temporary tables and their size in tempdb. Why It Matters: Identifies orphaned or oversized temp tables (e.g., #temp tables not dropped after use). LOB_DATA or ROW_OVERFLOW_DATA allocations indicate large object storage. 5. Monitor Version Store Usage SELECT (version_store_reserved_page_count * 8)/1024 AS [Version Store (MB)] FROM sys.dm_db_file_space_usage WHERE database_id = DB_ID('tempdb'); What This Does: Measures space used by the version store for snapshot isolation. Why It Matters: Long-running transactions with READ_COMMITTED_SNAPSHOT or SNAPSHOT ISOLATION can bloat the version store. Resolve by killing blocking transactions or reducing isolation levels. Common Issues & Fixes Issue Diagnosis Solution Excessive Internal Objects High internal_object_reserved_page_count. Optimize queries with ORDER BY/GROUP BY. Version Store Growth High version_store_reserved_page_count. Kill long-running transactions. User Objects Not Freed Persistent user_object_reserved_page_count. Ensure temp tables are dropped post-use. Frequent Auto-Growth Repeated events in default trace. Pre-size tempdb files. Proactive Tuning Recommendations Pre-Size tempdb: Initialize data files to 8 GB each to avoid auto-growth. Multiple Data Files: Create 4-8 data files (1 per logical CPU core) to reduce allocation contention. Avoid SELECT INTO: Use explicit schemas for temp tables to minimize logging. Enable Query Store: Use sys.query_store_runtime_stats to track tempdb-heavy queries. Conclusion: By systematically analyzing tempdb usage with these queries, you can preemptively address space issues, optimize query performance, and ensure smooth SQL Server operations. Regularly monitor key DMVs and automate alerts for tempdb growth to stay ahead of problems. In SQL Server, the tempdb database is the unsung hero behind countless operations—storing temporary tables, managing sorting and hashing for complex queries, and handling version stores for snapshot isolation. But when tempdb falters, your entire system can grind to a halt. Unexplained slowdowns, sudden space exhaustion, or blocking caused by allocation contention are just a few of the crises that can erupt if tempdb isn’t closely monitored. tempdb This guide is your tactical toolkit for diagnosing and resolving tempdb issues before they escalate into full-blown outages. You’ll learn how to: Pinpoint exactly what’s consuming space (user objects, internal operations, or version stores). Track hourly growth trends to catch runaway queries or misconfigured auto-grow settings. Identify the top offenders—whether it’s a specific user, query, or temporary object. Leverage built-in DMVs and queries to uncover hidden bottlenecks. Pinpoint exactly what’s consuming space (user objects, internal operations, or version stores). Pinpoint exactly what’s consuming space Track hourly growth trends to catch runaway queries or misconfigured auto-grow settings. Track hourly growth trends Identify the top offenders —whether it’s a specific user, query, or temporary object. Identify the top offenders Leverage built-in DMVs and queries to uncover hidden bottlenecks. Leverage built-in DMVs and queries 1. Check Current tempdb Usage and Space Allocation 1. Check Current tempdb Usage and Space Allocation Query 1: Current tempdb Size and Free Space Query 1: Current tempdb Size and Free Space USE tempdb; GO SELECT name AS [File Name], physical_name AS [Physical Path], size/128.0 AS [Current Size (MB)], -- Converts 8KB pages to MB size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Free Space (MB)], growth AS [Growth (MB or %)] -- Growth setting (MB or percentage) FROM sys.database_files; USE tempdb; GO SELECT name AS [File Name], physical_name AS [Physical Path], size/128.0 AS [Current Size (MB)], -- Converts 8KB pages to MB size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Free Space (MB)], growth AS [Growth (MB or %)] -- Growth setting (MB or percentage) FROM sys.database_files; What This Does : What This Does Lists all data and log files in tempdb, showing their current size, free space, and auto-growth settings. Why It Matters: Free Space: Helps identify if tempdb is close to capacity, which can trigger auto-growth events (a performance killer). Growth Settings: Reveals whether auto-growth is configured in fixed MB (recommended) or percentage (risky for large databases). Lists all data and log files in tempdb , showing their current size , free space , and auto-growth settings . tempdb current size free space auto-growth settings Why It Matters : Free Space: Helps identify if tempdb is close to capacity, which can trigger auto-growth events (a performance killer). Growth Settings: Reveals whether auto-growth is configured in fixed MB (recommended) or percentage (risky for large databases). Why It Matters Free Space: Helps identify if tempdb is close to capacity, which can trigger auto-growth events (a performance killer). Growth Settings: Reveals whether auto-growth is configured in fixed MB (recommended) or percentage (risky for large databases). Free Space : Helps identify if tempdb is close to capacity, which can trigger auto-growth events (a performance killer). Free Space tempdb Growth Settings : Reveals whether auto-growth is configured in fixed MB (recommended) or percentage (risky for large databases). Growth Settings Query 2: Space Usage by Component Query 2: Space Usage by Component SELECT SUM(user_object_reserved_page_count)*8/1024 AS [User Objects (MB)], -- Temp tables, table variables SUM(internal_object_reserved_page_count)*8/1024 AS [Internal Objects (MB)], -- Sorts, hashes, spools SUM(version_store_reserved_page_count)*8/1024 AS [Version Store (MB)], -- Snapshot isolation versions SUM(unallocated_extent_page_count)*8/1024 AS [Free Space (MB)] -- Unused space FROM sys.dm_db_file_space_usage; SELECT SUM(user_object_reserved_page_count)*8/1024 AS [User Objects (MB)], -- Temp tables, table variables SUM(internal_object_reserved_page_count)*8/1024 AS [Internal Objects (MB)], -- Sorts, hashes, spools SUM(version_store_reserved_page_count)*8/1024 AS [Version Store (MB)], -- Snapshot isolation versions SUM(unallocated_extent_page_count)*8/1024 AS [Free Space (MB)] -- Unused space FROM sys.dm_db_file_space_usage; What This Does : What This Does Breaks down tempdb space usage into user objects (e.g., temp tables), internal objects (query execution artifacts), and version store (snapshot isolation). Why It Matters: User Objects: Identify sessions creating excessive temporary tables. Internal Objects: High values indicate heavy query operations like sorting (ORDER BY) or grouping (GROUP BY). Version Store: Growth here suggests long-running transactions using snapshot isolation. Breaks down tempdb space usage into user objects (e.g., temp tables), internal objects (query execution artifacts), and version store (snapshot isolation). tempdb user objects internal objects version store Why It Matters : User Objects: Identify sessions creating excessive temporary tables. Internal Objects: High values indicate heavy query operations like sorting (ORDER BY) or grouping (GROUP BY). Version Store: Growth here suggests long-running transactions using snapshot isolation. Why It Matters User Objects: Identify sessions creating excessive temporary tables. Internal Objects: High values indicate heavy query operations like sorting (ORDER BY) or grouping (GROUP BY). Version Store: Growth here suggests long-running transactions using snapshot isolation. User Objects : Identify sessions creating excessive temporary tables. User Objects Internal Objects : High values indicate heavy query operations like sorting ( ORDER BY ) or grouping ( GROUP BY ). Internal Objects ORDER BY GROUP BY Version Store : Growth here suggests long-running transactions using snapshot isolation. Version Store 2. Hourly tempdb Growth Over Last 24 Hours 2. Hourly tempdb Growth Over Last 24 Hours DECLARE @current_trace NVARCHAR(500); SELECT @current_trace = path FROM sys.traces WHERE is_default = 1; SELECT tg.DatabaseName, tg.StartTime, tg.FileName, tg.Duration/1000 AS [Duration (ms)], -- Time taken for auto-growth tg.ApplicationName, tg.LoginName, (tg.IntegerData * 8)/1024 AS [Growth (MB)] -- Pages grown converted to MB FROM sys.fn_trace_gettable(@current_trace, DEFAULT) tg WHERE (tg.EventClass = 92 OR tg.EventClass = 93) -- 92: Data file grow, 93: Log file grow AND tg.DatabaseName = 'tempdb' AND tg.StartTime >= DATEADD(HOUR, -24, GETDATE()) ORDER BY tg.StartTime DESC; DECLARE @current_trace NVARCHAR(500); SELECT @current_trace = path FROM sys.traces WHERE is_default = 1; SELECT tg.DatabaseName, tg.StartTime, tg.FileName, tg.Duration/1000 AS [Duration (ms)], -- Time taken for auto-growth tg.ApplicationName, tg.LoginName, (tg.IntegerData * 8)/1024 AS [Growth (MB)] -- Pages grown converted to MB FROM sys.fn_trace_gettable(@current_trace, DEFAULT) tg WHERE (tg.EventClass = 92 OR tg.EventClass = 93) -- 92: Data file grow, 93: Log file grow AND tg.DatabaseName = 'tempdb' AND tg.StartTime >= DATEADD(HOUR, -24, GETDATE()) ORDER BY tg.StartTime DESC; What This Does : What This Does Retrieves tempdb auto-growth events from the default trace over the last 24 hours. Why It Matters: Frequent auto-growth events indicate undersized tempdb files, leading to file fragmentation and I/O latency. Correlate growth events with LoginName or ApplicationName to pinpoint responsible workloads. Retrieves tempdb auto-growth events from the default trace over the last 24 hours. tempdb default trace Why It Matters : Frequent auto-growth events indicate undersized tempdb files, leading to file fragmentation and I/O latency. Correlate growth events with LoginName or ApplicationName to pinpoint responsible workloads. Why It Matters Frequent auto-growth events indicate undersized tempdb files, leading to file fragmentation and I/O latency. Correlate growth events with LoginName or ApplicationName to pinpoint responsible workloads. Frequent auto-growth events indicate undersized tempdb files, leading to file fragmentation and I/O latency . tempdb file fragmentation I/O latency Correlate growth events with LoginName or ApplicationName to pinpoint responsible workloads. LoginName ApplicationName 3. Identify Top tempdb Consumers by Session/User 3. Identify Top tempdb Consumers by Session/User Query 1: Active Sessions Using tempdb Query 1: Active Sessions Using tempdb SELECT es.session_id, es.login_name, es.program_name, es.host_name, (tsu.user_objects_alloc_page_count * 8)/1024 AS [User Objects (MB)], (tsu.internal_objects_alloc_page_count * 8)/1024 AS [Internal Objects (MB)] FROM sys.dm_db_session_space_usage tsu INNER JOIN sys.dm_exec_sessions es ON tsu.session_id = es.session_id ORDER BY [User Objects (MB)] + [Internal Objects (MB)] DESC; SELECT es.session_id, es.login_name, es.program_name, es.host_name, (tsu.user_objects_alloc_page_count * 8)/1024 AS [User Objects (MB)], (tsu.internal_objects_alloc_page_count * 8)/1024 AS [Internal Objects (MB)] FROM sys.dm_db_session_space_usage tsu INNER JOIN sys.dm_exec_sessions es ON tsu.session_id = es.session_id ORDER BY [User Objects (MB)] + [Internal Objects (MB)] DESC; What This Does : What This Does Lists active sessions and their tempdb usage in MB. Why It Matters: User Objects: Temporary tables or table variables created by the session. Internal Objects: Query execution artifacts (e.g., worktables for sorting). Use this to terminate runaway queries or optimize heavy processes. Lists active sessions and their tempdb usage in MB. active sessions tempdb Why It Matters : User Objects: Temporary tables or table variables created by the session. Internal Objects: Query execution artifacts (e.g., worktables for sorting). Use this to terminate runaway queries or optimize heavy processes. Why It Matters User Objects: Temporary tables or table variables created by the session. Internal Objects: Query execution artifacts (e.g., worktables for sorting). Use this to terminate runaway queries or optimize heavy processes. User Objects : Temporary tables or table variables created by the session. User Objects Internal Objects : Query execution artifacts (e.g., worktables for sorting). Internal Objects Use this to terminate runaway queries or optimize heavy processes. Query 2: Top Queries Consuming tempdb Query 2: Top Queries Consuming tempdb SELECT er.session_id, es.login_name, er.start_time, er.status, er.command, (tsu.user_objects_alloc_page_count * 8)/1024 AS [User Objects (MB)], (tsu.internal_objects_alloc_page_count * 8)/1024 AS [Internal Objects (MB)], qt.text AS [Query Text] FROM sys.dm_db_task_space_usage tsu INNER JOIN sys.dm_exec_requests er ON tsu.session_id = er.session_id INNER JOIN sys.dm_exec_sessions es ON tsu.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) qt ORDER BY [User Objects (MB)] + [Internal Objects (MB)] DESC; SELECT er.session_id, es.login_name, er.start_time, er.status, er.command, (tsu.user_objects_alloc_page_count * 8)/1024 AS [User Objects (MB)], (tsu.internal_objects_alloc_page_count * 8)/1024 AS [Internal Objects (MB)], qt.text AS [Query Text] FROM sys.dm_db_task_space_usage tsu INNER JOIN sys.dm_exec_requests er ON tsu.session_id = er.session_id INNER JOIN sys.dm_exec_sessions es ON tsu.session_id = es.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) qt ORDER BY [User Objects (MB)] + [Internal Objects (MB)] DESC; What This Does : What This Does Identifies actively running queries and their tempdb consumption. Why It Matters: Links high tempdb usage to specific SQL statements (e.g., sorts, hashes, temp tables). Example: A query with ORDER BY on a large dataset may consume internal objects. Identifies actively running queries and their tempdb consumption. actively running queries tempdb Why It Matters : Links high tempdb usage to specific SQL statements (e.g., sorts, hashes, temp tables). Example: A query with ORDER BY on a large dataset may consume internal objects. Why It Matters Links high tempdb usage to specific SQL statements (e.g., sorts, hashes, temp tables). Example: A query with ORDER BY on a large dataset may consume internal objects. Links high tempdb usage to specific SQL statements (e.g., sorts, hashes, temp tables). tempdb Example: A query with ORDER BY on a large dataset may consume internal objects. ORDER BY 4. Identify Objects in tempdb 4. Identify Objects in tempdb SELECT t.name AS [Object Name], t.type_desc AS [Object Type], (au.total_pages * 8)/1024 AS [Size (MB)], au.type_desc AS [Allocation Type] FROM tempdb.sys.allocation_units au INNER JOIN tempdb.sys.partitions p ON au.container_id = p.hobt_id INNER JOIN tempdb.sys.tables t ON p.object_id = t.object_id WHERE au.type_desc IN ('IN_ROW_DATA', 'LOB_DATA', 'ROW_OVERFLOW_DATA'); SELECT t.name AS [Object Name], t.type_desc AS [Object Type], (au.total_pages * 8)/1024 AS [Size (MB)], au.type_desc AS [Allocation Type] FROM tempdb.sys.allocation_units au INNER JOIN tempdb.sys.partitions p ON au.container_id = p.hobt_id INNER JOIN tempdb.sys.tables t ON p.object_id = t.object_id WHERE au.type_desc IN ('IN_ROW_DATA', 'LOB_DATA', 'ROW_OVERFLOW_DATA'); What This Does : What This Does Lists temporary tables and their size in tempdb. Why It Matters: Identifies orphaned or oversized temp tables (e.g., #temp tables not dropped after use). LOB_DATA or ROW_OVERFLOW_DATA allocations indicate large object storage. Lists temporary tables and their size in tempdb . temporary tables tempdb Why It Matters : Identifies orphaned or oversized temp tables (e.g., #temp tables not dropped after use). LOB_DATA or ROW_OVERFLOW_DATA allocations indicate large object storage. Why It Matters Identifies orphaned or oversized temp tables (e.g., #temp tables not dropped after use). LOB_DATA or ROW_OVERFLOW_DATA allocations indicate large object storage. Identifies orphaned or oversized temp tables (e.g., #temp tables not dropped after use). #temp LOB_DATA or ROW_OVERFLOW_DATA allocations indicate large object storage. LOB_DATA ROW_OVERFLOW_DATA 5. Monitor Version Store Usage 5. Monitor Version Store Usage SELECT (version_store_reserved_page_count * 8)/1024 AS [Version Store (MB)] FROM sys.dm_db_file_space_usage WHERE database_id = DB_ID('tempdb'); SELECT (version_store_reserved_page_count * 8)/1024 AS [Version Store (MB)] FROM sys.dm_db_file_space_usage WHERE database_id = DB_ID('tempdb'); What This Does : What This Does Measures space used by the version store for snapshot isolation. Why It Matters: Long-running transactions with READ_COMMITTED_SNAPSHOT or SNAPSHOT ISOLATION can bloat the version store. Resolve by killing blocking transactions or reducing isolation levels. Measures space used by the version store for snapshot isolation. version store Why It Matters : Long-running transactions with READ_COMMITTED_SNAPSHOT or SNAPSHOT ISOLATION can bloat the version store. Resolve by killing blocking transactions or reducing isolation levels. Why It Matters Long-running transactions with READ_COMMITTED_SNAPSHOT or SNAPSHOT ISOLATION can bloat the version store. Resolve by killing blocking transactions or reducing isolation levels. Long-running transactions with READ_COMMITTED_SNAPSHOT or SNAPSHOT ISOLATION can bloat the version store. READ_COMMITTED_SNAPSHOT SNAPSHOT ISOLATION Resolve by killing blocking transactions or reducing isolation levels. Common Issues & Fixes Common Issues & Fixes Issue Diagnosis Solution Excessive Internal Objects High internal_object_reserved_page_count. Optimize queries with ORDER BY/GROUP BY. Version Store Growth High version_store_reserved_page_count. Kill long-running transactions. User Objects Not Freed Persistent user_object_reserved_page_count. Ensure temp tables are dropped post-use. Frequent Auto-Growth Repeated events in default trace. Pre-size tempdb files. Issue Diagnosis Solution Excessive Internal Objects High internal_object_reserved_page_count. Optimize queries with ORDER BY/GROUP BY. Version Store Growth High version_store_reserved_page_count. Kill long-running transactions. User Objects Not Freed Persistent user_object_reserved_page_count. Ensure temp tables are dropped post-use. Frequent Auto-Growth Repeated events in default trace. Pre-size tempdb files. Issue Diagnosis Solution Issue Issue Issue Diagnosis Diagnosis Diagnosis Solution Solution Solution Excessive Internal Objects High internal_object_reserved_page_count. Optimize queries with ORDER BY/GROUP BY. Excessive Internal Objects Excessive Internal Objects Excessive Internal Objects High internal_object_reserved_page_count. High internal_object_reserved_page_count . internal_object_reserved_page_count Optimize queries with ORDER BY/GROUP BY. Optimize queries with ORDER BY / GROUP BY . ORDER BY GROUP BY Version Store Growth High version_store_reserved_page_count. Kill long-running transactions. Version Store Growth Version Store Growth Version Store Growth High version_store_reserved_page_count. High version_store_reserved_page_count . version_store_reserved_page_count Kill long-running transactions. Kill long-running transactions. User Objects Not Freed Persistent user_object_reserved_page_count. Ensure temp tables are dropped post-use. User Objects Not Freed User Objects Not Freed User Objects Not Freed Persistent user_object_reserved_page_count. Persistent user_object_reserved_page_count . user_object_reserved_page_count Ensure temp tables are dropped post-use. Ensure temp tables are dropped post-use. Frequent Auto-Growth Repeated events in default trace. Pre-size tempdb files. Frequent Auto-Growth Frequent Auto-Growth Frequent Auto-Growth Repeated events in default trace. Repeated events in default trace. Pre-size tempdb files. Pre-size tempdb files. tempdb Proactive Tuning Recommendations Proactive Tuning Recommendations Pre-Size tempdb: Initialize data files to 8 GB each to avoid auto-growth. Multiple Data Files: Create 4-8 data files (1 per logical CPU core) to reduce allocation contention. Avoid SELECT INTO: Use explicit schemas for temp tables to minimize logging. Enable Query Store: Use sys.query_store_runtime_stats to track tempdb-heavy queries. Pre-Size tempdb : Initialize data files to 8 GB each to avoid auto-growth. Pre-Size tempdb Multiple Data Files : Create 4-8 data files (1 per logical CPU core) to reduce allocation contention. Multiple Data Files Avoid SELECT INTO : Use explicit schemas for temp tables to minimize logging. Avoid SELECT INTO Enable Query Store : Use sys.query_store_runtime_stats to track tempdb-heavy queries. Enable Query Store sys.query_store_runtime_stats Conclusion: Conclusion: By systematically analyzing tempdb usage with these queries, you can preemptively address space issues, optimize query performance, and ensure smooth SQL Server operations. Regularly monitor key DMVs and automate alerts for tempdb growth to stay ahead of problems. tempdb tempdb