SQL Server Performance Forum – Threads Archive
Stripe uit size for SQL 2K
Hi, We are using SQL 2K std with sp4. We have a need to reconfigure our Raid in our main production server which serves as transactional data collection (or OLTP). My question is about what strip size I should configure for Raid 10 (4 disks) for our transactional SQL server databases. Our database is about 14 Gbytes. Most tables(including some large ones) have less than 10 columns but some of them have text fields and some may have Varchar(1000) fields. However, we have very few of these types of fields. There are 4~6 big tables that may have up to 30 columns. Most of the big tables are used frequently and some of them have more than 1 million records but they are narrow in width. Generally speaking I consider our tables are small. The server we are reconfiguring is mostly for transaction processing meaning 60% write and 40% read. There shouldn’t be a lot of large data read and write frequently. Transaction rate is like 20 per minutes during our peak hours (10 am to 3 pm so we are not high volume OLTP like banking and sales. With the above fact, here are my questions. 1. what stripe size should we set? I think the default is 8K. 2. Say if I increase it to 64K, will it hurt performance if there are not a lot of large data read or write? 3. Is there a scientific way for me to determine what stripe size I should use? 4. If I set the stripe size to a certain number during the raid configuration, I assume I need to match the stripe size during the disk formatting (allocation unit size) using W2K3 disk management tool, right? Any help on this is very much appreciated. wingmanif you are using HP/Compaq Smart Array controllers, leave the default alone, there is something they know but don’t really discuss you could do diskpart, that is, force alignment on 4k or 8k boundaries, at the OS level, leave it in default, people have wasted much time looking into this and not gained anything. but really, get more disks, i like to be able to do 400-800MB/sec large block sequential, this means 2-4 U320 SCSI channels, 3-4 disks per channel,
this is based on the max bandwidth a SQL 2000 table scan can generate, so it does not choke off transactions
We are using IBM e-server. The computer is about 1 1/2 year old and has a 5i scsi controller in there. I don’t have the model name o The server only has six slots and I allocate 2 for the OS and transaction log and the rest (4) is raid 10. I don’t have much hard drive to work with. I don’t have the exact e-server model name but are you familiar with IBM e-server to make some suggestion about the stripe size? Their Raid installation manual suggests for database type of server to set to 16 KB stripe size. Their IBM support guy suggested me to change to 64 KB but this is before I gave him more factual information as I illustrated in my original email. I haven’t heard from him yet and like to hear others’ opinion. wingman
quote:Originally posted by joechang
if you are using HP/Compaq Smart Array controllers, leave the default alone, there is something they know but don’t really discuss
you could do diskpart, that is, force alignment on 4k or 8k boundaries,
at the OS level, leave it in default, people have wasted much time looking into this and not gained anything.
but really, get more disks, i like to be able to do 400-800MB/sec large block sequential, this means 2-4 U320 SCSI channels, 3-4 disks per channel,
this is based on the max bandwidth a SQL 2000 table scan can generate, so it does not choke off transactions
this is based on the max bandwidth a SQL 2000 table scan can generate, so it does not choke off transactions
]]>