A shared system database in SQL Server that stores temporary objects, intermediate query results, row version stores, and other transient data. TempDB is recreated from scratch every time SQL Server starts.
- Purpose: TempDB serves as SQL Server’s scratch space, storing temporary tables, table variables, cursors, spill operations, and row versions generated by snapshot isolation and other row versioning features.
- Contention: TempDB is often the most heavily contended system resource in busy SQL Server environments. Contention on allocation pages such as PFS, GAM, and SGAM can result in severe latch waits and reduced performance.
- Best practice: Configure one TempDB data file per logical CPU core, up to a maximum of eight files, with equal initial sizes to significantly reduce allocation page contention.
- File sizing: TempDB data files should be pre-sized to accommodate expected workloads and minimize autogrowth events, as file growth during peak activity can cause noticeable performance spikes.
- Metadata optimization: SQL Server 2019 introduced TempDB metadata optimization using in-memory metadata, significantly reducing latch contention related to TempDB system table access.
-
Version store monitoring: DBAs should monitor TempDB version store usage, especially when using
READ_COMMITTED_SNAPSHOTor snapshot isolation, since uncontrolled growth can consume all available TempDB storage. - Relevant Idera tools: SQL Diagnostic Manager monitors TempDB file sizes, space utilization, and latch contention, providing configurable alerts for potential issues.
- Related terms: Latch Wait, Disk Usage, In-Memory OLTP, Row Version Store, Snapshot Isolation.
