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.
💡 Aireforge Advisor can automatically generate a script for this.
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.
Virtual cores are considered to be one CPU.
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