What Is tempdb, and Why Everyone Shares It

tempdb is the scratch pad of a SQL Server instance. Every database on that instance uses the same one. It is thrown away and rebuilt every time the service starts, and a surprising amount of what SQL Server does quietly ends up in it.

One Bench, Everybody’s Job

Picture a workshop where everyone has a private cupboard and there is one shared bench in the middle. You do your messy cutting on the bench and clear it when you are done.

That is tempdb. Your databases are the cupboards. The bench belongs to nobody, everybody uses it, and when one person spreads out across the whole thing the rest have to wait.

What Ends Up There

Three kinds of thing, and only the first is obvious.

Things you created. Temporary tables, table variables, anything starting with a hash. This is the part people know about.

Things SQL Server created without telling you. A sort too big for memory spills to tempdb. So does a hash join, a cursor, an index rebuild with SORT_IN_TEMPDB. You did not ask and it happened anyway.

Row versions. If a database uses read committed snapshot isolation, the previous version of every changed row lives in tempdb until nobody needs it. Turning that setting on moves real work onto the shared bench.

This shows the split on your own server:

SELECT SUM(unallocated_extent_page_count) * 8 / 1024 AS free_mb,
       SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
       SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_mb,
       SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb
FROM tempdb.sys.dm_db_file_space_usage;

free_mb  user_objects_mb  internal_mb  version_store_mb
3131     1                0            0

That is my development machine sitting quietly. On a busy server those middle columns are where the story is. Internal objects growing means queries are spilling. Version store growing means snapshot isolation is working hard, or a long transaction is stopping old versions from being cleaned up.

It Is Rebuilt Every Restart

When SQL Server starts, tempdb is created fresh from the model database. Whatever was in it is gone.

Two consequences follow. You can never back it up or restore it, and there is no point trying. And if tempdb grew to 200 GB last Tuesday, it comes back at its configured starting size and has to grow all over again, which is slow while it happens.

That is why the configured size matters. Set it to roughly what the server actually uses, so it starts there instead of climbing every morning.

Why the File Count Comes Up

tempdb gets created and dropped constantly, and every creation touches a small number of allocation pages at the front of a data file. With one file, every session queues for those same pages. The wait shows as PAGELATCH on tempdb, and it looks baffling because no disk is busy. It is a queue for a page in memory.

More data files means more sets of those pages and a shorter queue. My instance has eight:

SELECT COUNT(*) AS tempdb_data_files
FROM tempdb.sys.database_files WHERE type = 0;

The usual guidance is one file per processor core up to eight, then add more only if you still see the wait. Modern SQL Server setup asks you at installation and picks a sensible number, which is why this is less of a problem than it was ten years ago. All the files must be the same size and grow by the same amount, or SQL Server will favour the largest one and you lose the benefit.

Who Is Using It Right Now

SELECT s.session_id, s.login_name, s.host_name,
       (u.user_objects_alloc_page_count - u.user_objects_dealloc_page_count) * 8 / 1024 AS user_mb,
       (u.internal_objects_alloc_page_count - u.internal_objects_dealloc_page_count) * 8 / 1024 AS internal_mb
FROM sys.dm_db_session_space_usage AS u
JOIN sys.dm_exec_sessions AS s ON s.session_id = u.session_id
WHERE u.user_objects_alloc_page_count + u.internal_objects_alloc_page_count > 0
ORDER BY internal_mb DESC;

When tempdb fills up, this is the query that names the culprit. A session with a large internal number is spilling, which usually means a bad row estimate rather than a bad query.

What I Check First

Is it on fast storage, because everything shares it. Is it sized for what the server really uses, so it does not grow every day. Does it have enough files. And is anything spilling regularly, because that is a query problem wearing a tempdb costume.

The thing to hold on to is that tempdb is shared. A report nobody cares about, spilling a sort every few minutes, slows down the order system that has nothing to do with it. When one database feels slow for no reason, the shared bench is worth a look.

tempdb is not a temporary database, it is the one table everybody in the building has to work on.

This post was rewritten from scratch in September 2026. The original, published on 2013-06-13, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.