$ guides / microsoft-sql-server / microsoft-sql-server-tempdb-file-configuration ▌

Operations Guides

SQL Server TempDB file configuration: one data file per CPU and why it matters

TempDB is the shared scratchpad every database on a SQL Server instance writes to. Temp tables, table variables, sort and hash spills, row version stores for RCSI and AlwaysOn readable secondaries, and internal worktables all land here. When TempDB serializes, every database on the instance serializes with it. The most common serialization point is not space or I/O. It is logical latch contention on a handful of allocation bitmap pages.

The standard guidance is to create multiple TempDB data files. The rule most operators learn is “one data file per logical CPU.” That rule is correct as a starting point but incomplete. The file count, the file sizing, the autogrowth configuration, and the SQL Server version all change what the right number actually is.

This is the configuration side of TempDB contention. For active allocation latch waits in production, see the wait-statistics and diagnosis material in this guide cluster.

What it is and why it matters

Microsoft’s baseline guidance is:

If the instance has 8 or fewer logical CPUs, create one TempDB data file per logical CPU.
If the instance has more than 8 logical CPUs, start with 8 data files.
If allocation latch contention persists after that, add files in groups of 4, up to the number of logical CPUs.

The rule exists because of allocation page contention. SQL Server tracks free space in every data file using three bitmap page types: PFS (Page Free Space), GAM (Global Allocation Map), and SGAM (Shared Global Allocation Map). Every allocation or deallocation of an extent or page requires an in-memory latch update on the relevant bitmap page. With a single TempDB data file, every concurrent temp object creation, every sort spill, and every version store row update lines up on the same PFS, GAM, and SGAM pages. The latches are held for microseconds, but at thousands of allocations per second the queue becomes the bottleneck.

The symptom is not a clean error. It is throughput that will not climb no matter how much CPU, memory, or I/O headroom you add. PAGELATCH_UP and PAGELATCH_EX waits on pages in database ID 2 (TempDB) dominate the wait statistics. CPU utilization looks moderate. Disk latency looks fine. The server is idle and slow at the same time.

How it works

Multiple TempDB data files work because each file has its own allocation bitmap pages. Two files means two PFS page sets, two GAM page sets, two SGAM page sets. The latch contention that was concentrated on one set of pages is now spread across N sets. This is the entire mechanism. The file count is a serialization dilution strategy.

The allocation engine uses a proportional-fill algorithm. When SQL Server needs to write to TempDB, it does not round-robin across files. It writes to the file with the most free space, weighted proportionally. If all files are the same size with the same amount of free space, allocations distribute evenly across them. If one file is larger than the others, it receives a disproportionate share of new allocations because it has more free space.

This is why equal sizing is not a recommendation. It is the precondition that makes multiple files reduce contention. Unequal files defeat the algorithm.

flowchart TD
    A[Concurrent temp object creation] --> B[Each allocation needs a PFS/GAM page update]
    B --> C{TempDB file count}
    C -->|1 file| D[All updates serialize on the same bitmap pages]
    C -->|N equal files| E[Updates spread across N bitmap page sets]
    D --> F[PAGELATCH_UP/EX waits on database ID 2]
    E --> G[Latch contention distributed across files]
    F --> H[Throughput throttled without errors]
    G --> I[Allocation scales with file count]

Adding files does not make TempDB faster in the I/O sense. It removes a logical serialization point so the existing I/O and CPU can actually be used.

Where it shows up in production

TempDB contention is a high-concurrency OLTP problem. It appears when many sessions simultaneously create and drop temp objects, spill sorts or hashes, or generate row versions. The classic triggers:

Heavy temp table use in stored procedures. Temp tables created and dropped per request, especially under high request rates, hammer the allocation pages.
RCSI or snapshot isolation enabled. Every read transaction under Read Committed Snapshot Isolation generates row versions in TempDB. Every update generates the before-image version. The version store is append-heavy and allocation-intensive.
AlwaysOn readable secondaries. Readable secondaries use snapshot isolation, so they generate TempDB version store rows on the secondary side.
Massive sort or hash spills. A query with a badly underestimated memory grant spills a multi-gigabyte sort or hash into TempDB. One query can consume gigabytes and trigger autogrowth.

The pattern that catches teams off guard is the post-restart slowdown. TempDB is recreated on every SQL Server restart, at the configured file sizes recorded in sys.master_files (tempdb growth is not reflected in that view, so a grown file returns to its configured size). If TempDB was never pre-sized for the workload, the first big workload after a restart forces autogrowth events. Every autogrowth event pauses I/O to that file while the new space is initialized. Log file autogrowth is especially expensive because instant file initialization does not apply to log files. A cluster of autogrowth events on the first morning after a patching reboot looks like a mysterious performance regression.

Tradeoffs and when to use it

The one-file-per-CPU rule is a safe baseline, not a law. Several refinements matter in production.

Equal size and equal growth are non-negotiable. Every TempDB data file must have the same initial size and the same autogrowth increment, specified in fixed megabytes, not percent. If files start equal but one autogrows first (because growth increments are in percent, or because someone added a file at a different size), proportional fill starts concentrating allocations on the larger file. The new files stop absorbing work. You end up with N files but the contention profile of 1.

Pre-size TempDB for the workload. Configure the initial file sizes large enough that autogrowth never fires during normal operations. Ensure instant file initialization is enabled so data file autogrowth does not stall on zero-initialization. The goal is to make autogrowth a safety net, not a routine occurrence. A common operational target is 25 to 30 percent free space under peak load.

