NTFS-formatted partitions should not exceed 80% of their capacity. For example, if you have a 20GB drive, it should never be fuller than 16GB. Why? NTFS needs room to work, and when you exceed 80% capacity, NTFS become less efficient and I/O can suffer for it. You may want to create a SQL Server alert to notify you when your arrays exceed 80% of their capacity so you can take immediate action to correct the problem. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
Avoid locating read-intensive and write-intensive activity on the same drive or array. For example, don’t locate a OLTP and a OLAP database on the same physical device. The same goes for avoiding putting both heavily random and sequential activity on the same device. Whenever a drive or array has to change back and forth between activities, efficiency is lost. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
If your budget is tight and you can’t afford the ideal number of disk controllers and hard disks to maximize your server’s I/O performance, keep the following in mind. OLTP applications are best optimized for I/O when you speed up disk reads and writes. The best way to do this is to add more hard disks to the array(s) that hold your database files and/or transaction logs. While adding more disk controllers helps, adding more disks helps OLTP-based applications more than adding more or faster disk controllers. This is because OLTP-based applications tend to be more limited by the number of transfer operations (read/writes) than they are by bandwidth limitations.
But if your application is OLAP-based, then adding more and faster disk controllers is generally a better way to boost I/O than adding more disk drives to your array. This is because OLAP applications tend to be more limited by bandwidth than by read/write operations. Adding faster or more disk controllers increases the bandwidth and helps to remove any bottlenecks. While adding more hard disks to arrays also helps, adding faster or more disk controllers generally helps more. [6.5, 7.0, 2000, 2005] Added 2-20-2001
*****
Different RAID levels offer different levels of performance and redundancy. Here is a summary of how you can take the best advantage of the various RAID levels to optimize your SQL Servers.
RAID 1
· Ideally, the operating system and SQL Server executables, including the operating system’s swap file, should be located on a RAID 1 array. Some people locate the swap file on its own RAID 1 array, but I doubt that this really offers much of a performance boost because paging, on a well-configured SQL Server, is not much of an issue.
· If your SQL Server database(s) are very small, and all the databases can fit on a single disk drive, consider RAID 1 for the storing of all your SQL Server data files.
· Ideally, each separate transaction log should be located on its own RAID 1 array. This is because transactions logs are written to and read sequentially, and by isolating them to their own array, sequential disk I/O won’t be mixed with slower random disk I/O, and performance is boosted.
RAID 5
· Although this is the most popular type of RAID storage, it is also not the best option for optimum SQL Server I/O performance. If a database experiences more than 10% writes, and most OLTP databases do, write performance will suffer, hurting the overall I/O performance of SQL Server. RAID 5 is best used for read-only or mostly read-only databases. Testing at Microsoft has found that RAID 5 can be as much as 50% slower than using RAID 10.
RAID 10
· RAID 10 offers the best performance for SQL Server databases, although it is the most expensive RAID option. The more write intensive the database, the more important it is to use RAID 10.
· RAID 10 arrays are also a good option for transaction logs, assuming they are dedicated to a single transaction log.
[6.5, 7.0, 2000, 2005] Added 9-12-2001
*****
At the very minimum, your server should have at least one 100Mbs or faster network card, and perhaps two. Two cards can be used to increase network throughput and to offer redundancy. In addition, the network card(s) should be connected to full-duplex switched ports for best performance. [6.5, 7.0, 2000, 2005]