SQL Server Performance Forum – Threads Archive

Array stripe size for SQL

Hi. I got a new server to install (been a while since we had new hardware here…). Compaq DL380 G3. Smart Array 5i Controller with 48 MB battery backed up cache and 6 disks configured as follows:
2 x 18 GB – RAID1 – System/pagefile – C:
2 x 36 GB – RAID1 – SQL data – D:
2 x 36 GB – RAID1 – SQL log – E: 4-5 databases will be divided on the SQL disks.
D: and E: will be formated with allocation unit size of 8192 bytes instead of default 4096.
Read/Write cache is 50%/50%. Initial tests with the sqliostress tool on a standard installation gives:
256 MB DB – 3555 writes/s – 29.5 MB/s average
1024MB DB – 3137 writes/s – 26.0 MB/s average But I guess the cache effects this alot. I’ll do some test without cache as well. How does the array stripe size come into play here. What would be the best configuration here for SQL. I’m looking for really good write performance. By default the array stripe size is 128 KB. Valid options are 8, 16, 32, 64, 128, 256.

I would go with your initial thought of setting SQL on D & E. For RAID 5 you would tend to use a large hardware stripe size say 128K, perhaps use 64K allocation size for the format under NT, where this was ideal for 6.5 & 7.0 systems. For SQL 2K I would suggest to leave at 128k which is default. I think Compaq would have more information with regard to setting the stripe sizes. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

remember that the NT/2000 allocation size > 4K implies that you can’t defrag the drive. RAID 1, with 128K should be ok. SQL tends to like 64K since that matches its extent size… Have never tried to compare performance here though… RAID 5 would use much smaller stripes to make parity calculation efficient. in terms of write caching, you must make sure that the controller preserves the order of writes. if not then your whole SQL installation is unsupported… Cheers
Twan

Thx. About preserving the order of writes, is there any controller today that does not do this? I can’t find any information about this on the compaq site.

Hi Argyle, I’m not sure if all modern controllers do… It seems to almost contradict the benefit of the caching… since the controller needs to keep track of what is written, keep it in a queue and ensure that only the last entry in the queue can be updated again before it is written to disk. Does the MS site have any info on compatible hardware for write caching SQL Server? I’ve never had to look… (yet) Cheers
Twan

]]>