Microsoft SQL Server 2000 Administrators Companion

3 4

This section presents tips and recommendations for how you can best utilize RAID controllers in your system. Some of these tips and recommendations have been mentioned earlier, and some are new. Ideally, some of them will help you to better configure and utilize your RAID array. By following these guidelines and carefully monitoring your system, you should be able to avoid performance problems.

• Isolate the SQL Server transaction log onto its own RAID 1 or RAID 10 volume. I/O operations to the transaction log are almost 100 percent sequential and almost 100 percent writes. The only time the transaction log handles random I/O is during a rollback operation. If the data needed for a rollback is no longer cached, the information must be read from the transaction log.
• Configure enough drives to keep the data file volumes at fewer than 85 I/O operations per second per disk. You can accomplish this by simply adding more disk drives to the array until you have enough. If the I/O operations are random, which they usually are, they will spread out among all of the disk drives in the array.
• Configure data file volumes as RAID 5 if writes are less than 10 percent and as RAID 10 if writes are greater than 10 percent of the total I/O operations.
• Regularly monitor the number of I/O operations per second per disk. If the disks approach their limits, add more disk drives.
• Spread controllers out among the available PCI slots in your system. Don't double up on a PCI bus unless you need to.
• Use Windows 2000 RAID only in systems where CPU time is plentiful. Software RAID produces a fair amount of overhead, which can slow down a system that is short on CPU cycles.