SQL Server file and filegroup

Question

I can not think of any reasons why we need to have multiple files inside a file group. The reason why I think of this way is we can control from T-SQL (end user) level about file group, but can not control from T-SQL (end user) level about individual files of a file group. Any comments or ideas why files are still needed?

thanks in advance, George

Answer 1

Having multiple files per file group is only useful for the following reasons:

1. Distributing disk I/O load over multiple disks for performance reasons. i.e. in cases where re-configuring the RAID configuration with additional disks is not possible, or there is no RAID.
2. In cases where you have a VLDB and do not wish to deal with very large single files for logistical reasons.

There is 'urban legend' that SQL Server uses only 1 thread per file, so that the number of files should match the number of CPU's. This is however false, as discussed by Microsoft here.

Historically, there is another reason. Believe it or not in the days of SQL Server 4.2 through 7 sql server was sometimes installed on FAT32 file systems which had a 4 gig file limit. The ability to chain files together (in what we now call file groups) was a way to work around file system limitations and allow DBs larger than 4gigs on FAT based installs.