SQL Server file and filegroup

Question

Hello everyone,

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

I could provide a long explanation but MSDN does a good job of it here. It may be that you specifically don't need to have more than one file in a file group, but that is not true of everybody.

Answer 2

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.