Data file and log file location and sizing are critical to obtaining optimal performance. I/O is one of the most common bottlenecks in database servers. Consequently, sizing I/O is critical to performance.
Disk drives should not be run at more than 75% of the maximum I/O capacity.
The two types of I/O are Random I/Os and Sequential I/Os. The type of I/O determines the number of I/Os per second that a disk drive can handle. Random I/Os require a longer time to complete than sequential I/Os.
Random I/Os
The amount of time spent performing random I/Os is governed by the time it takes to perform the seek operations. Since the random I/O involves requests for data on different parts of the disk, most of the time spent in the I/O will be the seek time. If a disk drive takes 10 milliseconds (averaging read and write), it is theoretically possible to perform 100 random seeks per second (1/.001 sec), excluding such things as overhead associated with the I/O bus or latencies associated with processing the I/O. Queuing begins as you get closer to the theoretical limit of the disk drive. As queuing occurs, the I/O latency increases and database performance begins to suffer.
You can increase the system's random I/O capacity by spreading the data across multiple
files or install the data files on a RAID array.
Data files are mostly accessed by random I/Os.
Sequential I/Os
Because there is no seek operation involved in sequential I/O, a disk drive can handle several hundred sequential I/Os per second without seeing increasing latencies. Therefore, using sequential I/O whenever possible can greatly improve the performance of the disk drives. Microsoft SQL Server and Oracle keep a log of the transactions; this log is primarily sequential to take advantage of sequential I/Os.
In order to achieve maximum performance, the data file and log file must not be on
the same physical drive.