Select the best I/O controller you can get. Top-notch controllers offload much of the I/O work onto its own local CPU, freeing up CPU time on the server to do other tasks. For the ultimate in I/O controllers, consider a fiber channel connection instead of a SCSI connection. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
Don’t use Windows Server’s software-based RAID, instead use hardware-based RAID. Software-based RAID is much slower because it can’t offload the work to a separate processor, as does hard-based RAID solutions. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
Don’t store your operating system, application software, or databases on single disk drives as single drives don’t afford any fault tolerance. Instead, always choose a RAID array made up of two or more physical drives that offers fault tolerance. Common fault tolerant RAID configurations includes RAID Level 1 (mirroring or duplexing), RAID Level 5 (disk striping with parity), and RAID Level 10 (also called 1+0, which includes both striping without parity and mirroring). Each of these RAID levels offers different performance levels. Ideally, if your budget can support it, chose RAID Level 10, which offers both high-speed and fault tolerance. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
Don’t attach DAT, DLT, CD-ROM, scanners or other non-hard disk devices on the same I/O controllers that connect to your hard disk arrays. In addition, don’t put hard disks on the same I/O controller if they have different speeds. Putting different devices on the same I/O controller slows the faster devices. Always put slower devices on their own I/O controller. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
For the ultimate in I/O throughput, assign each type of major I/O activity (database, log files, tempdb, etc.) to its own separate RAID controller and dedicated RAID array. Very expensive, but very fast. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
Generally speaking, the greater the RAM cache on the controller, the higher the performance of the overall I/O. This is because data can be read ahead and stored in the cache, even if the data is not currently requested by SQL Server. Statistically speaking, the odds are the data SQL Server wants next from the array will already be in the cache, speeding up data access.
But, if your server’s I/O system is already maxed out, it is possible that the controller cache might actually slow down overall I/O. Why? This is because the act of caching itself takes some overhead.
If your SQL Server’s I/O system is already maxed out, then caching may not provide any benefits, and the overhead of the caching may increase I/O time. If your system is maxed out, consider turning off caching and see what happens to your I/O performance. Testing is the only way to know for sure. To find out how to turn your I/O controller’s read caching ability off and on, you will need to check your I/O controller’s vendor. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
Controller write caching can speed up SQL Server I/O. The problem with this is that SQL Server will not know about the write caching, and will assume that when it writes a page to disk, that it was written successfully to disk. While this will be the case in most instances, it may not always be the case. If the server should fail before the data in the cache has been written to disk, then that data will be lost and your SQL Server databases may become corrupted.
While a battery backup for the cache can help reduce this problem, they introduce their own problems. For example, what if the server’s motherboard fails and you need to move the drives to a new server. Will you be able to also move the cache controller to the new server, and will the data being backed up still be available in the new server?
I personally never use write caching. The potential benefits don’t outweigh the potential problems. To find out how to turn our I/O controller’s write caching ability off and on, you will need to check with your I/O controller’s vendor.
In addition, controller write caching can hurt performance on arrays that are running near full capacity. This is because cache controllers generally give write caching higher priority than read caching in order to more quickly empty the cache. Because of this, you may want to consider turning writing caching off for very busy servers. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
According to Microsoft, the best I/O performance occurs when database and stored on NTFS-formatted partitions. Not only is performance optimized, but so is security and recoverability. [7.0, 2000, 2005] Updated 1-3-2005