Database File Count

Microsoft recommends that each database has 0.25 data files, per CPU core. Therefore a server with four cores will ideally have one file per database, but a 16 core server may perform better having four files per database.

Suggested Action

šŸ’” Aireforge Advisor can automatically generate a script for this.

Consider adding more data files, evening out the data across them and ensuring even usage to benefit from Proportional Fill.

How to Add Extra Data Files to SQL Server

USE [master]
GO
ALTER DATABASE [WhippetWorks]
ADD FILE
    (
        NAME = N'WhippetWorks_#',
        FILENAME = N'S:\SQLDATA\WhippetWorks_#.ndf',
        SIZE = 8192KB,
        FILEGROWTH = 65536KB
    )
TO FILEGROUP [PRIMARY]
GO

Further Reading

Redistributing Data Across Files | SQL Server Central

ALTER DATABASE (Transact-SQL) File and Filegroup Options | Microsoft Docs

Add Data or Log Files to a Database | Microsoft Docs