TempDB is the workbench of SQL Server — every query, sort, join, and temporary object touches it. Misconfiguring tempdb can slow down your entire server.
⚖️ Tradeoffs (Think: Shared, Temporary, Busy)
Shared by all sessions → everyone uses it.
Temporary → cleared at every restart.
Busy → heavy workloads can overload it.
No recovery → always in simple mode.
Performance risk → misconfiguration = bottlenecks.
🔧 Optimizations (Think: 8 Equal Fast Monitored)
8 files max → one per CPU core, up to 8.
Equal size & growth → balance usage.
Fixed growth → MB increments, not %.
Pre-size → avoid autogrowth storms.
Fast storage → SSD/NVMe preferred.
Monitor → watch PAGELATCH waits.
Tune queries → reduce spills & temp tables.
`/* TempDB Space Usage */
SELECT
df.file_id, df.name, df.type_desc,
(df.size * 8 / 1024) AS SizeMB,
fsu.unallocated_extent_page_count * 8 / 1024 AS FreeSpaceMB,
fsu.version_store_reserved_page_count * 8 / 1024 AS VersionStoreMB,
fsu.user_object_reserved_page_count * 8 / 1024 AS UserObjectsMB,
fsu.internal_object_reserved_page_count * 8 / 1024 AS InternalObjectsMB
FROM tempdb.sys.database_files df
JOIN sys.dm_db_file_space_usage fsu ON df.file_id = fsu.file_id;
/* Contention Check */
SELECT wait_type, waiting_tasks_count, wait_time_ms/1000.0 AS WaitTimeSec
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;
`
Top comments (0)