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.
💡 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] GO ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp#', FILENAME = N'T:\SQLData\temp#.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO
Optimizing tempdb Performance | Microsoft Docs
Capacity Planning for tempdb | Microsoft Docs
Correctly adding data files to tempdb | SQLSkills.com