Quick Scan Report – TempDB only has a single data file

What this check looks for

A count of one data file in tempdb, from sys.master_files where database_id = 2 and type = 0. The message includes the server’s core count, because that is what the right number of files is derived from.

Why it matters

Every data file has a small set of allocation bitmap pages, and every allocation has to latch them. With one file there is one set, shared by every session on the instance.

The pages are:

Tempdb is the busiest allocator on the instance, because every temporary table, table variable, sort spill, hash spill, cursor and row version allocates there. On a busy server, sessions queue to latch those three pages, and the wait shows up as PAGELATCH_UP or PAGELATCH_EX on exactly those page numbers.

Note that this is PAGELATCH, not PAGEIOLATCH. The distinction is the whole diagnosis:

• PAGEIOLATCH is waiting for a page to come from disk. That is a storage problem, and it has its own check.
• PAGELATCH is waiting for a page already in memory. Faster disks do nothing for it. More files do.

Adding data files multiplies the number of bitmap page sets, which spreads the contention. That is the entire mechanism, and it is why the fix is file count rather than file size or storage speed.

SQL Server 2016 and later reduce this substantially, with TF 1118 behaviour on by default and improved allocation. SQL Server 2019 added memory optimized tempdb metadata, which addresses a different but related contention on the system tables. None of that removes the benefit of several data files; it lowers how often it matters.

It also matters less than it used to on small servers. On a four core instance with light tempdb use, one file may be perfectly adequate. The check reports it with the core count so you can judge.

How to confirm it yourself

What tempdb looks like now:

SELECT [name]                                                        AS [logical_name],
       [type_desc],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1))                    AS [size_mb],
       CASE WHEN [is_percent_growth] = 1 THEN CAST([growth] AS VARCHAR(10)) + ' %'
            ELSE CAST([growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       [physical_name]
  FROM sys.master_files WITH (NOLOCK)
 WHERE [database_id] = 2
 ORDER BY [type_desc], [file_id];

SELECT [cpu_count] FROM sys.dm_os_sys_info WITH (NOLOCK);

Whether the contention is actually happening, which decides whether this is urgent or theoretical:

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms] / 1000 AS [wait_seconds]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] LIKE 'PAGELATCH%'
 ORDER BY [wait_time_ms] DESC;

And live, which is the conclusive test:

SELECT r.[session_id],
       r.[wait_type],
       r.[wait_resource],
       r.[wait_time],
       DB_NAME(r.[database_id]) AS [database_name]
  FROM sys.dm_exec_requests AS r WITH (NOLOCK)
 WHERE r.[wait_type] LIKE 'PAGELATCH%'
 ORDER BY r.[wait_time] DESC;

Look at wait_resource. A value of 2:1:1, 2:1:2 or 2:1:3 is database 2, file 1, page 1, 2 or 3: tempdb’s allocation bitmaps. That is this finding, confirmed.

How to fix it

Add data files, all the same size, with the same growth increment.

USE [master];
GO

ALTER DATABASE [tempdb] ADD FILE
    (NAME = N'tempdev2', FILENAME = N'D:\SQLTempDB\tempdb2.ndf',
     SIZE = 2GB, FILEGROWTH = 512MB);

ALTER DATABASE [tempdb] ADD FILE
    (NAME = N'tempdev3', FILENAME = N'D:\SQLTempDB\tempdb3.ndf',
     SIZE = 2GB, FILEGROWTH = 512MB);

ALTER DATABASE [tempdb] ADD FILE
    (NAME = N'tempdev4', FILENAME = N'D:\SQLTempDB\tempdb4.ndf',
     SIZE = 2GB, FILEGROWTH = 512MB);

-- and bring the original up to match
ALTER DATABASE [tempdb] MODIFY FILE
    (NAME = N'tempdev', SIZE = 2GB, FILEGROWTH = 512MB);

Adding files takes effect immediately and needs no restart, though the benefit is fullest after one, because proportional fill favours the new empty files until they even out.

How many:

Do not go beyond 8 without evidence. More files than that rarely helps and makes each one smaller, which brings its own problems. There is a separate check for having too many.

All the same size, with identical growth. This is not optional. SQL Server fills files proportionally to their free space, so an unequal file receives more of the work and you get the contention of a single file while paying for several. That has its own check too.

Put them on the fastest storage you have, and not on the C: drive, both of which have their own checks.

On SQL Server 2014 and earlier, enable trace flags 1117 and 1118 as startup parameters. From 2016 that behaviour is the default for tempdb.

How long it takes

About four hours, most of it deciding the sizes and watching the waits afterwards. Adding the files is minutes.

Related reports

Related checks

Frequently asked questions

One file per core, always? Up to eight. Beyond that, add four at a time only if PAGELATCH waits on tempdb persist. The old “one per core” advice predates the allocation improvements in recent versions.

We are on SQL Server 2019. Is this still a thing? Less so. 2016 made the important allocation behaviour default and 2019 added memory optimized tempdb metadata. Several files still help on a busy instance; the threshold at which it matters is higher.

Do I need a restart? No. Adding files is online. A restart makes the balance even sooner because tempdb is recreated at the configured sizes.

Does file count help with tempdb being full? No. That is size, and it is a different problem. File count addresses allocation contention.