Professional Microsoft® SQL Server® 2008 Administration

12.2. The Silent Killer: I/O Problems

Customers often complain about their SQL Server performance and point to the database because the processors aren't that busy. After a discussion and a little elbow grease, frequently the culprit is an I/O bottleneck. The confusion comes from the fact that disk I/O is inversely proportional to CPU. In other words, over time, the processors are waiting for outstanding data requests that are queued on an overburdened disk subsystem. This section is about laying out the SQL Server shell or container on disk and configuring it properly to maximize the exploitation of the hardware resources. Scaling any database is a balancing act based on moving the bottleneck to the least affected resource.

12.2.1. SQL Server I/O Process Model

Windows Server 2003 or 2008 with SQL Server 2008 storage engine work together to mask the high cost of a disk I/O request. The Windows Server I/O Manager handles all I/O operations. The I/O Manager fulfills all I/O (read or write) requests by means of scatter-gather or asynchronous methods. For examples of scatter-gather or asynchronous methods, refer to SQL 2008 Books Online (BOL) under "I/O Architecture."

The SQL Server storage engine manages when disk I/O operations are performed, how they are performed, and the number of operations that are performed. However, the Windows operating system (I/O Manager Subsystem) performs the underlying I/O operations and provides the interface to the physical media. That is why we ...