Insufficient Data Files for tempdb

Ensuring that an instance has the optimum number of tempdb data files will maximize disk bandwidth and reduce tempdb contention. However, too many tempdb data files can reduce overall performance.

Suggested Action

šŸ’” Select 'Suggested Script' for an automatically generated script.

Create one tempdb data file per CPU, up to 8. If tempdb contention remains an issue, add extra tempdb data files in multiples of 4, ensuring that the initial size and auto growth settings are the same across all tempdb data files.

A dual-core CPU is considered to be two CPUs.

How to Add Extra Tempdb Data Files to SQL Server

USE [master]
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp#', FILENAME = N'T:\SQLData\temp#.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

Further Reading

Optimizing tempdb Performance | Microsoft Docs

Capacity Planning for tempdb | Microsoft Docs

Correctly adding data files to tempdb |