What is tempdb used for, and why can it be a bottleneck?
Quick Answer
tempdb is a shared system database used for temp tables and table variables, sort/hash spills when a query needs more memory than it got, worktables, cursors, online index rebuilds, and the version store for snapshot isolation / RCSI. Because every database on the instance shares it, heavy temp usage or allocation-page contention can slow everything.
tempdb is the instance's scratch space. It holds your temp tables and table variables, but also a lot of internal stuff: when a sort or hash join underestimates memory it spills to tempdb, cursors and worktables live there, and the row-version store for snapshot isolation and read-committed-snapshot is there too. Since it's one database shared by the whole instance, it becomes a contention point — either allocation-page latch contention, fixed by having several equally sized data files, or spills from bad cardinality estimates. It's rebuilt at every restart so nothing permanent goes in it.
- Allocation contention (
PAGELATCHon PFS/GAM/SGAM) is mitigated by multiple equally-sized data files (rule of thumb: one per core up to 8) — this is default in SQL Server 2016+. - Spills show as a warning in the execution plan — usually a bad row estimate or missing index.
- Enabling RCSI moves row versions into
tempdb— size and monitor it. tempdbis recreated on every restart; never store anything permanent.