An important aspect of database performance tuning is I/O performance tuning. Unless SQL Server is running on a computer with enough RAM to hold the entire database, I/O performance is dictated by how fast reads and writes of SQL Server data can be processed by the disk I/O subsystem.
The typical hard disk drive is capable of providing the Windows operating system and SQL Server with about 75 nonsequential (random) and 150 sequential I/O operations per second. Advertised transfer rates for these hard disk drives are around 40 megabytes (MB) per second. It is much more likely for a database server to be constrained by the 75/150 I/O transfers per second than the 40-MB per second transfer rate. This is illustrated by these calculations:
(75 random I/O operations per second) x (8-KB transfer) = 600 KB per second
This calculation indicates that by doing strictly random single-page read and write operations on a hard disk drive, you can expect at most 600 KB (0.6 MB) per second I/O processing capability from that hard disk drive. This is much lower than the advertised 40 MB per second I/O handling capacity of the drive. SQL Server worker threads, checkpoint, and lazy writer perform I/O in 8-KB transfer sizes.
(150 sequential I/O operations per second) x (8-KB transfer) = 1200 KB per second
This calculation indicates by doing strictly sequential single-page read and write operations on a hard disk drive, it is reasonable to expect at most 1200 KB (1.2 MB) per second I/O processing capability from the hard disk drive.
(75 random I/O operations per second) x (64-KB transfer) = 4800 KB (4.8 MB) per second
This calculation illustrates a worst-case scenario for read-aheads, assuming all random I/O. Even in the completely random situation, the 64-KB transfer size provides much better disk I/O transfer rate from disk (4.8 MB per second) than the single-page transfer rates (0.6 and 1.2 MB per second):
(150 sequential I/O operations per second) x (64-KB transfer) = 9600 KB (9.6 MB) per second
This calculation indicates that by doing strictly sequential read or write operations on a hard disk drive, you can expect at most 9.6 MB per second I/O processing capability from that hard disk drive. This is much better than the random I/O case. SQL Server Read Ahead Manager performs disk I/O in the 64-KB transfer rate and attempts to arrange reads so read-ahead scans are done sequentially (often referred to as serially or in disk order). Although Read Ahead Manager performs I/O operations sequentially, page splitting tends to cause extents to be read nonsequentially rather than sequentially. This is one reason to eliminate and prevent page splitting.
Log manager writes sequentially to log files up to 32 KB.