June 25, 2008 at 6:20 pm
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)?
June 25, 2008 at 8:55 pm
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)
Maninder
www.dbanation.com
June 25, 2008 at 9:10 pm
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.
Maninder
www.dbanation.com
June 26, 2008 at 6:39 am
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?
June 26, 2008 at 6:52 am
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.
Maninder
www.dbanation.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply