SQL Server TempDB Best Practices: Files, Sizing, Contention and Monitoring

Every database on a SQL Server instance shares a single tempdb. Temporary tables, sorts that spill out of memory, hash joins, row versions for snapshot isolation, online index rebuilds: all of it happens there. When tempdb is badly configured, the symptoms show up everywhere at once, as slow queries, blocking on allocation pages or a full disk that stops the whole instance.

This guide covers the tempdb best practices that matter on current SQL Server versions: how many data files to use, how to size them, where to put them, how to recognize and fix contention, what changed in SQL Server 2019, 2022 and 2025, and how to monitor tempdb space before it runs out.

What Lives in tempdb

tempdb holds three kinds of data:

• User objects: local and global temporary tables and their indexes, table variables, temporary stored procedures, cursors and tables returned by table-valued functions.
• Internal objects: work tables and work files the engine creates for spools, sorts, hash joins and hash aggregates, and index builds with SORT_IN_TEMPDB.
• The version store: row versions for READ_COMMITTED_SNAPSHOT and SNAPSHOT isolation, online index operations, MARS and AFTER triggers.

SQL Server re-creates tempdb empty every time the service starts, so it can’t be backed up or restored. That’s also why its configured size matters: after every restart, tempdb starts again from that size.

How Many tempdb Data Files Should You Have?

Multiple data files reduce contention on tempdb’s allocation pages. Microsoft’s general guidance is:

• With 8 or fewer logical processors, use the same number of data files as logical processors.
• With more than 8 logical processors, start with 8 data files.
• If allocation contention is still visible, add files in multiples of 4 until it drops to an acceptable level, or change the workload.

This query compares the recommended starting point with what the instance has today:

SELECT i.cpu_count                                   AS logical_processors,
       CASE WHEN i.cpu_count <= 8 THEN i.cpu_count ELSE 8 END AS recommended_start,
       f.data_files                                  AS current_data_files,
       f.min_size_mb,
       f.max_size_mb
FROM sys.dm_os_sys_info AS i
CROSS JOIN (SELECT COUNT(*)              AS data_files,
                   MIN(size) * 8 / 1024  AS min_size_mb,
                   MAX(size) * 8 / 1024  AS max_size_mb
            FROM tempdb.sys.database_files
            WHERE type_desc = N'ROWS') AS f;

If min_size_mb and max_size_mb differ, your data files aren’t the same size, which matters as much as their number.

To add files, create them with the same size and growth as the existing ones. New files are used immediately; no restart is needed:

-- Make the existing primary data file match the target size and growth
ALTER DATABASE tempdb
MODIFY FILE (NAME = N'tempdev', SIZE = 8192MB, FILEGROWTH = 512MB);

-- Add a second file with identical settings (repeat for each new file)
ALTER DATABASE tempdb
ADD FILE (NAME = N'temp2',
          FILENAME = N'T:TempDBtempdb_mssql_2.ndf',
          SIZE = 8192MB,
          FILEGROWTH = 512MB);

Size tempdb for the Workload, Not for the Default

The default tempdb files are 8 MB with 64 MB growth. On a busy server that means tempdb grows in small steps after every restart until it reaches the size the workload needs, and each growth event pauses the queries that triggered it.

• Measure first. Run a representative workload, including your index maintenance jobs, watch tempdb’s peak usage, and set the initial size of the files to cover it with headroom for concurrency.
• Keep autogrowth on as a safety net, with a fixed MB increment that’s the same for every file. The autogrowth guide covers how to choose the value.
• Enable instant file initialization for the SQL Server service account so data file growth doesn’t have to zero out new space. Since SQL Server 2022, log growth up to 64 MB also benefits from it.
• Size the log too. The tempdb log holds the log records of long-running transactions that use tempdb; a log that’s too small grows repeatedly at the worst moment.

Where to Put tempdb

Put tempdb on the fastest storage you have. It doesn’t need a separate disk per file. Microsoft’s guidance is to split files across disks only when you see disk-level I/O bottlenecks, and to separate tempdb from the user database disks when the two compete for I/O. In our experience, the most common tempdb incidents aren’t about latency but about space: disks that fill up at peak hours, combined with poorly chosen autogrowth settings.

To move tempdb, change the file paths and restart the service. The files are created at the new location during startup; afterwards, delete the old files by hand:

ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', FILENAME = N'T:TempDBtempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = N'templog', FILENAME = N'L:TempDBLogtemplog.ldf');
-- Repeat for every additional data file, then restart the SQL Server service

Before restarting, make sure the folders exist and the SQL Server service account can write to them. If SQL Server can’t create tempdb at startup, the service doesn’t start.

tempdb Contention and What Changed by Version

Contention shows up as PAGELATCH_UP or PAGELATCH_EX waits on tempdb pages: many sessions creating and dropping temporary objects at the same time and queuing on the same allocation or metadata pages. Each recent version has removed part of that bottleneck:

If contention remains on SQL Server 2019 or later, check whether it’s on metadata rather than allocation pages. Microsoft’s diagnostic query returns rows only when sessions are waiting on tempdb system tables:

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT r.session_id)               AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
  AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
  AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

If this returns rows regularly under load, memory-optimized tempdb metadata can help. Enable it with ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON; and a restart. Microsoft recommends it only when metadata contention significantly affects the workload, and suggests binding it to a resource pool to cap its memory. It also has limitations, for example columnstore indexes can’t be created on temporary tables while it’s on.

Monitor tempdb Space Before It Runs Out

This query shows what is using tempdb right now:

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

How to read the result:

• Large internal objects: sorts and hashes spilling to tempdb. Look for queries with missing indexes or too-low memory grants, and for index rebuilds with SORT_IN_TEMPDB. sys.dm_db_task_space_usage and sys.dm_db_session_space_usage show which sessions are responsible.
• Large user objects: big temporary tables or table variables, often in reporting or ETL code.
• A growing version store: usually a long-running transaction in a database that uses READ_COMMITTED_SNAPSHOT or snapshot isolation. Row versions can’t be cleaned up until it finishes. sys.dm_tran_version_store_space_usage shows which database generates them.

Turn the thresholds into SQL Server alerts so someone is notified while there’s still time to act.

tempdb Checklist

• Data files: as many as logical processors up to 8, then more in multiples of 4 only if contention remains.
• All data files the same size and the same fixed MB growth.
• Initial size based on measured peak usage, so tempdb doesn’t regrow after every restart.
• Instant file initialization enabled for the service account.
• Fast storage, separated from user databases if they compete for I/O.
• No trace flags 1117 or 1118 on SQL Server 2016 and later.
• Alerts on tempdb free space and disk free space; version store size watched on RCSI and snapshot databases.
• On SQL Server 2025, consider tempdb space resource governance for workloads that can run away.