|
SQL applications work best on striped mirrored RAID sets. Best practice from Microsoft recommends RAID5 for the database and RAID1 for the transaction logs. It will always be faster though on stripped mirrors, since RAID0+1 gives you the performance of stripe sets with the protection of mirrored sets.
Make sure your log drives are on separate physical spindles from your database. This not only gives you better performance since logs are more sequential and databases are more random, but it also protects you if you happen to lose the database disks. You can always replay the logs after a restore to get back in business. If the logs are on the same disks, you're out of luck.
I would need to look at your actual read/write ratio and ratio between random and sequential data access for your application before I could give you an intelligent answer to your question.
One way to find out what is going on with your access patterns is to turn on disk statistics for the performance monitor tool within NT/2000. (I assume you're using a Microsoft OS since you're running SQL). To turn on statistic gathering for disk for the NT perfmon utility, you need to open up a DOS prompt by selecting Start>Run> and typing in "CMD." A DOS screen will open up. At the command prompt, type in: "Diskperf -Y". You will have to reboot your server for the counters to be turned on.
Once you have the diskperf counter on, you can monitor all your disks during the day using the NT performance monitor tool to find out what's going on with your SQL volumes. To run the performance monitor, Click Start>Run, and type in "perfmon" at the prompt. Once the tool opens up, you can click the little + (plus sign) on the display screen to add disk perfomance stats. (Under the performance object pull down, make sure you select all the counters under both "physicalDisk" and "logicalDisk" objects.
If you find that most of your access is random, then adding more space for read ahead buffering may not be beneficial. If you find you have a more sequential access ratio, then more read cache will be helpful and the more you read ahead the better.
Ask your IBM representative if you can make the cache read ahead multiplier setting dynamic so the storage subsystem can tune itself depending on your data access type. That would make your life a whole lot easier and is something that should be asked when doing an RFI or RFP for storage. The question is: "Do you use manual or dynamic cache settings."
Chris
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our discussion forums.
|
