Demo SQL Server 2008 DW - Configure DW Storage

Download this episode

Download Video

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

Format

Available formats for this video:

Actual format may change based on video formats available and browser capability.

    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.

    Comments closed

    Comments have been closed since this content was published more than 30 days ago, but if you'd like to send us feedback you can Contact Us.