Here’s How to Keep Tempdb From Becoming Your Worst Nightmare | HackerNoon

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

Proactive Tuning Recommendations

1. Pre-Size tempdb: Initialize data files to 8 GB each to avoid auto-growth.
2. Multiple Data Files: Create 4-8 data files (1 per logical CPU core) to reduce allocation contention.
3. Avoid SELECT INTO: Use explicit schemas for temp tables to minimize logging.
4. 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.