Great topic, but what was the rationale for assigning multiple (four) data files to a single filegroup?


Let's assume:
• a desire to favor query performance (i.e., sequential I/O) over load times
• a willingness to assign a filegroup to each fact table
• fact tables are 'properly loaded' to facilitate sequential I/O, and have a clustered key
• we ignore tempdb


It's easy to find Microsoft DW whitepapers recommending a *single* file per filegroup, warning that proportional fill leads to file switching and hindered read-aheads.


Other sources, such as this video, advocate multiple files per filegroup.


Fast Track papers are content with a single filegroup (Primary), too, and recommend two files per RAID 1 LUN (~drive). Furthermore, the number of cores drives the number of LUNs (~drives).


Oddly, the SQL CAT team loves proportional fill striping, makes no mention of file switching, and only warns that proportional fill can hinder DW *load* times via smaller scatter/gather writes.