TEMPOBJ Wait Type Explained

TEMPOBJ waits emerge from SQL Server's internal temporary object lifecycle management within tempdb. When a temporary table, table variable, or other temporary object reaches end of scope, SQL Server must acquire exclusive access to the object's metadata structures before dropping it. This synchronization prevents race conditions where multiple sessions might attempt to access or modify the same temporary object simultaneously.

The wait specifically occurs in the tempdb allocation subsystem when the cleanup thread or user session needs to acquire an exclusive latch on temporary object metadata pages. SQL Server maintains internal hash tables and allocation structures for temporary objects that require serialized access during drop operations. Multiple concurrent requests to drop temporary objects can create a queue, manifesting as TEMPOBJ waits.

In SQL Server 2016 and later, tempdb's multiple data files and proportional fill algorithm reduce some contention, but TEMPOBJ waits can still occur during metadata synchronization regardless of data file configuration. The wait duration typically correlates with the complexity of temporary object cleanup operations and concurrent tempdb activity.

SQL Server 2019 introduced improvements to tempdb metadata handling with memory-optimized tempdb metadata (controlled by the MEMORY_OPTIMIZED_TEMPDB_METADATA setting), which reduces some allocation contention but does not eliminate TEMPOBJ waits entirely since object drops still require exclusive access coordination.

-- Check current TEMPOBJ wait statistics
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'TEMPOBJ';

-- Identify sessions currently experiencing TEMPOBJ waits
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    w.wait_type,
    w.wait_duration_ms,
    w.resource_description,
    r.command,
    r.status,
    t.text AS current_sql
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_sessions s ON w.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON w.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE w.wait_type = 'TEMPOBJ';

-- Monitor tempdb usage patterns that might correlate with TEMPOBJ waits
SELECT 
    session_id,
    SUM(user_objects_alloc_page_count) AS user_objects_pages,
    SUM(user_objects_dealloc_page_count) AS user_objects_dealloc_pages,
    SUM(internal_objects_alloc_page_count) AS internal_objects_pages,
    SUM(internal_objects_dealloc_page_count) AS internal_objects_dealloc_pages
FROM sys.dm_db_session_space_usage
GROUP BY session_id
HAVING SUM(user_objects_alloc_page_count) > 0 
    OR SUM(internal_objects_alloc_page_count) > 0
ORDER BY user_objects_pages DESC;

-- Check for blocking chains involving temporary objects
SELECT 
    blocking_session_id,
    session_id,
    wait_type,
    wait_resource,
    wait_time,
    command,
    status
FROM sys.dm_exec_requests
WHERE blocking_session_id IS NOT NULL
    AND (wait_type LIKE '%TEMP%' OR resource_description LIKE '%tempdb%');

-- Review tempdb file configuration and usage
SELECT 
    f.name AS file_name,
    f.physical_name,
    f.size * 8 / 1024 AS size_mb,
    f.growth,
    f.is_percent_growth,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms
FROM sys.database_files f
INNER JOIN sys.dm_io_virtual_file_stats(2, NULL) vfs ON f.file_id = vfs.file_id
WHERE f.type = 0; -- Data files only

Enable Memory-Optimized TempDB Metadata (SQL Server 2019+)

This reduces allocation contention in tempdb metadata structures, potentially reducing TEMPOBJ wait frequency.

-- WARNING: This requires a SQL Server service restart
-- Test thoroughly in development environment first
-- Check if already enabled
SELECT name, value, value_in_use 
FROM sys.configurations 
WHERE name = 'tempdb metadata memory-optimized';

-- Enable if not already configured
-- ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
-- Restart SQL Server service after enabling

Optimize TempDB Data File Configuration

Ensures proper tempdb file distribution to reduce allocation contention.

-- Check current tempdb file configuration
USE tempdb;
SELECT 
    f.name,
    f.size * 8 / 1024 AS size_mb,
    f.growth,
    f.is_percent_growth
FROM sys.database_files f
WHERE f.type = 0;

-- Add additional tempdb data files if CPU count > file count
-- Rule of thumb: 1 file per CPU core up to 8 files
-- Each file should be same initial size and growth settings
/*
ALTER DATABASE tempdb 
ADD FILE (
    NAME = 'tempdev2',
    FILENAME = 'C:\YourPath\tempdb2.ndf',
    SIZE = 1024MB,
    FILEGROWTH = 256MB
);
*/

Monitor and Kill Long-Running TempDB Sessions

Identifies sessions holding temporary objects for extended periods.

-- Identify sessions with excessive temp object usage
-- Review before killing any sessions
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    s.last_request_start_time,
    tsu.user_objects_alloc_page_count * 8 / 1024 AS temp_objects_mb,
    r.command,
    r.status,
    r.wait_type,
    t.text
FROM sys.dm_db_session_space_usage tsu
INNER JOIN sys.dm_exec_sessions s ON tsu.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE tsu.user_objects_alloc_page_count > 1280 -- More than 10MB temp objects
    AND s.session_id > 50 -- Exclude system sessions
ORDER BY tsu.user_objects_alloc_page_count DESC;

-- Kill problematic session only after investigation
-- KILL [session_id];

Clear Wait Statistics for Fresh Monitoring

Resets wait statistics to establish new baseline for TEMPOBJ monitoring.

-- Clear wait statistics to establish new monitoring baseline
-- This affects all wait statistics, not just TEMPOBJ
-- Use during maintenance window or planned monitoring reset
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Verify TEMPOBJ waits are cleared
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'TEMPOBJ';

Configure tempdb with multiple data files equal to CPU core count (maximum 8 files) with identical initial sizes and growth settings. This reduces allocation contention that can contribute to temporary object synchronization delays. Enable memory-optimized tempdb metadata in SQL Server 2019+ environments after thorough testing.

Monitor applications creating excessive temporary objects, particularly those using temp tables in loops or high-frequency operations. Implement connection pooling to reduce session churn and temporary object creation overhead. Consider table variables for small datasets instead of temp tables when transaction scope requirements permit.

Establish baseline monitoring for TEMPOBJ waits during normal operations to identify unusual spikes. Configure alerts when TEMPOBJ wait time exceeds historical patterns. Review query plans for operations generating high temporary object usage, focusing on sort and hash operations that create internal temporary objects.

Place tempdb data files on fast storage subsystems with adequate IOPS capacity. Separate tempdb from user databases onto dedicated storage when possible. Monitor tempdb space usage patterns to prevent file growth events during peak temporary object creation periods.