TempDB

40.1 What Uses TempDB

User objects: #temp, ##temp, table variables Internal objects: sorts, hash spills, spools, cursors, DBCC CHECKDB

Version store: RCSI, snapshot isolation, online index operations, triggers (non-ADR)

40.2 Configuration Best Practices

Multiple equally sized data files (1 per logical core, up to 8, then add in groups of 4 if contention persists)

Fast dedicated storage, presized files, identical autogrowth

Memory-optimized tempdb metadata (2019+):

TempDB space resource governance (2025)

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

40.3 Monitoring TempDB

SELECT * FROM sys.dm_db_file_space_usage;
SELECT * FROM sys.dm_db_session_space_usage ORDER BY user_objects_alloc_page_count DESC;
SELECT * FROM sys.dm_tran_version_store_space_usage;
-- PAGELATCH_* waits on 2:1:1 (PFS), 2:1:2 (GAM), 2:1:3 (SGAM) indicate allocation contention