What is tempdb used for in SQL Server?
Quick Answer
tempdb is a shared, recreated-on-restart system database used for anything temporary: user #temp tables and table variables, work tables for sorts/hashes/spools, the row-version store for snapshot isolation and triggers, and query “spills” when a sort or hash doesn't fit in granted memory. It's a common performance bottleneck because every session shares it.
tempdb is the scratch space for the whole instance — everyone shares it. It holds your temp tables and table variables, but also internal work tables when a query has to sort or hash more than fits in memory, and the version store for snapshot isolation. Because it's shared, it's a classic bottleneck. Two things I watch for: allocation-page contention when there's lots of tiny temp-object churn, which you fix with multiple tempdb data files; and spill warnings in execution plans, which mean a sort or hash ran out of granted memory, usually because the row estimates were off.
- Contention on allocation pages (PFS/GAM/SGAM) with many concurrent small temp objects — mitigated by having multiple equally-sized tempdb data files (roughly 1 per core up to 8) and enabling relevant trace flags / defaults (on by default in 2016+).
- Spills — a Sort or Hash Match operator with a “spill to tempdb” warning in the plan means the memory grant was too small (often from bad estimates / stale stats).
- Version store growth — long-running transactions under RCSI/SNAPSHOT keep old row versions and can fill tempdb.
- Put tempdb on fast storage; size it up front so it doesn't autogrow under load.