Setting Up TempDB: Files, Placement, and Size

This article answers the three questions that come up at every installation: how many data files, where to put data and log, and how large. It is based on our own measurements on SQL Server 2017, 2019, 2022, and 2025.

Multiple data files: which problem do they solve?

Each data file manages its space through a few special pages: PFS pages record how full a page is, GAM and SGAM pages which extents are free. When a session creates a temporary table, SQL Server has to latch these pages briefly. If very many sessions do this at the same time, they queue up in front of the same few pages. This shows up as wait type PAGELATCH_UP or PAGELATCH_EX on resources that begin with 2:, the database ID of TempDB.

Several equally sized data files spread this work: each file has its own allocation pages, and SQL Server fills the files in round-robin fashion. That helps only against this kind of waiting. It does not help against full disks, slow storage, or spills.

SQL Server has improved here in several steps:

In the lab, 64 sessions concurrently created small temporary tables in a loop, filled, read, and dropped them, with 1, 4, and 8 equally sized data files. Throughput in batches per second:

Three findings:

• As long as metadata is the bottleneck, the number of files changes nothing, on any version. Almost all waiters were at pages of the system tables in which SQL Server records temporary objects, hardly any at the allocation pages.
• With memory-optimized metadata, the classic bottleneck comes to light. On SQL Server 2019, throughput rose considerably, and now the waits were on GAM pages. Now 8 files delivered 72 percent more than one.
• SQL Server 2022 and 2025 have also eased this bottleneck. With memory-optimized metadata, it ran as fast with one file as with eight.

Which bottleneck you have is shown by the wait type PAGELATCH_* together with the page in resource_description: if it is on a PFS, GAM, or SGAM page, more files help. If it is on pages of system tables, memory-optimized metadata (from SQL Server 2019) or creating and dropping fewer temporary objects helps. On the TempDB page, Microsoft describes a query that detects this metadata contention (see Sources).

What follows from this:

• Start with as many equally sized data files as logical processors, at most 8. On instances that were set up with older versions and upgraded later, there is often still a single file.
• If waiting on allocation pages in TempDB persists, increase the number step by step by 4. More files without demonstrated contention bring nothing.
• All files the same size and with the same growth. If one file grows alone, it receives most of the new allocations, and the distribution is gone.

Where to put data and log?

TempDB belongs on fast storage and separate from the data files and logs of user databases. Spills, large temporary tables, and the version store generate load that would otherwise compete with the application's reads and writes. In virtual machines in the cloud, a local, ephemeral disk is often a good fit for this: TempDB is recreated at every start anyway.

The TempDB log deserves a closer look. In a user database, every commit waits until its log records are on disk; only that way can the change be recovered after a crash. TempDB, however, is never recovered after a restart but recreated. On the TempDB page, Microsoft states that delayed durability is always active in TempDB, even if the database option is set to DISABLED; the page names no version. A commit with delayed durability does not wait for the log to be written. We measured whether this shows on all versions. Microsoft's recommendation is accordingly to put TempDB on fast storage and, when there are bottlenecks, to separate it from the user databases; it does not prescribe separating TempDB data and log.

In the lab, 16 sessions wrote small transactions of one row each, once into a #temp table, once into a table in a user database on the same storage:

Although sys.databases reports the value DISABLED for the delayed_durability option for TempDB, commits in TempDB practically never waited for the log on all four versions and ran 10 to 15 times as fast.

In a second experiment with a log-intensive load (1,000 rows per transaction), the TempDB log was once on its own SSD, once on the same SSD as the TempDB data: 980 versus 996 transactions per second. The individual writes were somewhat faster on the dedicated SSD (0.57 instead of 0.88 ms); throughput did not change.

What follows from this: A dedicated disk for the TempDB log is not a basic rule but a measure for instances on which a great deal is changed in TempDB, for example when filling large temporary tables or during long rollbacks. More important is that TempDB as a whole does not compete with the user databases for the same storage and that the log is also created large enough from the start.

How large does TempDB need to be?

The most widespread rule of thumb is: TempDB as large as the largest table. As a general rule, that is not true. TempDB fills through concurrent work: sorts and hash joins that spill, temporary tables of many sessions, the version store, and open transactions. This initially has nothing to do with the size of a single table.

In the lab, a query sorted a 196 MB table with a tight memory grant, so that the sort spilled to TempDB:

The largest table in this database was just under 2 GB. Twenty concurrent executions of a query over a small table needed twice as much TempDB as that. We limited the memory grant as a lab instrument; in production, the same picture arises from estimation errors or tight memory, typically in load peaks.

The rule has a kernel of truth nonetheless. Some maintenance tasks really do need TempDB on the order of one object:

Creating an index with SORT_IN_TEMPDB did need roughly the size of the index. Without the option, SQL Server sorts in the target database, and TempDB stays empty. The REBUILD of the existing index occupied no TempDB, presumably because it reads the rows already sorted from the old index. And with ample memory, both operations sorted entirely in memory; Microsoft describes that SORT_IN_TEMPDB is then ignored. The CHECKDB estimate was several times higher than the actual need.

The object thus sets an upper bound for individual maintenance steps, not a yardstick for TempDB as a whole.

For the size of TempDB, this means:

1. Measure instead of guess. Observe usage over a period that includes all typical loads: daily business, month-end close, maintenance windows with index maintenance and CHECKDB. The TempDB snapshot shows which area occupies how much; run regularly and saved, it yields the trend.
2. Peak plus a deliberately chosen buffer. Create the data files with equal size and pre-sized to what covers the measured peak with headroom.
3. Auto-growth as an emergency brake. A fixed growth in MB, the same for all files, prevents a standstill at an unexpected peak. It does not replace the planned size.
4. The planned size is the configured size. After a restart, SQL Server recreates TempDB at the configured size. In the lab, the data files had grown from 256 to 768 MB under load; after the restart they were 256 MB again. What arose through auto-growth must grow again after every restart, in the middle of the load.
5. Plan for maintenance. For index maintenance with SORT_IN_TEMPDB and for DBCC CHECKDB, estimate the need beforehand instead of sizing TempDB across the board on the largest object.

Checklist

Sources

• Microsoft: tempdb Database
• Microsoft: DBCC CHECKDB (Transact-SQL)
• Microsoft: SORT_IN_TEMPDB Option For Indexes

More TempDB topics