The best practice from Microsoft is to generally have 1 database file per processor on a dedicated disk. The files should be set large enough so that they meet your growth estimates for a good 6 months, if you can afford the space. I should warn you that there is some debate on this recommendation and many people run .25 database files per processor or even just 1 big file on less busy servers.
Update: There is not a lot of good information on this subject outside of the best practices recommendations. I am basing my recommendations on actual I/O experiences on a highly utilized OLTP environment. I am not advocating a certain number of files for database files in this post. Measure what I have provided against your own experiences in your environment and modify accordingly.
In General on my larger heavily used environments I like to run with 1 database file per core / processor. Of course being big servers, these machines have a lot of cores. The last server I built had 32 cores. Creating individual files via the GUI would have taken 30 minutes to an hour assuming I did not fat finger something and have to redo it.
I have written a script to add a database file per processor. The script is easy enough to use. It looks at the file allocations currently in place, sets the max to infinite, sets growth to 10% of current size and assumes auto grow is on. Please adjust to your standards. Next you would simply double check your math and settings then run the script and n copies of that file would be created. The script generates the Alter Database statements however, you can set it to automatically execute the Alter database statements. I prefer to review before I execute. You could also add a check for available disk space and adjust the file settings accordingly. This could also be easily modified to handle log files as well (type_desc = ‘LOG’).
DECLARE @cpucount int,
SET @dbid = DB_ID(@dbname)
SELECT @physicalname = physical_name,
@size = size,
@maxsize = 268435456,
@growth = size/10
WHERE database_id = @dbid AND type_desc = ‘ROWS’
SELECT @filecount = COUNT(*)
WHERE type_desc = ‘ROWS’ AND database_id = @dbid
SELECT @cpucount = cpu_count
WHILE @filecount < @cpucount — Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.
SELECT @logicalname = ” + @dbname + ” + CAST(@filecount AS nvarchar)
SELECT @filename = REPLACE(@physicalname, @dbname + ‘.mdf’, @logicalname + ‘.ndf’)
SELECT @altercommand = ‘ALTER DATABASE ‘ + @dbname + ‘ ADD FILE ( NAME =N”’ + @logicalname + ”’, FILENAME =N”’ + @filename + ”’, SIZE = ‘ + CAST(@size AS nvarchar) + ‘MB, MAXSIZE = ‘ + CAST(@maxsize AS nvarchar) + ‘MB, FILEGROWTH = ‘ + CAST(@growth AS nvarchar) + ‘MB )’
–EXEC sp_executesql @altercommand
SELECT @filecount = @filecount + 1
As with any script you get off the internet please review for appropriateness in your environment. NJOY and I hope this is helpful.