Professional Microsoft® SQL Server® 2008 Administration

11.8. I/O

I/O encompasses both network I/O and disk I/O. In most cases with SQL Server, you are primarily concerned with disk I/O, as that's where the data lives. However, you also need to understand the effect that poor network I/O can have as a bottleneck to performance.

Configuring I/O for a server storage system is perhaps the place where you have the most options, and it can have the largest impact on the performance of your SQL Server system. When you turn off your computer, the only thing that exists is the data stored on your hard drive. When you turn the power on, the processor starts running, the OS is loaded, and SQL Server is started; all this happens by reading data and code from the disk.

This basic concept is true for everything that happens on a computer. Everything starts its life on the disk and has to be read from the disk into memory and from there through the various processor caches before it reaches the processor and can be used as either code or data. Any results the processor arrives at have to be written back to disk to persist any system event, e.g., shutdown, failure, maintenance, and so on.

SQL Server 2008 is very sensitive to disk performance, more so than many applications, because of the manner in which it manages large amounts of data in user databases. Many applications have the luxury of being able to load all their data from disk into memory and then being able to run for long periods of time without having to access the disk again. SQL Server ...