DBAtlas · Database operations atlas

Add a data file to tempdb

SQL ServerStorage

Command

-- Show current tempdb files:
SELECT name, physical_name, size*8/1024 AS size_mb, growth
FROM   sys.master_files WHERE database_id = DB_ID(N'tempdb');

-- Add a new file (up to the number of CPU cores, equally sized):
ALTER DATABASE [tempdb]
ADD FILE (
  NAME = N'tempdev2',
  FILENAME = N'T:\tempdb\tempdb_mssql_2.ndf',
  SIZE = 8GB, FILEGROWTH = 1GB
);

What it does

Adds an extra data file to tempdb to reduce allocation (PFS/GAM/SGAM) contention and spread I/O.

When to use it

On systems with tempdb contention (PAGELATCH_UP waits) or heavy tempdb usage.

Caution

All tempdb data files must have the SAME size and growth increment (for proportional fill). The added file is used immediately without a restart; if existing files differ in size, equalize them all first, otherwise the new (empty) file takes a disproportionate load. The file count is usually matched to the core count (up to 8); SQL Server 2016+ setup does this by default.

Open in DBAtlas →

Related