The one-file-per-core ceiling is not always 8. Microsoft’s “start with 8, add in groups of 4” guidance assumes typical OLTP concurrency. On very high core count systems, blindly creating one file per core creates hundreds of files, introducing its own overhead in allocation tracking and file management. Community guidance, notably from Paul Randal, suggests one quarter to one half of the logical CPU count is often sufficient on modern SQL Server versions where several allocation contention sources have been removed at the engine level. The right workflow is to start with the Microsoft baseline, measure PAGELATCH waits on TempDB pages, and add files only when the measurement justifies it.

Adding files does not retroactively fix unequal sizing. If existing TempDB files have grown through autogrowth to different sizes, adding new files at the original configured size does nothing useful. The new files are smaller, proportional fill concentrates on the larger existing files, and the new files contribute almost no contention relief. When adding files to a system that has already autogrown unevenly, size the new files to match the current grown size of the existing files, not the original configured size.

Storage placement matters. TempDB should live on the fastest storage available. It is a write-heavy workload with random I/O patterns from spills and version store appends. On local SSD or NVMe, this is straightforward. On shared SAN storage, TempDB competes with data and log I/O. On cloud VMs, TempDB on the OS disk or a slow data disk is a common misconfiguration that caps the whole instance. Splitting TempDB files across multiple physical volumes can improve I/O parallelism, but only after the file count and sizing are correct.

Version-specific improvements change the calculus. Several SQL Server releases have reduced the allocation contention that multiple files exist to mitigate:

SQL Server 2016 and later. Trace flags 1117 (uniform autogrowth across files in a filegroup) and 1118 (uniform extent allocation instead of mixed extents) became default behavior for TempDB. Do not enable these trace flags for TempDB on SQL Server 2016 or newer. They are only relevant for TempDB on SQL Server 2014 and earlier.
SQL Server 2019 and later. Concurrent PFS page updates are enabled by default, reducing PFS latch contention directly.
SQL Server 2022 and later. GAM and SGAM page updates became concurrent, using shared-latch updates similar to the concurrent PFS updates introduced in SQL Server 2019, significantly improving high-concurrency TempDB allocation throughput.
SQL Server 2019 and later. Memory-optimized TempDB metadata removes metadata latch contention on system tables but does not address GAM or SGAM page contention. These are separate problems with separate fixes.

On SQL Server 2022 and later, the allocation latch problem is substantially smaller than on older versions. The file-count rule still applies as a baseline, but the threshold at which adding more files produces measurable improvement is higher (the SQL Server 2019+ concurrent PFS and SQL Server 2022 concurrent GAM/SGAM updates are engine-level fixes that postdate Paul Randal’s one-quarter-to-one-half recommendation).

Verify your configuration. After applying any changes, confirm the result:

SELECT name, size/128.0 AS current_size_mb, growth, is_percent_growth
FROM sys.master_files WHERE database_id = 2 ORDER BY file_id;

Every data file should show the same current_size_mb, the same growth value, and is_percent_growth = 0.

Signals to watch in production

Signal	Why it matters	Warning sign
`PAGELATCH_UP` and `PAGELATCH_EX` waits with `resource_description` matching `2:%`	Direct evidence of allocation page latch contention in TempDB (database ID 2)	Sustained waits above 5 percent of total wait time, correlated with a throughput ceiling
TempDB free space from `sys.dm_db_file_space_usage`	TempDB exhaustion halts queries across all databases	Below 20 percent under normal load, below 10 percent urgent
Version store size from `sys.dm_db_file_space_usage`	RCSI and snapshot isolation push version store rows into TempDB. Long-running transactions prevent cleanup.	Version store above 50 percent of TempDB usage indicates a long-running transaction holding the version store open
Autogrowth events from the default trace	Autogrowth pauses I/O to the file while initializing new space. Log file autogrowth is especially expensive.	Any autogrowth event during business hours on a production instance
TempDB I/O latency per file from `sys.dm_io_virtual_file_stats`	TempDB on slow storage caps every spill, version store append, and temp table operation	Average read or write latency above 20ms sustained
`RESOURCE_SEMAPHORE` waits and memory grants pending	Memory grant pressure causes queries to spill to TempDB. Spill volume is proportional to the grant shortfall.	Any sustained nonzero memory grants pending

How Netdata helps

Correlate PAGELATCH waits with throughput. Netdata collects SQL Server wait statistics at per-second granularity. A sustained rise in PAGELATCH_UP or PAGELATCH_EX waits on database ID 2 pages, with no corresponding rise in CPU or I/O utilization, is the signature of TempDB allocation contention. Per-second resolution matters because latch contention spikes are short and easy to miss with 60-second polling.
Track TempDB space by consumer. User objects, internal objects, and version store are distinct failure modes. A growing version store points to long-running snapshot transactions. Growing internal objects point to spill-heavy queries. Netdata surfaces the breakdown so you do not chase the wrong consumer.
Detect autogrowth events as they happen. Because autogrowth pauses I/O and is a leading indicator of under-sized files, alerting on autogrowth events catches misconfiguration before users report latency spikes.
Watch version-specific behavior change. If you upgrade from SQL Server 2017 to SQL Server 2022, the latch-free GAM pages should reduce PAGELATCH contention. Tracking the wait profile before and after the upgrade confirms the improvement rather than assuming it.
Correlate TempDB I/O latency with spill activity. High TempDB I/O latency combined with high internal object allocation is the signature of memory grant undersizing. Netdata’s per-second collection lets you align I/O latency spikes with the workload that caused them.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

The Netdata solution

Microsoft SQL Server monitoring with Netdata

Netdata monitors SQL Server with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate wait statistics, blocking chains, transaction-log and TempDB pressure, Page Life Expectancy, memory grants, per-file I/O stalls, and AlwaysOn send/redo queues against the rest of your stack so you catch the incidents in these runbooks before they page anyone.

See SQL Server monitoring → Start monitoring free