Demo SQL Server 2008 DW - Configure DW Storage

Sign in to queue

The Discussion

  • User profile image
    SteveOLAP

    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.

Add Your 2 Cents