TempDB: The Unsung Hero Living in Your SQL Server's Basement

You know that friend who lets everyone crash at their place after parties? That's the tempdb.  It's SQL Server's communal workspace, shared playground, and sometimes... total chaos zone.

Why TempDB is Having All the Fun (And All the Problems)

Unlike your user databases with their carefully planned schemas, tempdb is like a Vegas hotel room: what happens there should stay there, everything resets on checkout (restart)... and sometimes you find some really weird stuff left behind.

Every single session on your SQL Server shares this ONE database. It's handling:

• Your temporary tables (#temp) and table variables (@table)
• Sort operations that spill from memory
• Row versioning for snapshot isolation
• Internal worktables for spools, hashing, and sorting
• DBCC CHECKDB operations
• Online index builds

The '8 Files or Bust' Rule

Here's the deal: tempdb defaults to one data file. That's like having one bathroom at a Metallica concert.  It's going to get ugly.

The golden rule: Configure one tempdb data file per CPU core, up to 8 files. Beyond 8, you're probably not gaining much unless you are in a very specific high-contention scenario.

Why? SQL Server uses a proportional fill algorithm, spreading writes across files. More files = more GAM/SGAM pages = less allocation contention.  It's beautiful when it works.

-- Are your tempdb files sized equally?
SELECT name, size/128.0 AS size_mb, growth
FROM tempdb.sys.database_files
WHERE type_desc = 'ROWS';

-- Who's hogging tempdb space RIGHT NOW?
SELECT 
    s.session_id,
    DB_NAME(r.database_id) AS database_name,
    CAST(SUM(u.user_objects_alloc_page_count) * 8.0/1024 AS DECIMAL(10,2)) AS user_objects_mb,
    CAST(SUM(u.internal_objects_alloc_page_count) * 8.0/1024 AS DECIMAL(10,2)) AS internal_objects_mb
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_db_task_space_usage u ON r.session_id = u.session_id
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE u.user_objects_alloc_page_count > 0 
   OR u.internal_objects_alloc_page_count > 0
GROUP BY s.session_id, r.database_id
ORDER BY (u.user_objects_alloc_page_count + u.internal_objects_alloc_page_count) DESC;

Pro Tips That'll Save Your Bacon

1. Size all files identically - If one file is 1GB and another is 10GB, SQL Server will hammer that smaller file trying to keep proportions.  Don't do this to yourself.

2. Set reasonable autogrowth - 64MB or 10% on a 500GB file?  Please.  Use 256MB for data and 128MB for log files.  Autogrowth events are expensive.

3. Instant file initialization is your friend - Enable it at the Windows level. Tempdb creates new files on every restart, and you don't want to wait for Windows to zero out 100GB.

4. Put it on fast storage - Tempdb is high-throughput, low-durability. SSDs are perfect.  It doesn't need to be on your expensive SAN.

Next time someone says 'the database is slow', check tempdb first:

-- What's waiting on tempdb?
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGE%LATCH%'
   OR wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

If you see PAGELATCH_UP or PAGELATCH_EX waits on 2:1:1 or 2:1:3 (PFS and SGAM pages), you've got allocation contention.  Time to add more files.

The Bottom Line

TempDB is the workhorse of your SQL Server instance. Treat her right: give her more space than she needs, multiple files, fast disk, and for the love of all things holy, size those files equally.

Your future self (and your users) will thank you.  I promise.  

More to read

Brent Ozar: Tempdb Cheat Sheet: How to configure tempdb

Brent Ozar: How to Tell if You Need More Tempdb Files

MSFT: The tempdb