Demo SQL Server 2008 DW - Configure DW Storage

Sign in to queue

Description

Eric Hanson, principal program manager for SQL Server data warehousing, walks through several of the key features and functions of SQL Server 2008. Video 1 is on configuring DW storage.
If you're having difficulty reading the scripts, please select a higher-res format for viewing.

Embed

Download

Download this episode

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