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.