Professional SQL Server® 2005 Performance Tuning

3.6. Disk Bottlenecks

Moving data onto or off disk is almost always the most time-consuming (therefore expensive) operation SQL Server needs to undertake. SQL Server goes to great lengths to avoid the user having to wait while data is transferred between memory and disk because any slight delay in this process is likely to impact perceived server performance. To do this SQL Server has a buffer cache that it pre-loads with data and a plan cache that is loaded with optimal plans detailing the most efficient way to retrieve data.

There are a couple of factors which often mislead engineers working with SQL Server specifically when related to disk problems. The first is listening too much to the sales guy from the SAN manufacturers; the second is not having a baseline. Here's a summary of each of these:

SAN vendors have impressive marketing engines, and their representatives often penetrate deep into customer organizations — so much so that they'll come on-site and help you configure the SAN. Often these SAN engineers believe their own marketing pitch too. They can be heard making impressive claims about disk, controller, and cache performance on their particular SAN. However, few of them fully understand SQL Server and the patterns and I/O demands it places on the disk subsystem. No matter how fast the disk spindles rotate, how good the controllers operate, or how large the cache is, demanding OLTP applications running with SQL Server even on commodity hardware can nearly always request ...