What is the optimum chunk size for MS SQL Server 2005 data files on Raid 5
I am setting up a new MS SQL Server 2005 on a server with Direct Attached Storage SAS (serial attached SCSI) drives. I have a limited number of hard drives to work with. From other posts in Experts Exchange I've learned that
1. c: drive should be mirrored (Raid 1) and can contain the operating system, SQL2005 binaries, log files, and backup files (.trn and .bak).
2. d: drive should be RAID5 and contain the data and index files (.mdf files). I have 4 drives (including the hot spare) for the RAID5.
Questions
1. When the RAID5 is created, do I end up setting the chunk size or segment size? What do these terms mean and what are the optimum settings for SQL server 2005.
2. I understand that in RAID5, increasing the number of drives improves performance, but is there something less efficient about having 3 active drives instead of 2; or 4 is worse than 3? I thought I read relating to Oracle that "blocks" were 8k, so you want to split them over 2 or 4 drives, not 3 drives. Is this true for SQL 2005 as well?
1. c: drive should be mirrored (Raid 1) and can contain the operating system, SQL2005 binaries, log files, and backup files (.trn and .bak).
2. d: drive should be RAID5 and contain the data and index files (.mdf files). I have 4 drives (including the hot spare) for the RAID5.
Questions
1. When the RAID5 is created, do I end up setting the chunk size or segment size? What do these terms mean and what are the optimum settings for SQL server 2005.
2. I understand that in RAID5, increasing the number of drives improves performance, but is there something less efficient about having 3 active drives instead of 2; or 4 is worse than 3? I thought I read relating to Oracle that "blocks" were 8k, so you want to split them over 2 or 4 drives, not 3 drives. Is this true for SQL 2005 as well?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
http://www.microsoft.com/technet/prodtechnol/sql/2005/physdbstor.mspx
The parameter that is signficant in RAID setup is the stripe size. For queries of large tables (larger than 100mb) large stripes (256k) are recommended, as opposed to the default in the RAID configuration, which may be 64k.