Transfer size and I/Os per second are mutually exclusive. By increasing the stripe size, you significantly decrease the number of I/O requests you can handle concurrently. So the answer depends on the nature of the I/Os and the database.
If your sql requests are transactional, like pulling up customer records and running reports for multiple concurrent users, then make it as small as possible. If, however, you are serving up huge image files for an automotive parts database, then larger is better.
My guess is that you are doing the former, so set stripe as small as possible. Furthermore, use RAID1 instead of RAID10 and let the O/S decide what RAID1 group you want. This is because if you use RAID10, then you are forcing a larger I/O size to be done on the RAID. With small records, you could very easily be reading 256KB on disks when your app only wants 2KB.
As for write cache? Get a UPS and enable write cache (write back as it is sometime called). Don't consider enabling it unless you have a UPS AND AUTOMATED MONITORING SOFTWARE TO SHUT IT DOWN IN EVENT OF POWR LOSS>
Main Topics
Browse All Topics
by: bhess1Posted on 2009-11-04 at 11:10:45ID: 25742743
Since a SQL Server Extent is 64k, a stripe size of 64k is also an optimal choice.