Internal architecture of TempDB

The internal architecture of TempDB is largely the same as a user database, using the same page and extent structures for data storage. The key differences lie in its non-durable nature, minimal logging, and a set of operational restrictions that optimize it for temporary, high-concurrency workloads. 

TempDB is a globally shared system database used by SQL Server to store temporary objects, intermediate query results, worktables, version store, and spill data. It is structurally similar to a user database but behaves differently internally — it is recreated on every SQL restart, always uses Simple Recovery Model, supports minimal logging, does not support backup/restore, and does not perform crash recovery. TempDB performance is critical because almost every workload uses it; therefore, multiple data files, metadata optimizations, and caching mechanisms are used to reduce contention.

Below objects TempDB Store Internally

Ø  #Temp Tables

Ø   ##Global Temp Tables

Ø   @Table Variables (metadata + spills)

Ø   Table-Valued Parameters when spilled

Ø  Worktables created internally by SQL Server for

o   Sorting
o   Hash joins
o   Hash aggregates
o   Cursors
o   Spools
o   ORDER BY without index
o   DISTINCT
o   GROUP BY

Ø  Version Store

o   Read Committed Snapshot Isolation (RCSI)
o   Snapshot Isolation
o   Online Indexing
o   MARS

Ø  Row Overflow & Spill Data

o   Sort spill to disk
o   Hash spill to disk

o   Memory Grant overflow

TempDB is Re-created on every SQL Restart and System table copied from model database with Empty, clean state each startup and Space is not recovered per session; engine manages cleanup. TempDB uses the same storage structures as a normal DB like Pages, GAM, SGAM, PFS, IAM , DCM/BCM etc.

TempDB uses minimal logging because It never needs crash recovery. It never needs to rollback after restart. No point-in-time recovery. No backups allowed. TempDB is ALWAYS in Simple Recovery Model we cannot change it.

TempDB Internal Architecture vs User Database

Feature	TempDB	User Database
Persistence	Non-persistent	Persistent
Recreated on Restart	Yes	No
Recovery Model	Always Simple	Simple / Full / Bulk Logged
Logging	Minimal	Full logging based on recovery model
Crash Recovery	No	Yes
Backup Allowed	No	Yes
Restore Allowed	No	Yes
Shared Across Server	Yes	No
Supports Snapshot Isolation	Stores version store	Uses own version store
Read Only?	No	No
Metadata Caching	Optimized	Normal
Multiple Files Needed	Recommended	Optional
Performance Critical	EXTREMELY	Depends

TempDB functions as the SQL Server instance's global scratchpad, optimized for speed and temporary data management, whereas user databases are designed for permanent, reliable data storage.