TempDB with excessive files

What this check looks for

A count of more than 14 data files in tempdb, from sys.master_files where database_id = 2 and type = 0.

Why it matters

This is the opposite of the single file problem, and it comes from the same advice taken too far.

The guidance “one tempdb data file per logical processor” was written when servers had four or eight cores. Applied literally to a modern 64 core machine it produces 64 tempdb files, and the current recommendation has been 8 to 12, adding more only if allocation contention persists, for many years.

Past the point where contention is resolved, extra files cost rather than help:

• Proportional fill has more work to do. SQL Server picks a file for each allocation based on free space across all of them. More files means more bookkeeping on the hottest allocation path on the instance.
• Each file is smaller for the same total size, so each is more likely to autogrow, and growth events on tempdb happen under load.
• More file handles and more I/O queues, which on some storage configurations is itself a constraint.
• Startup is slower, because tempdb is recreated at every restart and each file has to be created.
• They are harder to keep equal. Keeping 8 files the same size is easy; keeping 48 the same size through years of autogrowth is not, and unequal files reintroduce the contention the files were added to solve. That has its own check.

The benefit curve flattens quickly. Going from 1 file to 4 removes most allocation contention. From 4 to 8 helps on a busy instance. Beyond 8 the returns are small and require measurement to justify, and beyond 12 they are usually negative.

And modern versions need fewer. SQL Server 2016 made the allocation improvements default, and 2019 added memory optimized tempdb metadata, which addresses the related system table contention. An instance on a current version with 32 tempdb files is carrying a configuration designed for a problem the engine has largely solved.

How to confirm it yourself

SELECT COUNT(*)                                                  AS [data_files],
       CAST(SUM([size]) * 8.0 / 1024 / 1024 AS DECIMAL(10,2))    AS [total_gb],
       CAST(AVG([size] * 8.0 / 1024) AS DECIMAL(12,1))           AS [avg_file_mb],
       MIN([size] * 8 / 1024)                                    AS [smallest_mb],
       MAX([size] * 8 / 1024)                                    AS [largest_mb]
  FROM sys.master_files WITH (NOLOCK)
 WHERE [database_id] = 2 AND [type] = 0;

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

If smallest_mb and largest_mb differ, you have the unequal files problem as well, and that one matters more than the count.

Every file, so you can see the shape:

SELECT [file_id], [name],
       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 AND [type] = 0
 ORDER BY [file_id];

Whether there is any contention left to justify them:

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;

Low PAGELATCH waits with 30 tempdb files means the files are doing nothing for you.

How to fix it

Reduce to 8, all the same size, on the fastest storage available. This needs a restart, and that is the only awkward part.

Because tempdb is recreated at every startup, removing files is a two step operation:

USE [tempdb];
GO

-- 1. empty the file so it holds nothing
DBCC SHRINKFILE (N'tempdev12', EMPTYFILE);

-- 2. remove it
USE [master];
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev12];

EMPTYFILE on a tempdb file in use is unreliable, which is the practical difficulty. It can fail or hang while sessions are allocating. Two workable approaches:

• Do it in a quiet window, one file at a time, and accept that some attempts fail and need retrying.
• Or configure the target state and restart, which is cleaner. Remove the files from the configuration and restart the instance; tempdb is rebuilt from what remains. A file that cannot be emptied while running is removed cleanly this way.

Then set the remaining files equal, which is the part that actually matters:

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev',  SIZE = 4GB, FILEGROWTH = 512MB);
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev2', SIZE = 4GB, FILEGROWTH = 512MB);
-- and so on for each remaining file

Judge whether it is worth doing at all. Too many files is a mild inefficiency, not an outage, and the fix needs a restart. If you have a restart coming for another reason, fold this into it. If the files are also unequal, that is the stronger reason to act, because unequal files reintroduce real contention.

Size the total for the workload, not the file count. Eight files of 4 GB is 32 GB of tempdb, and whether that is right depends on what the instance does, not on how many files it is divided into.

How long it takes

About two hours, nearly all of it arranging the restart. The configuration change is minutes.

Related reports

Related checks

Frequently asked questions

Is one file per core wrong? It was reasonable advice for four and eight core servers. On modern hardware the recommendation is 8 to 12, adding more only with measured contention.

How urgent is this? Mild. It is an inefficiency rather than a problem, and the fix needs a restart. Fold it into the next one.

We have 32 files and no contention. Then the files are doing nothing and you can reduce them at your convenience. Check whether they are all the same size first; that is the finding worth acting on.

Can I remove files without a restart? Sometimes. EMPTYFILE on a tempdb file in use is unreliable, so a restart is the dependable route.