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.