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

šŸ’” 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

Further Reading

Optimizing tempdb Performance | Microsoft Docs

Capacity Planning for tempdb | Microsoft Docs

Correctly adding data files to tempdb | SQLSkills.com