For small DBs one or maybe two data files (.mdf/.ndf) works fine. With larger DBs though there is an opportunity to gain performance by the judicious use of additional file groups and files. Unfortunately some folks go a little crazy and you can end up with more files than you can shake a dead cat at (my apologies to cat lovers).
Here’s a quick script to give you the lay of the land regarding file groups and their files.
Select
FG.name As FGName
,Count(F.name) As FileCount
,Convert (Integer, Avg((F.size*8096.0)/(POWER(1024.0,2))))
As AvgFileSize_MB
From
<DBName,sysname,MyDB>.sys.filegroups As FG
Inner Join
sys.master_files As F
On FG.data_space_id = F.data_space_id
Where
F.database_id = DB_ID(‘<DBName,sysname,MyDB>’)
Group By
FG.name
Having
COUNT(F.name) > 1
Order By
FGName
Actually I like cats, I just can’t eat a whole one by myself.