Re: Demo SQL Server 2008 DW - Configure DW Storage

SteveOLAP — Tue, 04 Aug 2009 15:42:12 GMT

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.

Comment Feed for Demo SQL Server 2008 DW - Configure DW Storage (Joe Longtin on Channel 9)

Re: Demo SQL Server 2008 DW - Configure DW Storage