drives, filegroups, and files - recommendations

  • I've got a server with 5 drives...database is relatively large and we're doing some researching just into performance. Redundancy may take a back seat. I've got about 5 filegroups. Should I...

    1) Have a standalone drive for the OS, put the logs & tempdb on C: as well, then have two RAID 0 stripe sets (D: & E:). this would allow me to spread the files across the two data drives (D&E). Two files per filegroup. So if I'm writing out to the HR filegroup, spread across HR1.ndf and HR2.ndf, then HR1.ndf is striped against drives 2 & 3, and HR2.ndf is striped across drives 4 & 5.

    OR

    2) Have a standlone drive for all drives (C, D, E, F, G). Logs, tempdb on C. 4 files per filegroup. So my HR filegroup would have HR1.ndf.....HR4.ndf, all on their own spindles.

    In which case am I going to get the best performance? In both cases all spindles are being used, and my storage quantity is the same, but I'm wondering which is better? 2 2-disk volumes at Raid 0 or 4 1disk volumes with no raid (or essentially raid 0)?

  • Well, here is the basic IDEA.

    A table striped across multiple drives can typically be scanned/accessed faster than the if the same table is stored on a single drive

    and otherwise, Placing tables on separate drives can significantly improve the performance of queries joining those tables(related tables)

  • personally, i will place my binaries on the OS Drive and consider RAID 5 for system Databases. consider Raid 5 for user DataFile as they are readonly most of the time. if they are most writes then RAID 0+1 will be the best option. Database Log File are Write intensive so, consider RAID 0+1. Again RAID 0+1 for tempdb as it is very Write intensive.

    Rest will depend upon your cost vs availability.

  • Yes - I understand about striping tables across drives. Both of my scenarios allow for this, it's just that the striping occurs at different levels. In scenario #1, the striping occurs at the RAID controller level. In scenario 2, the striping occurs at the OS/SQL file/filegroup level. My question was which would be better?

  • Surely Hardware level i.e. RAID controller level will be the best option to go with.

    It is a debate in itself, as you will see more members debating my opinion.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply